MyODBC Connector: Databinding Step-By-Step

Purpose

This step-by-step tutorial takes the reader through the process of using databound controls in VB.NET using the MyODBC Driver to connect to a MySQL database. We will start by creating a very basic and simple MySQL database, then connect to it and bind controls from within Visual Basic.

For the technical description of the MySQL Connector/ODBC, read this section of the MySQL Reference Manual 5.1.

For a code-heavy example of using Connector/ODBC with Visual Basic, read this section of the MySQL Reference Manual 5.1.

Why?

While I have found plenty of fine tutorials for using MySQL and VB.NET, I have not found one that specifically covers the MyODBC Driver in enough detail for me to do what I wanted to accomplish. I found my way through it, and want to document the steps, as much for myself as well as anyone else who may benefit from it.

Many are aware of Michael Hillyar’s excellent tutorials using the MySQL Connector/NET to connect VB.NET to a MySQL database. If you are not, you really should be. I learned a lot by following along with his tutorials. Please note that this tutorial also appears on his site.

I developed a database application using Connector/NET, and tired of writing SQL Statements. I wanted a connector that could be used with VB.NET’s cool databound controls – mostly because I am lazy. I tried playing with MyODBC, and while not perfect, it gets me closer to where I want to be when it comes to Rapid Application Design.

For a helpful discussion about connecting MySQL and Visual Studio .NET using MyODBC, visit this thread.

Many tutorials I have followed tend to throw in a lot of extra code, such as formatting of labels and text boxes, naming forms, and throwing in extra tools. Of course, the purpose of which is to demonstrate good programming practices. I want to skip all of that and focus on databinding. This tutorial assumes you are already coherent in database design and Visual Basic programming.

Please note that this tutorial requires the full version of Visual Studio .NET. The Express (free) version does not allow you to connect to a MySQL Database throught the MyODBC Connector.

For a tutorial on database design, visit Part 1 of The VB.NET-MySQL Tutorial.

Platform

The topology in the figure below shows the computers and software I am using.

Of course, MySQL runs fine under Windows, so it is not necessary to use a separate computer for the MySQL Server. Regardless of your configuration, this tutorial should work as long as you know how to connect to your MySQL Server.

Software Downloads

• MySQL Connector/ODBC 3.51. Users with version 5.0 have had some problems with this tutorial.
• MySQL GUI Tools for Windows and Linux.
• MySQL 5.0 for Windows or Linux.

Initial Steps

Warning – I am breezing through these steps. I want to get to the “meat” of the tutorial, which is the databound controls. Besides, most of the downloads come with installation instructions, and Mike Hillyar explains most of this better than I can.

For a tutorial on downloading and installing MySQL and MySQL Query Browser, visit Part 2 of The VB.NET-MySQL Tutorial.

For a tutorial on downloading and installing Visual Basic 2005 Express and MySQL Administrator, visit Part 3 of The VB.NET-MySQL Tutorial.

1. The first step is to set up the MySQL database. I am going to create a real simple database named “mystore” with only two tables – one for store locations, and one for employees. The Entity Relationship Diagram follows:

   

   The following code creates the database and tables, as well as putting in a few stores and employees for practice. You can copy and paste the code into the MySQL Query Browser and execute it (assuming the Query Browser is properly setup):

   use mysql;
   drop database if exists mystore;
   create database mystore;
   use mystore;

   create table store
   (
   storeID int unsigned auto_increment primary key,
   city char(50),
   state char(2),
   manager char(50)
   );

   create table employee
   (
   employeeID int unsigned auto_increment primary key,
   storeID int unsigned not null references store(storeID),
   lastname char(50),
   firstname char(50)
   );

   insert into store values
   (default,'San Diego','CA','Russell Smith'),
   (default, 'St. Louis', 'MO','Jim Marks');

   insert into employee values
   (default,1,'Jones','Bill'),
   (default,1,'Collins','Howard'),
   (default,2,'Johnson','Cindy'),
   (default,2,'Gonzales','Hector'),
   (default,2,'Hanson','Danny');

   

2. The next step is to give access to a user, and to limit permissions.
   1. Open MySQL Administrator and log on as the root user.
   2. Select User Administration –> New User. In the example below, I named the MySQL User as “user”, and the password is “user”. This user name and password is used to establish the connection.

      

   3. Select the Apply Changes button. “user” should appear under Users Accounts.
   4. Select the user account and select the Schema Privileges tab.
   5. Assign this user account the privileges shown in the figure belowto the mystore schemata, and then select the Apply Changes button.

      

   6. Close the MySQL Administrator.


3. This next step creates the ODBC connection for VB.NET (assuming the MySQL ODBC Connector is properly installed).

   This step is optional. Creating a Data Source Name (DSN) provides a convenient connection method for the computer developing the application. However, if the finished application is to be installed on other computers, this step will need to be repeated for each computer. The alternative is to use a Connection String, which will be covered later.

   1. Open the Windows Control Panel and double-click the Administrative Tools icon.
   2. Double-click the Data Sources (ODBC) icon to bring up the ODBC Data Source Administrator, and then select the Add… button.

      

   3. Select the MySQL ODBC 3.51 Driver and select the Finish button.

      

   4. Fill in the Connector/ODBC dialog box that pops up next.
      • The Data Source Name (DSN) and Description can be anything you want.
      • The Server needs to be the name of your MySQL Server (linuxbox, in my case).
      • Enter the User and Password for your database (user, and user for me), and then select the Database (mystore) from the list.

        

   5. Test your connection by selecting the Test button.
   6. Assuming it passed, select the Advanced tab, and check the boxes shown in the figure below (I’m not sure why, but it is recommended in several tutorials).

      

   7. Select the OK button.
   8. Select the OK button in the ODBC Data Source Administrator to close it.

Create a New Project

1. Open Visual Studio and start a new project. I am calling mine “MyStore” in the example below.

   

2. From the Data menu item, select Add New Data Source…

   

3. In the Data Source Configuration Wizard, select the Database object and select the Next > button.

   

4. Select the New Connection… button.
5. Select the Change… button on the Add Connection dialog box.

   

6. Select the Microsoft ODBC Data Source and select OK. If you are using the Express (free) version of Visual Studio, you will not be able to continue.

   

7. As mentioned previously, there are two connection methods: DSN and Connection String. Both methods are demonstrated here. Use one of them.
   1. Data Source Name (DSN)
      • Advantage: Convenient connection by DSN, without worrying about the connection string, which some find complex.
      • Disadvantage: The DSN will need to be defined on each computer on which the application is installed.

      Select your DSN (myodbc-mystore in my case) from the combo box and select OK. User name and Password are not needed here, as they are part of the DSN.

      

   2. Connection String
      • Advantage: Installation on client computers is simpler, as they do not require a DSN setup.
      • Disadvantage: Defining the connection string.
      • In this example, the Connection String would be:

        DRIVER={MySQL ODBC 3.51 Driver}; SERVER=linuxbox; PORT=3306; DATABASE=mystore; OPTION=3

      • An excellent source for Connection Strings: http://www.connectionstrings.com/

      Enter the Connection String, User name (user), and Password (user) in the appropriate text boxes and select OK.

      

8. Select the Next > button in the Data Source Configuration Wizard.

   

9. Rename or accept the Connection String, and select the Next > button.

   

10. Check the box next to Tables to select all tables in the database, accept or rename the DataSet, and select the Finish button.

    

11. With a default MySQL configuration, the database will not understand the double quotes used by the DataSet. There is a fix at the MySQL side of the equation, but I elect to change the DataSet (next section). Select the OK button to move on.

    

Designing the DataSet

1. The DataSet will appear in the Solution Explorer. Right-click on it and select View Designer.

   

2. Both tables will be displayed in the Designer, but they will be empty because of the issue with double quotes. In order to fix this, right-click on the employeeTableAdapter and select Configure…

   

3. The SQL Statement will appear, complete with the offending tick marks and extra period.

   

4. Remove the tick marks and extra period from the SQL Statement and select
   the Next > button.

   

5. Choose the methods to use with this Table Adapter. In this case, I will use the defaults. Select the Next > button.

   

6. Select the Finish button.

   

7. The table will now display the column names in the Designer.

   

8. Follow the above procedures to correct the SQL Statement for the storeTableAdapter.
9. Create a link between the two tables by dragging from the store.storeID element to the employee.storeID element.

   

10. To keep it simple, I will keep this link as a Relation Only. Select the OK button.
    • I have had trouble maintaining Referential Integrity using this connector. I may be missing something.

    

11. The Designer now shows the relationship between the tables.

    

12. The MyODBC Driver will now allow these SQL Select Statements to fill databound controls within Visual Basic .NET forms.

Create the Insert, Delete, and Update Methods

The MyODBC Driver will not automatically create Insert, Delete, or Update statements for the Table Adapters. If you wish to alter the database from a Visual Basic application, you will have to create these statements yourself.

1. Right-click on the storeTableAdpater and select Properties.

   

2. In the properties window for the storeTableAdpater, select (New) from the InsertCommand combo box.

   

3. Expand the InsertCommand selections by selecting the + next to it. Click in the CommandText box and click on the ellipses (…).

   

4. Add the store table to the Query Builder by selecting it and selecting the Add button.

   

5. Select the columns to insert by placing a check next to the column names in the store table. You do not need to insert the storeID, as it is the Primary Key and auto-increments.

   

6. Place question marks into the SQL Statement as place holders for parameters you will pass to this command.

   

7. Select the OK button.
8. In the properties window for the storeTableAdpater, select (New) from the DeleteCommand combo box.

   

9. Expand the DeleteCommand selections by selecting the + next to it. Click in the CommandText box and click on the ellipses (…).

   

10. Add the store table to the Query Builder by selecting it and selecting the Add button.

    

11. Add the following line to the end of the SQL Statement:

    WHERE storeID = ?

    This allows you to determine which record requires deleting within the database.

    

12. Select the OK button.
13. In the properties window for the storeTableAdpater, select (New) from the UpdateCommand combo box.

    

14. Expand the UpdateCommand selections by selecting the + next to it. Click in the CommandText box and click on the ellipses (…).

    

15. Add the store table to the Query Builder by selecting it and selecting the Add button.

    

16. Select the columns to update from the store table by selecting the check boxes next to them. You do not need to update the storeID, as it is the Primary Key.

    

17. In the SQL command, insert question marks to act as placeholders for parameters passed to the update command. In addition, add the following code:

    WHERE storeID = ?

    

18. Select the OK button.
19. Perform all of the previous steps for the employeeTableAdapter.
    • The only difference is that employeeID is the Primary Key for the employee table, and needs to be used in the WHERE clause instead of storeID.
    • The storeID column is a Foriegn Key in the employee table.
    • The three screenshots below show the final SQL Statements for the employee table.

      

Databinding with Combo Boxes and Text Boxes

Now that the Table Adapters have the basic queries (Select, Insert, Delete, Update), we can start binding them to controls in a Visual Basic form.

Please realize in the following instructions that I am not particularly concerned with how the form looks, only how it acts. I have used Labels and Text Properties to identify the Name Property for each control.

In addition, if this were a real application, I would provide for validation of user input before attempting to commit input to the database. As it is, incorrect inputs will cause this simple application to crash.

1. Design a form with the same components as the one shown below.
   • 1 Group Box (GroupBox1)
   • 1 Combo Box (cboStore)
   • 3 Text Boxes (txtCity, txtState, txtManager)
   • 3 Buttons (btnDelete, btnAdd, btnSave

   
2. Right-click on the cboStore combo box and select Properties.

   

3. From the DataSource combo box, drill down to select the store table from the mystoreDataSet.

   

4. Visual Basic will automatically add the DataSet, Table Adapter, and Binding Source to the project.

   

5. From the DisplayMember combo box, select the city element.

   

6. Right-click on the txtCity textbox and select Properties.

   

7. Expand the DataBindings selections by clicking on the + next to it. From the Text combo box, select the city element from the StoreBindingSource.

   

8. Perform steps 6 and 7 above for the txtState and txtManager text boxes, where txtState will be bound to the state field and txtManager will be bound to the manager field.
9. Press F5 to Build and Run your application.
10. You should be able to select the store with the combo box, and see the city, state, and manager change with each selection.
11. Close the application.
12. Double-click the btnUpdate button in the Form Designer to create a Click event in the code window.

    

13. Within the btnSave_Click event, enter the following code:

    
    Me.Validate()
    Me.StoreBindingSource.EndEdit()
    Me.StoreTableAdapter.Update(Me.MystoreDataSet.store)
    

    This code will commit changes to the database from the DataSet when the btnSave button is pushed, and then refresh the DataSet, as well as all databound controls.
    Closing the application without pressing this button will not save changes to the database.

    

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

    

15. Add the following code to the btnAdd_Click event:

    Me.StoreBindingSource.AddNew()
    

    This code adds a new record to the DataSet when the btnAdd button is pressed, providing blank textboxes for adding new information.

    

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

    

17. Add the following code to the btnDelete_Click event:

    
    Me.StoreBindingSource.RemoveCurrent()
    

    This code deletes the current record from the DataSet when the btnDelete button is pressed.

    

18. Press F5 to Build and Run the program.
19. Practice adding and deleting stores. Remember that incorrect entries (such as using more than 2 characters for a state) will cause the application to crash as we have intentionally left out data validation.
20. Close the application

Databinding with a DataGrid

By far, the easiest databinding is with a DataGrid, although I do not consider it very user-friendly on the client side. However, I am going to add a wrinkle to this one – I am going to link it to the selected store to only show (and edit) the employees in that store.

1. Create another GroupBox (GroupBox2) as shown in the figure below.

   

2. In the Data Sources window, find the employee table that is a subset of the store table. The figure below shows two employee tables, but only one is a subset of store.

   

3. Drag the entire employee table into GroupBox2.

   

4. Click on the smart tag at the upper-right corner of the DataGrid and select Edit Columns…

   

5. I do not want to show the employeeID or storeID in the DataGrid, as these will not be editable anyway. Select each and select the Remove button to remove these columns from the DataGrid.

   

6. Select the OK button. Your form should look similar to the figure below.

   

7. From the Toolbox, drag the BindingNavigator into GroupBox2.

   

8. Right-click on the BindingNavigator and select Properties.

   

9. From the BindingSource combo box, select the EmployeeBindingSource.

   

10. Since I already have a Save Button (btnSave), I can use it to save information in both tables. Double-click the btnSave button to jump to the Click event.
11. Edit the btnSave_Click event to update both tables in the database and the DataSet by adding the following lines to the existing code:

    Me.EmployeeBindingSource.EndEdit()
    Me.EmployeeTableAdapter.Update(Me.MystoreDataSet.employee)
    
    Notice in the figure below that I put the new commands after the commands to update the store table. this is on purpose, as the employee table is a child of the store table, the store table needs to be updated first.

    

12. Press F5 to Build and Run the application. Practice adding stores and employees.

Summary

I think I accomplished what I set out to do with this tutorial – to create a simple step-by-step approach to completing the task of connecting the MyODBC Connector to a Visual Basic application. The finished product is by no means pretty, or even useful, except to demonstrate how to use the nifty databinding abilities in Visual Studio with the MyODBC Connector.

Reading Resources

Microsoft® ADO.NET 2.0 Step by Step
By Rebecca M. Riordan

Professional ADO.NET 2: Programming with SQL Server 2005, Oracle, and MySQL
By Wallace B. McClure, Gregory A. Beamer, IV John J. Croft, J. Ambrose Little, Bill Ryan, Phil Winstanley, David Yack, Jeremy Zongker