Printing via IDatareader

[alander]

Hi i wanna print a list of database records off a oledbdatareader, every new row it shld create a new page, however its not working any help appreciated

Private Sub pDocPaySlip_PrintPage(ByVal sender As System.Object, ByVal e As System.Drawing.Printing.PrintPageEventArgs) Handles pDocPaySlip.PrintPage
        Dim sqlConn As New OleDbConnection(connString)
        Dim sqlStatement As String = "Select * from PayRoll WHERE Date>=#" + _
                                        Date.Now.Month.ToString + "/1/" + Date.Now.Year.ToString + _
                                        "# AND Date<=#" + Date.Now.Month.ToString + "/" + Date.DaysInMonth(Date.Now.Year, Date.Now.Month).ToString + "/" + Date.Now.Year.ToString + "#"

        Dim font As New System.Drawing.Font("Arial", 8, FontStyle.Regular)
        Dim sqlCmd As New OleDbCommand(sqlStatement, sqlConn)

        sqlConn.Open()
        Dim sqlReader As IDataReader = sqlCmd.ExecuteReader()

        Dim noOfPage As Integer = 0

        While sqlReader.Read
            e.Graphics.DrawString(sqlReader.Item(0), font, Brushes.Black, 100, 100)
            e.Graphics.DrawString(sqlReader.Item(1), font, Brushes.Black, 100, 150)
            e.Graphics.DrawString(sqlReader.Item(2), font, Brushes.Black, 100, 200)
            e.Graphics.DrawString(sqlReader.Item(3), font, Brushes.Black, 100, 250)
            e.Graphics.DrawString(sqlReader.Item(4), font, Brushes.Black, 100, 300)
            e.Graphics.DrawString(sqlReader.Item(5), font, Brushes.Black, 100, 350)
            e.HasMorePages = True
        End While

        e.HasMorePages = False

        sqlReader.Close()
        sqlConn.Close()

    End Sub

 

[JohnH]

PrintPage handler prints one single page. You set e.HasMorePages at the end of that method. If you set True same method is called again, this happens until you set False (or not set). The code logic in that method must be dynamic, and to print more than one page you need an indexer declared outside the method. Here is a typical flow for using a datareader for this:

Private reader As OleDbDataReader

Private Sub PrintDocument1_BeginPrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs) _
Handles PrintDocument1.BeginPrint
    theConnection.Open()
    reader = theCommand.ExecuteReader
    'get first row
    If Not reader.Read() Then 
        e.Cancel = True
        MsgBox("nothing to print")
    End If
End Sub

Private Sub PrintDocument1_PrintPage(ByVal sender As System.Object, ByVal e As System.Drawing.Printing.PrintPageEventArgs) _
Handles PrintDocument1.PrintPage
    'draw the fields of current row
    For ix As Integer = 0 To reader.FieldCount - 1
        e.Graphics.DrawString(reader(ix)...)
    Next
    'advance to next row
    If reader.Read Then
        e.HasMorePages = True 'if there is a next row allow for print next page
    End If
End Sub

Private Sub PrintDocument1_EndPrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs) _
Handles PrintDocument1.EndPrint
    reader.Close()
    reader = Nothing
    theConnection.Close()
End Sub

Also, DON'T write sql statements like that, use parameters:

Dim sqlStatement As String = "Select * from PayRoll WHERE [Date] >= @theDate"
Dim sqlCmd As New OleDbCommand(sqlStatement, sqlConn)
sqlCmd.Parameters.AddWithValue("@theDate", Date.Now.Date)

 

[alander]

Thanks for the tips