[RESOLVED] Retrieving and Formatting Database Data for Print

[venuspcs]

I need to know how to retrieve selected data from a database (searching all three tables in the database), put it into a printable (and understandable) format and then print it.

The database is an access database named Shenandoah.accdb with three tables named Shenandoah, Shenandoahs_Covered_Wagon and Shenandoahs_Heavy_Haul

All three tables have the following columns: mmddyy, Driver, Truck, Customer, Rate, Where_From, Where_To, Pit_Ticket, PO_Number, Yards_Tons, Description, QP_Fee, Fuel, Expenses and Advances.

I have a form fields for "Driver" and "mmddyy" that I need to use for the query to gather only information matching the values of those two form fields and then organize the results, display them and print them.

[Hack]

Moved From The CodeBank

[RobDog888]

Check out jmcilhinney's signature for a good ADO.NET tutorial on connecting to and retrieving data from a database.

Are you wanting to show the database report for printing or are you using something else for your print?

[venuspcs]

I tried his code for retrieving the data and I am able retrieve (all record) into memory using a modified version of his code but I can't figure out how to pull only selected rows (based on "mmddyy" and "Driver"). Nor am I able to PRINT (as in on Paper) the retrieved data.

[RobDog888]

Well you need to filter the data by using a WHERE clause in your sql statement passing the dates and other criteria.

Still not sure how you are needing/wanting to print.

[venuspcs]

I want to display the acquired data in a document or spreadsheet and then be able to print it out to paper. I want this to all happen when a button is clicked from my main form.

[RobDog888]

Then once you have a dataset populated from the query you can use that to write out to a Excel file and ADO.NET. If you have needs to format the Excel sheet or use Excel Object Model features then using Excel Automation would be required.

[venuspcs]

I haven't programmed anything since VB5 and now trying to use .NET. Furthermore, I have never programmed "database" anything so I am at a total loss on two fronts.

[RobDog888]

Well what does this print out need to look like?

[venuspcs]

I am playing with the DataGridView but not having any luck getting the data to populate the object.

Here is the code from my Main.form

    Private Sub Print_Daily_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Print_Daily.Click

        Dim connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Users\Richard Colbert\Documents\Visual Studio 2008\Projects\WindowsApplication1\WindowsApplication1\shenandoah.accdb'")
        Dim command As New OleDb.OleDbCommand("SELECT mmddyy, Driver, Truck, Customer, Rate, Where_From, Where_To, Pit_Ticket, PO_Number, Yards_Tons, Description, QP_Fee, Fuel, Expenses, Advances FROM Shenandoah  WHERE mmddyy='+mmddyy+' AND Driver='+Driver+'", connection)

        connection.Open()

        Dim reader As OleDb.OleDbDataReader = command.ExecuteReader()
        Dim table As New DataTable

        table.Load(reader)
        reader.Close()
        connection.Close()

        'The table can be used here to display the data.
        'That will most likely be done via data-binding but that is NOT a data access issue. 

        WindowsApplication2.Report.DataGridView1.DataSource = table

        Report.Show()

    End Sub

Which gathers the data needed based on the values of the "Driver" and "mmddyy" fields on my main form. It parses it to a DataTable named table then attempts to pass it to the Reports form to the DataGridView1 object. It then Shows the "Reports" form the DataGridView object contains only 1 empty line.