[RESOLVED] Retrieving and Formatting Database Data for Print

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.

Moved From The CodeBank

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?

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.

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.

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.

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.

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.

Well what does this print out need to look like?

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

Code:

---

    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.