VBMySQL.com

Part of the MyODBC Connector Databinding Tutorial

Matthew R. King
mking56@comcast.net

Purpose

This tutorial is in addition to my previous tutorial on using the MyODBC Connector, and uses the MyStore database. I will attempt to kill two birds with one tutorial: creating reports within Visual Basic.NET and modifying queries to the MySQL DataTables.

Why?

It took me awhile to figure out both, and – especially in the case of creating reports – I found other tutorials hard to follow. After fighting my way through it, I created this tutorial so i would not forget.

Initial Steps

This tutorial assumes you have completed the MyODBC Connector Databinding tutorial and have a working version of the MyStore database.

As in my previous tutorial, I do not spend a great deal of time on esthetics. Rather, I focus on functionality (getting it to work).

Creating a Report

I intend to create a very simple report that lists all employees by the store in which they work. First, I will create a report that shows employees for all stores, and then I will create a report that only shows the employees for the selected store.

Create a new DataTable in the DataSet

This first step is optional in most cases. When creating the report, one could simply drag and drop fields from existing DataTables. However, I have found this approach difficult in complex reports, and have found it easier to create a single DataTable that contains all information I need in the report.

1. Open the MyStore Project in Visual Studio.NET
2. Within the DataSet Designer, right-click an empty space and select Add –> TableAdapter



3. Use the existing connection for this DataTable. Select Next >



4. Select Use SQL Statements and select Next >



5. Select to use the Query Builder…



6. Add both tables by selecting them one at a time and pressing Add, then close the Add Table window.



7. The Query Builder will show both tables, including the link between them.



8. Select only the columns you want to show in the report. In this case, I want to list each store by city, state, and manager – then list each employee by first and last names.



9. Select OK to close the Query Builder



10. Select Finish. The new DataTable and TableAdapter are added to the DataSet with the default name TableAdapter1.

I want to point out here that editing within the DataSet Designer rarely goes smoothly for me. Often, everything looks OK until I try to save it, then errors start popping up. Usually, exiting the program and restarting it takes me right back to where I left off without loss of data — but still a nuisance.

I do not know if this is due to my computer, me, or the unholy combination of Open Source and Microsoft, but I have gotten into the habit of attempting to save often when I make changes within the DataSet Designer.

Designing the Report

For the purpose of this tutorial, I will keep the report design to a minimum, but it is not difficult to see how very complex reports can be created with a little practice.

1. Within the Solution Explorer, right-click on the solution and select Add –> New Item…



2. Select the Report icon and select Add.



3. The Report Designer appears, and the Report Items Toolbox becomes available.



4. From the Report menu, select Data Sources…



5. In the Report Data Sources dialog box, select the DataTable you created and select Add to Report. Select OK.



6. Drag a List Item from the Toolbox. Once in the Report Designer, you can resize it.



7. From the Data Sources window, drag the city, state, and manager textboxes from the DataTable into the List Item.



8. Drag a textbox from the Toolbox into the List Item and enter a value of Employees:



9. Drag another List Item from the Toolbox into the first List Item.



10. With List2 selected, set the DataSetName property to your DataTable.



11. From the Data Sources window, drag the firstname and lastname textboxes from the DataTable into the second List Item.



12. Set the Grouping property for list1 by right-clicking on the List Item and selecting Properties.



13. In the General tab of the List Properties dialog box, select the edit group details… button.



14. In the Group and Sorting Properties dialog box, select to use the city field as the Group On… expression.



15. Select OK twice to close the List Properties windows.
16. Obviously, this report could stand to have a little more formatting, but I am sticking to the fundamentals of data retrieval.

Create the Report Viewer

Setting up the Report Viewer – at least for this simple report – is quite easy.

1. Within the Solution Explorer, right-click on the solution and select Add –> New Item…



2. Select the Windows Form icon, and then select Add.



3. From the Toolbox, drag a ReportViewer into the new form.



4. From the Smart Tag, select the Report1 from the Choose Report list.



5. Again from the Smart Tag, select Rebind Data Sources.



6. The Report Viewer is now located in Form2, and Report1 is the selected source. As before, many other settings affect the way the report shows up within this form, and how the Report Viewer acts. I am only focusing on the fundamentals.

Create a Button to Link the Forms

The final step is to create a button on Form1 that brings up Form2 with the Report Viewer.

1. Open Form1 and add a button (btnPrintAll).



2. Double-click the btnPrintAll button in the Form Designer to create a Click event in the code window.



3. Within the btnPrintAll_Click event, enter the following code:


Dim rpt As New Form2
rpt.Show()




4. Build the application and run it by pressing F5. You should be able to select the Print All button and see a very ugly report of employees listed by the store they work in. However, with more time and effort, the report can be drastically improved.

Filter the Report

There are several ways of filtering a report to view the records you want to see. I will use a method that creates a new query and passes parameters in order to only view information for the selected store. In order to do this, I need to select a parameter I can use to select a store.

The obvious choice of a filter is storeID, as it is the Primary Key of the store table. Therefore, we need a way of selecting the storeID.

1. Open Form1 and select ComboBox1.



2. In the Properties window, select storeID as the ValueMember. When the user selects the city, the ValueMember of the ComboBox1 will contain the storeID of the selection.



3. Now that I have a way of selecting the storeID, I need to create a query that will use the storeID to filter the report. In the DataSet Designer, right-click the TableAdpater and select Add Query…



4. Select to Use SQL Statements and select Next >



5. Select to create a statement that is a Select which returns rows and select Next >



6. At the end of the WHERE clause in the Query Configuration Wizard, add this code:

AND (store.storeID = ?)



7. Select Next >
8. The Query Configuration Wizard provides the opportunity to rename the new methods. I entered FillByStoreID and GetDataByStoreID.



9. Select Next >



10. Select Finish. The Table Adapter shows the new method with which we can filter the results of the query by storeID.

Create a New Report Viewer

Although it is possible to write code to use the same Report Viewer (Form2), I will create a new Report Viewer (Form3). However, there is no need to create a new report, as Report1 contains all the data fields and bindings we need.

1. Use the same procedure as above to create a new Windows Form (Form3)
2. Add a ReportViewer to Form3
3. Select the same report (MyStore.Report1.rdlc) for this ReportViewer as you did for Form2
4. Rebind the Data Sources



5. Press F7 to view From3’s code window.



6. Notice the TableAdapter’s Fill method. We need to change this to use the new query we created. Modify the code to read:

Me.DataTable1TableAdapter.FillByStoreID(Me.mystoreDataSet.DataTable1, storeID)

7. Note that this method requires a variable (storeID), which is an integer I will pass to the query. I need to define this variable first, so above the From3_Load event, define the storeID variable as an integer. The next screenshot shows the code so far.



8. Add the following function to Form3’s code:

Sub New(ByVal s As Integer)

Try
initializeComponent()
storeID = s
Catch ex As Exception
MsgBox("Error: " & ex.Message)
End Try

End Sub

Create a Button to Link the Forms

The final step is to create a button on Form1 that brings up Form3 with the Report Viewer, and passes the storeID to it.

1. Open Form1 and add a button (btnPrintSelected).



2. Double-click the btnPrintSelected button in the Form Designer to create a Click event in the code window.



3. Within the btnPrintAll_Click event, enter following code:


Dim rpt As New Form3(Me.ComboBox1.SelectedValue)
rpt.Show()




4. Notice in the code above that I am using the ComboBox’s SelectedValue - which we defined earlier as storeID - to the instance of Form3.
5. Build the application and run it by pressing F5. You should be able to select a store in the Combo Box, select the Print Selected button, and view a report of the selected store only.

A Few ReportViewer Preferences

I have a few preferences in the layout of the ReportViewer that I did not add in the body of the tutorial. I will list them here.

1. The first preference is an easy one. I like to dock the ReportViewer in the form.



2. The next preference is to set the default layout to the Print Layout with the document zoomed to the Full Page Width. Add the following code above the RefreshReport command:

Me.ReportViewer1.SetDisplayMode(Microsoft.Reporting.WinForms.DisplayMode.PrintLayout)
Me.ReportViewer1.ZoomMode = Microsoft.Reporting.WinForms.ZoomMode.PageWidth

Summary

This tutorial demonstrated several Visual Basic.NET features:

1. Creating Reports
2. Creating DataTables
3. Adding Queries to a DataTable
4. Passing variables between forms
5. Filtering Report Data