M
madmabs
Guest
Hello guys, am currently trying to develop reports using a the printpreview dialog control and even the printpreview dialog , it all works fine until I get to a point where I hit a brick wall.
what happens is am pulling data from three tables in MS SQL server and I need to get to a point where I need to tell the printpage event that it should stop just before the last row of rendered lines hits the marginbounds.bottom and then pull the next set of rows of data unto the second page and the third page and so on but instead I have the data on page 1 being repeated on the next set of pages over and over again
Also when I hit the preview button the preview dialog box keeps counting the number of pages to load and this count could go on forever I have to terminate the count before the preview can display
below is the code and outcome
Dim Check1 As String
Dim check2 As String
Dim xPos As Single = 0
Dim xPoscenter As Single = 0
Dim yPos As Single = 100
Dim Size As System.Drawing.SizeF
Dim xPosmargin As Single = 15
Dim strformat As New StringFormat
Dim startY As Integer = 100
Dim startX As Integer = 100
If Frontpage = 0 Then
Dim RectArea1 As Rectangle = New Rectangle(startX, startY, e.MarginBounds.Width, startY - 50)
e.Graphics.FillRectangle(Brushes.LightGray, RectArea1)
e.Graphics.DrawRectangle(Drawing.Pens.Black, RectArea1)
Size = e.Graphics.MeasureString(companyNameText1, CompanyName2)
'Now lets center this text a little
xPoscenter = ((e.PageBounds.Width * e.Graphics.PageScale) / 2) - (Size.Width / 2)
e.Graphics.DrawString(companyNameText1, CompanyName2, Drawing.Brushes.Black, xPoscenter, yPos + 10)
yPos = CSng(yPos + Size.Height)
Size = e.Graphics.MeasureString("WORK-IN", CompanyPhone1)
'Now lets center this text a little
xPoscenter = ((e.PageBounds.Width * e.Graphics.PageScale) / 2) - (Size.Width / 2)
e.Graphics.DrawString("WORK-IN", CompanyPhone1, Drawing.Brushes.Black, xPoscenter, yPos + 10)
yPos = CSng(yPos + Size.Height)
Size = e.Graphics.MeasureString("PRINTED ON:", Headerfonts)
xPoscenter = ((e.PageBounds.Width * e.Graphics.PageScale) / 2) - (Size.Width / 2)
' now move it to the left
e.Graphics.DrawString("PRINTED ON:", CompanyPhone1, Drawing.Brushes.Black, xPoscenter - 50, yPos + 10)
Size = e.Graphics.MeasureString(InvoiceDate, CompanyPhone1)
' now move the date a little to the right of the centered text "PRINTED ON:"
e.Graphics.DrawString(InvoiceDate, CompanyPhone1, Drawing.Brushes.Black, xPoscenter + 10, yPos + 10)
Else
End If
Frontpage += 1
startY = startY
Dim pickTicketIDvalue As String
Dim pickTicketIDvalue2 As String
Dim pickTicketIDvalue3 As String
Dim Useraccno As String
Dim Phone As String
Dim Firstname As String
Dim Surname As String
Dim PLUDescription As String
Dim PLUPrice As String
Dim ExtraDescription As String
Dim Extrasprice As String
Dim TicketTotal As String
cmdWorkinWashboxpremiere = CnnWashboxpremiere.CreateCommand
cmdWorkinWashboxpremiere.CommandText = "SELECT [tblticket].[TicketID]," &
"[Tblcustomers].[CustomerAccNo]," &
"[tblticket].[TicketDate], " &
"[tblticket].[Collectdate]," &
"[tblticket].[TicketTotal]," &
"[tblticket].[UserAccNo], " &
"[Tblcustomers].[FirstName]," &
"[Tblcustomers].[Surname]" &
"from TbLTicket" &
" inner join Tblcustomers" &
" on TblTicket.[CustomerAccNo] = Tblcustomers.[CustomerAccNo]" &
"WHERE TicketDate BETWEEN '" & txtStartdate.Text & "' and '" & txtenddate.Text & "'"
daWorkinWashboxpremiere.SelectCommand = cmdWorkinWashboxpremiere
dsWorkinWashboxpremiere.Clear()
daWorkinWashboxpremiere.Fill(dsWorkinWashboxpremiere, "TblTicket")
daWorkinWashboxpremiere.SelectCommand.CommandText = "SELECT TicketID, PLUDescription, GarmentQty, PLUPrice, PLUPriceTotal, GarmentPiecesTotal, RowID, ParentID from TbLTicketTrans WHERE TicketDate BETWEEN '" & txtStartdate.Text & "' and '" & txtenddate.Text & "'"
daWorkinWashboxpremiere.Fill(dsWorkinWashboxpremiere, "TbLTicketTrans")
daWorkinWashboxpremiere.SelectCommand.CommandText = "SELECT TicketID, ExtraDescription, ExtrasPrice, RowID FROM TblTicketTransExtra WHERE TicketDate BETWEEN '" & txtStartdate.Text & "' and '" & txtenddate.Text & "'"
daWorkinWashboxpremiere.Fill(dsWorkinWashboxpremiere, "TblTicketTransExtra")
For R As Integer = 0 To dsWorkinWashboxpremiere.Tables(0).Rows.Count - 1
Dim RectArea2 As Rectangle = New Rectangle(startX, startY, e.MarginBounds.Width, e.MarginBounds.Height - 50)
yPos = CSng(yPos + Size.Height * 2.0) + companyPhone1Height + 5
pickTicketIDvalue = dsWorkinWashboxpremiere.Tables(0).Rows(R)(0).ToString()
Useraccno = dsWorkinWashboxpremiere.Tables(0).Rows(R)(5).ToString()
Phone = dsWorkinWashboxpremiere.Tables(0).Rows(R)(1).ToString()
Firstname = dsWorkinWashboxpremiere.Tables(0).Rows(R)(6).ToString()
Surname = dsWorkinWashboxpremiere.Tables(0).Rows(R)(7).ToString()
TicketTotal = dsWorkinWashboxpremiere.Tables(0).Rows(R)(4).ToString()
Size = e.Graphics.MeasureString(pickTicketIDvalue, Headerfonts)
e.Graphics.DrawString(pickTicketIDvalue, CompanyPhone1, Drawing.Brushes.Black, Xdate + 130, yPos)
Size = e.Graphics.MeasureString("Server:", Headerfonts)
e.Graphics.DrawString("Server:", CompanyPhone1, Drawing.Brushes.Black, Xdate + 200, yPos)
'=======
Size = e.Graphics.MeasureString(Useraccno, Headerfonts)
e.Graphics.DrawString(Useraccno, CompanyPhone1, Drawing.Brushes.Black, Xdate + 250, yPos)
Size = e.Graphics.MeasureString(Phone, Headerfonts)
e.Graphics.DrawString(Phone, CompanyPhone1, Drawing.Brushes.Black, Xdate + 300, yPos)
Size = e.Graphics.MeasureString(Firstname, Headerfonts)
e.Graphics.DrawString(Firstname, CompanyPhone1, Drawing.Brushes.Black, Xdate + 425, yPos)
Size = e.Graphics.MeasureString(Surname, Headerfonts)
e.Graphics.DrawString(Surname, CompanyPhone1, Drawing.Brushes.Black, Xdate + 525, yPos)
yPos = CSng(yPos + Size.Height * 2.0) + companyPhone1Height
txtnooflines.Text = pickTicketIDvalue
For S As Integer = 0 To dsWorkinWashboxpremiere.Tables(1).Rows.Count - 1
Check1 = dsWorkinWashboxpremiere.Tables(1).Rows(S)(7).ToString()
pickTicketIDvalue2 = dsWorkinWashboxpremiere.Tables(1).Rows(S)(0).ToString()
If pickTicketIDvalue = pickTicketIDvalue2 Then
PLUDescription = dsWorkinWashboxpremiere.Tables(1).Rows(S)(1).ToString()
Size = e.Graphics.MeasureString(PLUDescription, Headerfonts)
e.Graphics.DrawString(PLUDescription, CompanyPhone1, Drawing.Brushes.Black, Xdate + 525, yPos)
PLUPrice = dsWorkinWashboxpremiere.Tables(1).Rows(S)(3).ToString()
Size = e.Graphics.MeasureString(PLUPrice, Headerfonts)
e.Graphics.DrawString(PLUPrice, CompanyPhone1, Drawing.Brushes.Black, Xdate + 650, yPos)
yPos = CSng(yPos + Size.Height * 2.0) + companyPhone1Height
For M As Integer = 0 To dsWorkinWashboxpremiere.Tables(2).Rows.Count - 1
pickTicketIDvalue3 = dsWorkinWashboxpremiere.Tables(2).Rows(M)(0).ToString()
check2 = dsWorkinWashboxpremiere.Tables(2).Rows(M)(3).ToString()
If pickTicketIDvalue2 = pickTicketIDvalue3 And check2 = Check1 Then
ExtraDescription = dsWorkinWashboxpremiere.Tables(2).Rows(M)(1).ToString()
Size = e.Graphics.MeasureString(ExtraDescription, Headerfonts)
e.Graphics.DrawString(ExtraDescription, CompanyPhone1, Drawing.Brushes.Black, Xdate + 525, yPos)
Extrasprice = dsWorkinWashboxpremiere.Tables(2).Rows(M)(2).ToString()
Size = e.Graphics.MeasureString(Extrasprice, Headerfonts)
e.Graphics.DrawString(Extrasprice, CompanyPhone1, Drawing.Brushes.Black, Xdate + 650, yPos)
yPos = CSng(yPos + Size.Height * 2.0) + companyPhone1Height
End If
Next
Else
End If
Next
xPoscenter = ((e.PageBounds.Width * e.Graphics.PageScale) / 2) - (Size.Width / 2)
yPos = CSng(yPos + Size.Height * 1.0)
'This is the Line stretching from width to width
Size = e.Graphics.MeasureString(Breakline1, BreakFonts)
e.Graphics.DrawLine(Drawing.Pens.Black, xPos + 100, yPos, e.Graphics.PageScale * e.MarginBounds.Width - xPos + 100, yPos)
yPos = CSng(yPos + Size.Height * 2.0) + companyPhone1Height
Size = e.Graphics.MeasureString(TicketTotal, Headerfonts)
e.Graphics.DrawString(TicketTotal, CompanyPhone1, Drawing.Brushes.Black, Xdate + 650, yPos)
yPos = CSng(yPos + Size.Height * 2.0) + companyPhone1Height
'This is the Line stretching from width to width
Size = e.Graphics.MeasureString(Breakline1, BreakFonts)
e.Graphics.DrawLine(Drawing.Pens.Black, xPos + 100, yPos, e.Graphics.PageScale * e.MarginBounds.Width - xPos + 100, yPos)
yPos = CSng(yPos + Size.Height * 2.0) + companyPhone1Height
If yPos > e.MarginBounds.Bottom Then
e.HasMorePages = True
yPos = 150
Exit Sub
End If
Next
End Sub
Thanks for your anticipated response and support
Continue reading...
what happens is am pulling data from three tables in MS SQL server and I need to get to a point where I need to tell the printpage event that it should stop just before the last row of rendered lines hits the marginbounds.bottom and then pull the next set of rows of data unto the second page and the third page and so on but instead I have the data on page 1 being repeated on the next set of pages over and over again
Also when I hit the preview button the preview dialog box keeps counting the number of pages to load and this count could go on forever I have to terminate the count before the preview can display
below is the code and outcome
Dim Check1 As String
Dim check2 As String
Dim xPos As Single = 0
Dim xPoscenter As Single = 0
Dim yPos As Single = 100
Dim Size As System.Drawing.SizeF
Dim xPosmargin As Single = 15
Dim strformat As New StringFormat
Dim startY As Integer = 100
Dim startX As Integer = 100
If Frontpage = 0 Then
Dim RectArea1 As Rectangle = New Rectangle(startX, startY, e.MarginBounds.Width, startY - 50)
e.Graphics.FillRectangle(Brushes.LightGray, RectArea1)
e.Graphics.DrawRectangle(Drawing.Pens.Black, RectArea1)
Size = e.Graphics.MeasureString(companyNameText1, CompanyName2)
'Now lets center this text a little
xPoscenter = ((e.PageBounds.Width * e.Graphics.PageScale) / 2) - (Size.Width / 2)
e.Graphics.DrawString(companyNameText1, CompanyName2, Drawing.Brushes.Black, xPoscenter, yPos + 10)
yPos = CSng(yPos + Size.Height)
Size = e.Graphics.MeasureString("WORK-IN", CompanyPhone1)
'Now lets center this text a little
xPoscenter = ((e.PageBounds.Width * e.Graphics.PageScale) / 2) - (Size.Width / 2)
e.Graphics.DrawString("WORK-IN", CompanyPhone1, Drawing.Brushes.Black, xPoscenter, yPos + 10)
yPos = CSng(yPos + Size.Height)
Size = e.Graphics.MeasureString("PRINTED ON:", Headerfonts)
xPoscenter = ((e.PageBounds.Width * e.Graphics.PageScale) / 2) - (Size.Width / 2)
' now move it to the left
e.Graphics.DrawString("PRINTED ON:", CompanyPhone1, Drawing.Brushes.Black, xPoscenter - 50, yPos + 10)
Size = e.Graphics.MeasureString(InvoiceDate, CompanyPhone1)
' now move the date a little to the right of the centered text "PRINTED ON:"
e.Graphics.DrawString(InvoiceDate, CompanyPhone1, Drawing.Brushes.Black, xPoscenter + 10, yPos + 10)
Else
End If
Frontpage += 1
startY = startY
Dim pickTicketIDvalue As String
Dim pickTicketIDvalue2 As String
Dim pickTicketIDvalue3 As String
Dim Useraccno As String
Dim Phone As String
Dim Firstname As String
Dim Surname As String
Dim PLUDescription As String
Dim PLUPrice As String
Dim ExtraDescription As String
Dim Extrasprice As String
Dim TicketTotal As String
cmdWorkinWashboxpremiere = CnnWashboxpremiere.CreateCommand
cmdWorkinWashboxpremiere.CommandText = "SELECT [tblticket].[TicketID]," &
"[Tblcustomers].[CustomerAccNo]," &
"[tblticket].[TicketDate], " &
"[tblticket].[Collectdate]," &
"[tblticket].[TicketTotal]," &
"[tblticket].[UserAccNo], " &
"[Tblcustomers].[FirstName]," &
"[Tblcustomers].[Surname]" &
"from TbLTicket" &
" inner join Tblcustomers" &
" on TblTicket.[CustomerAccNo] = Tblcustomers.[CustomerAccNo]" &
"WHERE TicketDate BETWEEN '" & txtStartdate.Text & "' and '" & txtenddate.Text & "'"
daWorkinWashboxpremiere.SelectCommand = cmdWorkinWashboxpremiere
dsWorkinWashboxpremiere.Clear()
daWorkinWashboxpremiere.Fill(dsWorkinWashboxpremiere, "TblTicket")
daWorkinWashboxpremiere.SelectCommand.CommandText = "SELECT TicketID, PLUDescription, GarmentQty, PLUPrice, PLUPriceTotal, GarmentPiecesTotal, RowID, ParentID from TbLTicketTrans WHERE TicketDate BETWEEN '" & txtStartdate.Text & "' and '" & txtenddate.Text & "'"
daWorkinWashboxpremiere.Fill(dsWorkinWashboxpremiere, "TbLTicketTrans")
daWorkinWashboxpremiere.SelectCommand.CommandText = "SELECT TicketID, ExtraDescription, ExtrasPrice, RowID FROM TblTicketTransExtra WHERE TicketDate BETWEEN '" & txtStartdate.Text & "' and '" & txtenddate.Text & "'"
daWorkinWashboxpremiere.Fill(dsWorkinWashboxpremiere, "TblTicketTransExtra")
For R As Integer = 0 To dsWorkinWashboxpremiere.Tables(0).Rows.Count - 1
Dim RectArea2 As Rectangle = New Rectangle(startX, startY, e.MarginBounds.Width, e.MarginBounds.Height - 50)
yPos = CSng(yPos + Size.Height * 2.0) + companyPhone1Height + 5
pickTicketIDvalue = dsWorkinWashboxpremiere.Tables(0).Rows(R)(0).ToString()
Useraccno = dsWorkinWashboxpremiere.Tables(0).Rows(R)(5).ToString()
Phone = dsWorkinWashboxpremiere.Tables(0).Rows(R)(1).ToString()
Firstname = dsWorkinWashboxpremiere.Tables(0).Rows(R)(6).ToString()
Surname = dsWorkinWashboxpremiere.Tables(0).Rows(R)(7).ToString()
TicketTotal = dsWorkinWashboxpremiere.Tables(0).Rows(R)(4).ToString()
Size = e.Graphics.MeasureString(pickTicketIDvalue, Headerfonts)
e.Graphics.DrawString(pickTicketIDvalue, CompanyPhone1, Drawing.Brushes.Black, Xdate + 130, yPos)
Size = e.Graphics.MeasureString("Server:", Headerfonts)
e.Graphics.DrawString("Server:", CompanyPhone1, Drawing.Brushes.Black, Xdate + 200, yPos)
'=======
Size = e.Graphics.MeasureString(Useraccno, Headerfonts)
e.Graphics.DrawString(Useraccno, CompanyPhone1, Drawing.Brushes.Black, Xdate + 250, yPos)
Size = e.Graphics.MeasureString(Phone, Headerfonts)
e.Graphics.DrawString(Phone, CompanyPhone1, Drawing.Brushes.Black, Xdate + 300, yPos)
Size = e.Graphics.MeasureString(Firstname, Headerfonts)
e.Graphics.DrawString(Firstname, CompanyPhone1, Drawing.Brushes.Black, Xdate + 425, yPos)
Size = e.Graphics.MeasureString(Surname, Headerfonts)
e.Graphics.DrawString(Surname, CompanyPhone1, Drawing.Brushes.Black, Xdate + 525, yPos)
yPos = CSng(yPos + Size.Height * 2.0) + companyPhone1Height
txtnooflines.Text = pickTicketIDvalue
For S As Integer = 0 To dsWorkinWashboxpremiere.Tables(1).Rows.Count - 1
Check1 = dsWorkinWashboxpremiere.Tables(1).Rows(S)(7).ToString()
pickTicketIDvalue2 = dsWorkinWashboxpremiere.Tables(1).Rows(S)(0).ToString()
If pickTicketIDvalue = pickTicketIDvalue2 Then
PLUDescription = dsWorkinWashboxpremiere.Tables(1).Rows(S)(1).ToString()
Size = e.Graphics.MeasureString(PLUDescription, Headerfonts)
e.Graphics.DrawString(PLUDescription, CompanyPhone1, Drawing.Brushes.Black, Xdate + 525, yPos)
PLUPrice = dsWorkinWashboxpremiere.Tables(1).Rows(S)(3).ToString()
Size = e.Graphics.MeasureString(PLUPrice, Headerfonts)
e.Graphics.DrawString(PLUPrice, CompanyPhone1, Drawing.Brushes.Black, Xdate + 650, yPos)
yPos = CSng(yPos + Size.Height * 2.0) + companyPhone1Height
For M As Integer = 0 To dsWorkinWashboxpremiere.Tables(2).Rows.Count - 1
pickTicketIDvalue3 = dsWorkinWashboxpremiere.Tables(2).Rows(M)(0).ToString()
check2 = dsWorkinWashboxpremiere.Tables(2).Rows(M)(3).ToString()
If pickTicketIDvalue2 = pickTicketIDvalue3 And check2 = Check1 Then
ExtraDescription = dsWorkinWashboxpremiere.Tables(2).Rows(M)(1).ToString()
Size = e.Graphics.MeasureString(ExtraDescription, Headerfonts)
e.Graphics.DrawString(ExtraDescription, CompanyPhone1, Drawing.Brushes.Black, Xdate + 525, yPos)
Extrasprice = dsWorkinWashboxpremiere.Tables(2).Rows(M)(2).ToString()
Size = e.Graphics.MeasureString(Extrasprice, Headerfonts)
e.Graphics.DrawString(Extrasprice, CompanyPhone1, Drawing.Brushes.Black, Xdate + 650, yPos)
yPos = CSng(yPos + Size.Height * 2.0) + companyPhone1Height
End If
Next
Else
End If
Next
xPoscenter = ((e.PageBounds.Width * e.Graphics.PageScale) / 2) - (Size.Width / 2)
yPos = CSng(yPos + Size.Height * 1.0)
'This is the Line stretching from width to width
Size = e.Graphics.MeasureString(Breakline1, BreakFonts)
e.Graphics.DrawLine(Drawing.Pens.Black, xPos + 100, yPos, e.Graphics.PageScale * e.MarginBounds.Width - xPos + 100, yPos)
yPos = CSng(yPos + Size.Height * 2.0) + companyPhone1Height
Size = e.Graphics.MeasureString(TicketTotal, Headerfonts)
e.Graphics.DrawString(TicketTotal, CompanyPhone1, Drawing.Brushes.Black, Xdate + 650, yPos)
yPos = CSng(yPos + Size.Height * 2.0) + companyPhone1Height
'This is the Line stretching from width to width
Size = e.Graphics.MeasureString(Breakline1, BreakFonts)
e.Graphics.DrawLine(Drawing.Pens.Black, xPos + 100, yPos, e.Graphics.PageScale * e.MarginBounds.Width - xPos + 100, yPos)
yPos = CSng(yPos + Size.Height * 2.0) + companyPhone1Height
If yPos > e.MarginBounds.Bottom Then
e.HasMorePages = True
yPos = 150
Exit Sub
End If
Next
End Sub
Thanks for your anticipated response and support
Continue reading...