MyODBC Connector
Databinding Step-By-Step
Matthew R. King
mking56@comcast.net
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.
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.
For a tutorial on database design, visit Part 1 of The VB.NET-MySQL Tutorial.
Who am I?
A newbie. I am still learning, and I know enough to know that the steps I outline are not the only way of accomplishing these tasks – and in fact, there are probably better ways of doing it. I welcome feedback.
I have already received valuable feedback in the form of a little history. It seems that professional developers question the performance of Microsoft’s GUI databinding abilities in Visual Studio .NET based on prior experience. The following is a quote from Ernest Bonat, Ph.D.:
1. MySQL Connector/ODBC was created to connect to a MySQL database server using the ODBC database API functions. We used this Connector/ODBC driver in the old days to build Windows applications using VB 6.0. Today, with VB.NET, we don’t use it any more. MySQL has a Connector/NET driver named MySQL .NET Data Provider to build Windows application in .NET platform using VS.NET 2005. I believe, if we’re building Windows or Internet web database applications for MySQL server the native .NET Data Provider is the right one to be used.
2. For many years, Microsoft databinding technologies have not proved good performance for real production applications. Every thing that we built at this time in VS 6.0 was in unbound mode, in other words, we practically wrote the entire application. We know that very well. I know that in .NET, Microsoft has done an incredible job improving these technologies for Windows and Internet. Still these upgrades need to prove in real production environment. Couple of months ago I did some testing bounding a ComboBox with three thousands records from SQL Server 2005 table and it was very slow. In fact, I’m writing a paper now about that.
Dr. Bonat goes on to recommend MySQLDirect.NET from Core Lab as a proven solution for GUI databinding. MyODBC is free however, and MySQLDirect.NET is not. As I am not a professional developer (yet), I will learn with the lowest-cost solution until I am ready to take the next step.
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.
- 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.
- 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 next step is to give access to a user, and to limit permissions.
- Open MySQL Administrator and log on as the root user.
- 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.
- Select the Apply Changes button. “user” should appear under Users Accounts.
- Select the user account and select the Schema Privileges tab.
- Assign this user account the privileges shown in the figure belowto the mystore schemata, and then select the Apply Changes button.
- Close the MySQL Administrator.
- 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.
- Open the Windows Control Panel and double-click the Administrative Tools icon.
- Double-click the Data Sources (ODBC) icon to bring up the ODBC Data Source Administrator, and then select the Add… button.
- Select the MySQL ODBC 3.51 Driver and select the Finish button.
- 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.
- Test your connection by selecting the Test button.
- 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).
- Select the OK button.
- Select the OK button in the ODBC Data Source Administrator to close it.
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');
Create a New Project
- Open Visual Studio and start a new project. I am calling mine “MyStore” in the example below.
- From the Data menu item, select Add New Data Source…
- In the Data Source Configuration Wizard, select the Database object and select the Next > button.
- Select the New Connection… button.
- Select the Change… button on the Add Connection dialog box.
- Select the Microsoft ODBC Data Source and select OK.
- As mentioned previously, there are two connection methods: DSN and Connection String. Both methods are demonstrated here. Use one of them.
- 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.
- 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.
- Select the Next > button in the Data Source Configuration Wizard.
- Rename or accept the Connection String, and select the Next > button.
- Check the box next to Tables to select all tables in the database, accept or rename the DataSet, and select the Finish button.
- 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
- The DataSet will appear in the Solution Explorer. Right-click on it and select View Designer.
- 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…
- The SQL Statement will appear, complete with the offending tick marks and extra period.
- Remove the tick marks and extra period from the SQL Statement and select
the Next > button. - Choose the methods to use with this Table Adapter. In this case, I will use the defaults. Select the Next > button.
- Select the Finish button.
- The table will now display the column names in the Designer.
- Follow the above procedures to correct the SQL Statement for the storeTableAdapter.
- Create a link between the two tables by dragging from the store.storeID element to the employee.storeID element.
- 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.
- The Designer now shows the relationship between the tables.
- 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.
- Right-click on the storeTableAdpater and select Properties.
- In the properties window for the storeTableAdpater, select (New) from the InsertCommand combo box.
- Expand the InsertCommand selections by selecting the + next to it. Click in the CommandText box and click on the ellipses (…).
- Add the store table to the Query Builder by selecting it and selecting the Add button.
- 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.
- Place question marks into the SQL Statement as place holders for parameters you will pass to this command.
- Select the OK button.
- In the properties window for the storeTableAdpater, select (New) from the DeleteCommand combo box.
- Expand the DeleteCommand selections by selecting the + next to it. Click in the CommandText box and click on the ellipses (…).
- Add the store table to the Query Builder by selecting it and selecting the Add button.
- Add the following line to the end of the SQL Statement:
- Select the OK button.
- In the properties window for the storeTableAdpater, select (New) from the UpdateCommand combo box.
- Expand the UpdateCommand selections by selecting the + next to it. Click in the CommandText box and click on the ellipses (…).
- Add the store table to the Query Builder by selecting it and selecting the Add button.
- 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.
- In the SQL command, insert question marks to act as placeholders for parameters passed to the update command. In addition, add the following code:
- Select the OK button.
- 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.
WHERE storeID = ?
This allows you to determine which record requires deleting within the database.
WHERE storeID = ?
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.
- 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
- Right-click on the cboStore combo box and select Properties.
- From the DataSource combo box, drill down to select the store table from the mystoreDataSet.
- Visual Basic will automatically add the DataSet, Table Adapter, and Binding Source to the project.
- From the DisplayMember combo box, select the city element.
- Right-click on the txtCity textbox and select Properties.
- Expand the DataBindings selections by clicking on the + next to it. From the Text combo box, select the city element from the StoreBindingSource.
- 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.
- Press F5 to Build and Run your application.
- You should be able to select the store with the combo box, and see the city, state, and manager change with each selection.
- Close the application.
- Double-click the btnUpdate button in the Form Designer to create a Click event in the code window.
- Within the btnSave_Click event, enter the following code:
- Double-click the btnAdd button in the Form Designer to create a Click event in the code window.
- Add the following code to the btnAdd_Click event:
- Double-click the btnDelete button in the Form Designer to create a Click event in the code window.
- Add the following code to the btnDelete_Click event:
- Press F5 to Build and Run the program.
- 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.
- Close the application
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.
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.
Me.StoreBindingSource.RemoveCurrent()
This code deletes the current record from the DataSet when the btnDelete button is pressed.
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.
- Create another GroupBox (GroupBox2) as shown in the figure below.
- 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.
- Drag the entire employee table into GroupBox2.
- Click on the smart tag at the upper-right corner of the DataGrid and select Edit Columns…
- 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.
- Select the OK button. Your form should look similar to the figure below.
- From the Toolbox, drag the BindingNavigator into GroupBox2.
- Right-click on the BindingNavigator and select Properties.
- From the BindingSource combo box, select the EmployeeBindingSource.
- 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.
- Edit the btnSave_Click event to update both tables in the database and the DataSet by adding the following lines to the existing code:
- Press F5 to Build and Run the application. Practice adding stores and employees.
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.
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
December 6th, 2006 at 7:01 am
This is great. I just wish I found it before I worked through about 80% of it the hard way. There is one more thing you could add which is to let people know to use this code (just learned this tonight):
In it’s own module:
Namespace DataSet1TableAdapters
Partial Public Class StoreTableAdapter
Private Sub _adapter_RowUpdated(ByVal sender As Object, ByVal e As System.Data.Odbc.OdbcRowUpdatedEventArgs) Handles _adapter.RowUpdated
GetAutoIncrementNo(e)
End Sub
End Class
Partial Public Class EmployeeTableAdapter
Private Sub _adapter_RowUpdated(ByVal sender As Object, ByVal e As System.Data.Odbc.OdbcRowUpdatedEventArgs) Handles _adapter.RowUpdated
GetAutoIncrementNo(e)
End Sub
End Class
End Namespace
In a code module:
Module basGen
Private OdbcCmd As New System.Data.Odbc.OdbcCommand(”SELECT @@IDENTITY”, Nothing)
Public Sub GetAutoIncrementNo(ByVal e As System.Data.Odbc.OdbcRowUpdatedEventArgs, Optional ByVal vCol As Object = 0)
If e.StatementType = StatementType.Insert AndAlso e.Status = UpdateStatus.Continue Then
OdbcCmd.Connection = e.Command.Connection
e.Row(vCol) = OdbcCmd.ExecuteScalar
End If
End Sub
End Module
Without it you get errors with delete/insert repetition because the DataSet gets out of sync. with the DB on the primary key unless I am missing something.
If there are any other tricks I’ll need to know please let me know and I’ll keep giving you mine as I come across them.
December 7th, 2006 at 5:54 am
Thanks, Reg.
I did not test this out as much as you seem to have — so upon reading your reply I went straight to the program and tried adding and deleting in succession. Sure enough, I eventually ran into a “EHDBConcurrency violation”. Is this what you are referring to? I can’t seem to find a lot of information on this particular violation, but it gives me more to look at.
Thanks again.
December 7th, 2006 at 6:53 am
That’s the error!
December 8th, 2006 at 1:14 am
I have researched a little and found a sure-fire way of causing the exception:
Bring up both the application and MySQL Query Browser at the same time, with the database selected. Delete a record within the Query Browser, then attempt to delete the same record in the application — it reutrns the Concurrency Violation because there is no more row to delete.
Instead of using your code (which I have to admit I do not understand yet) you could solve the problem by putting the table update command in a try…catch block. All that does is stop the program from crashing, however.
the real trick is to write the code in the catch block that will bypass the offending record and commit the other changes to the database.
December 8th, 2006 at 3:48 am
Here, this is the only page I have found with this solution and it is explained there so you should get an understanding of it. I hope this helps.
http://forums.microsoft.com/msdn/showpost.aspx?postid=11355&siteid=1
December 8th, 2006 at 7:19 am
Thanks, Reg. I understand a little better now, but just a little. I’m a little slow.
In the article, he seems to be discussing how the parent-child relationships can break down. I can see how that might happen when coding everything the way Mike’s example shows — especially the importance of getting the last inserted ID. With databinding, I think the “theory” is that this type of error should not happen, as the bound tables cascade relationships. However, I’ve not heard good things about the reliabilty of databinding, and in the several MySQL/VB applications I have written, I could not get cascading relationships to work, so i always turn that feature off when defining the links within the dataset.
I wrote an application using Connector.NET for work using much of Mike’s example, and as far as I know, there haven’t been any Concurrency Violations. however, only one person makes use of the application.
My concern, and the experiment I mentioned above, focuses on a multi-user environment, where several users may be working from a copy of a data table, then update one after the other, offering different versions of the same table to the database. I am creating an application right now which would have multiple users accessing the same database. I’m curious to see if it is a problem, and figuring out how to resolve it.
December 9th, 2006 at 3:22 am
From what I have read so far, MS really only got Databinding worthwhile in .net 2005. While the article, as you said, might mostly be about child/parent relationships, regardless, the code or code that does the same thing (get the ID from the database on insert and put into the Dataset), should be implemented as even if you are using only one table, if the Dataset Primary Key ID on any table gets out of sync. with the DB’s then you are going to have problems. And, certainly, if you can’t keep the DB and Dataset in sync on the Primary Keys - cascading will fail often!
The reason they get out of sync of course is because the Dataset has it’s own rule for creating values for auto-incremented fields. If it’s rules are not the same as the DB’s it’s going to desync. on certain operations. My guess is, that the rules that VB.net uses for maintaining the field probably matches MS SQL since that’s MS’s flagship database. Certainly it does not match MySQL or the above code would not be needed.
To me, if you are concerned with multiple users, this is something you want to get right first, even for one user, and then you can address the more complex issues with multiple users knowing that you haven’t got single user gremlins lurking around waiting to get you.
Anyway, take and thanks for putting up a great page!!! You saved me probably a day of figuring stuff out and it would have been a lot more if I found this page before I was mostly down the track of making all this go.
December 10th, 2006 at 9:46 am
I think I did you an injustice here. I actually found the original page that I got this solution from. Have a look at this:
http://www.groupsrv.com/dotnet/ntopic153619.html
It will probably fit together much better in your mind than my last reference.
December 10th, 2006 at 2:32 pm
Here’s my last piece on this subject. I’m adding more because the code above in theory should work according to various sources but I have found, at least with a MySQL DB, it doesn’t. So, first here is a comprehensive article on the matter (finally found one):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/manidcrisis.asp
Second, here is my workaround for what doesn’t work as they say it should (at least until I find what I am missing:
This fits into GetAutoIncermentNo mentioned above:
Case StatementType.Insert
If e.Status = UpdateStatus.Continue Then
Dim iPK&, iRow&, lRowToFind&
Dim oTable As System.Data.DataTable
Dim oRow As System.Data.DataRow
‘ Get a new key from the DB
OdbcCmd.Connection = e.Command.Connection
iPK = OdbcCmd.ExecuteScalar
‘ Put it into the row passed to this routine
lRowToFind = e.Row(vCol)
e.Row(vCol) = iPK
e.Row.AcceptChanges()
‘ This is all we should have to do according to msgs all around.
‘ In reality we have to do what’s below as well.
‘ For some reason updating the row does NOT update the row in the
‘ table so update date it ourselves here.
oTable = gDS.Tables(e.Row.Table.TableName)
If oTable.PrimaryKey.Length 1 Then ‘ 0=no primary key > 1 = Compound primary key. We are only equiped to handle single column PK’s right now
For iRow = oTable.Rows.Count - 1 To 0 Step -1 ‘ Most likely we’ll find what we want at the end so start there.
If oTable.Rows(iRow).Item(vCol) = lRowToFind Then
oTable.Rows(iRow).Item(vCol) = iPK
oTable.Rows(iRow).AcceptChanges()
Exit For
End If
Next
Else
‘ Use Primary key if can for efficiency
oRow = oTable.Rows.Find(iPK)
If Not IsNothing(oRow) Then
oRow(vCol) = iPK
oRow.AcceptChanges()
End If
End If
Debug.Print(”Ins PK: ” & iPK)
End If
I’ve only tested this for today but I think its solid as I spent all day soaking up what I needed to know to make it work.
December 10th, 2006 at 5:22 pm
No injustice at all — I’ve learned a lot in the last week. I’ve always known there were considerations with multi-user databases, but until now haven’t spent the time to learn them. This article definitely adds to the understanding.
Here is the most straight-forward article I’ve seen on the subject:
http://msdn2.microsoft.com/en-us/library/ms171936(VS.80).aspx
After I learn more maybe i will create my own here.
I have come up with my own solution for the application I am putting together for work. It is rather simplistic, but it should work as only a few people will have permission to change anything, and in different areas of the database. I do not expect them to run over each other’s toes all that often. Here is the pseudo-code of it:
Try
TableAdapter.update
catch ex
MsgBox(”Error: ” & ex.message & LineFeed & “Some changes may not have been saved. Try again…”)
TableAdapter.Fill
End Try
In this case, in the event of a Concurrency Violation, The user will see the message and the databound controls will re-fill with the most current data. The user can then correct any mistakes, and save again.
I think this adequate for the application I am putting together, but in the future I will definitely need something more robust.
December 11th, 2006 at 2:50 am
Hi,
I think it is a great tutorial, it is like I saw something like open your Visual Basic 2005 Express Edition, that’s exactly what I have, but when I tried to select the ODBC connector happens that it is not allowed. Do you know any way to get this working with this edition, maybe I have to write all the code but don’t know how.
Any help will be appreciate.
Bye.
December 11th, 2006 at 4:04 am
Thanks Yesid.
I am using Visual Studio 2005 Academic (I’m in school). I’ve tried the free Express verison — and if I remember correctly — it does not have the same database connection options. It was very limited.
In this tutorial, step 6 under “Create A New Project” shows a screenshot of the different types of Data Sources available. I seem to recall that the same screen in Express did not have that many options.
If this is true (you should check), then I am not sure how to add the connector.
December 18th, 2006 at 2:50 pm
Hi Matthew, I should have responded sooner but was caught up in work. Your solution is good for simple situations you outlined and I might adopt it for an app. I am working on now. Good job.
Be aware though that the concurrency issue you are solving is different to what the code I posted solves. The code I posted resolves the issue of the DataSet sending an insert to the DB but not updating it’s own DataSet with the Primary Key generated by the DB. That’s why the successive delete fails, it’s sending the Primary Key in in the WHERE part of the Delete SQL the DataSet generated which doesn’t exist in the DB and the DB naturally returns an error. The reason it will work once or twice before failing is because the DataSet and DB do, by default, keep in sync. once or twice.
It is very dangerous to not address this problem because you will, without doubt, have records get deleted that were not meant to if you do not address this. Consider: the Dataset generates a PK of 1000 and the DB for the same records creates a PK of 1001 because 1000 already exists. If you then do a delete on that record the Dataset will send a PK of 1000 - and a totally unrelated record will be deleted in the Db and no error generated.
The way you stop this “clashing” of PK’s is in the DataSet you set all primary keys to step -1 (decrement) and start at -1. That way the your DataSet will never generate a PK that matches the DB, and of course, you will be forced to retrieve the real PK from the DB to keep your DataSet and DB in sync.
December 22nd, 2006 at 11:48 pm
Reg,
Thanks for lighting a fire for me. I now know more about Concurrency Violations than I thought I would. I’ve added a new section to this tutorial that introduces a routine I wrote that is capable of handling several different types of Cuncurrency Exceptions with one update. I just hope it works
January 16th, 2007 at 5:17 pm
Hi!My problem is that in the “Select the Microsoft ODBC Data Source and select OK.” in the step 6 in the new data source connection is not available in my visual studio express and the driver is installed.What can be the problem?I’m desperate
January 17th, 2007 at 1:37 am
Montix,
I don’t think VS.NET Express allows that. I used VS.NET Express for a while, and could not find the solution you are seeking — so I upgraded to an Academic version (it’s OK, Mr. Gates, I’m a student).
If there is a VS.NET Express solution, I could not find it. I even bought VS.NET Express for Dummies, and the relevant screen shots never showed MS ODBC Data Source as a selectable item — so I am assuming it is one of those features you do not get for free.
Sorry I could not help…
January 17th, 2007 at 6:24 am
Matthew:
OK!We’re going to other version 
Thank you very,very much to answer my question.I also think is a Express Edition’s problem
Thank you!
January 18th, 2007 at 11:35 am
Hi!I wish to know how create a form in this application to login the database first to load the main form.I mean i’m interesting to control the application’s access.
Thank you!
January 19th, 2007 at 3:13 am
Montix,
I’ve done this in various projects, but never thought to put it nto a tutorial. It might make a good one. However, I honestly don’t have plans for doing that in the near future. I am a little bogged down right now with other projects.
January 26th, 2007 at 4:17 pm
Well, this is a great tutorial in developing Visual Basic.NET apps based in mysql database. It saved me all the nightmare working with SQL statements directly and moving around MySQL DataReader objects, e.t.c.
I wish there is a nice tutorial like this on how to use ReportViewer.
January 26th, 2007 at 4:48 pm
Hamisi,
Thanks for the compliment. As far as ReportViewer, here you go: http://www.vbmysql.com/articles/vbnet-mysql/myodbc-connector/creating-reports/
If you go to the Articles menu of this site, you will find several tutorials, including a fine one on using MySQL Connector/Net.
For this tutorial, using the MyODBC Connector, I’ve added tutorials on ReportViewer, Windows Installer, and Concurrency Violations. I hope you find these helpful.
Matt
February 1st, 2007 at 8:39 am
Thanks King,
I have one more question. I have a (calls) table that references three other tables (status,calling_person and refererral type) for the help desk application. I want to insert data into the calls table with the foreign keys from the other three tables. I want to link the three foreign keys in the controls in the data entry form so that the bindingnavigator save button updates the calls table with correct values.
How can I do this?
Hamisi
——–
February 1st, 2007 at 10:49 pm
Hamisi,
It is a little difficult to come up with the exact answer here. However, here are some generalities:
1. If the tables are linked in the DataSet, and are properly linked in the form (as in the tutorial above), Visual Studio will take care of the Foriegn Keys for you.
2. If linking within the form is not practical, you may have to create a query (for each table) to find the Primary Keys in the child tables based on a WHERE clause. In my tutorial on creating reports, I show how to add queries to DataTables. You could make a Scalar Query which reutrns a single value (the Primary Key) and save that as a variable, then use that variable as a Foriegn Key when inserting the new record.
February 2nd, 2007 at 7:08 am
Thanks Mathew.
I am looking at the code and try the methods.
February 6th, 2007 at 7:46 am
Hi Kings,
Thanks for the tutorial which is clearly presented. I have one issue which I have not figured out despite my intensive search on the Internet. I have a form which has several controls (TextBoxes, ComboBoxes, etc) that I use as my data capturing tool which eventually feeds the database. I want now to separate the business logic from data services using Stored Procedures of MySQL. I want all my SQL statements to reside in the stored procedures but I don’t know how to send Visual Basic objects to Stored Procedures so that I can get the object’s attributes in the stored procedure for Insert query for example. I want this so that once the client is in place every change to the database should not affect clients because I will be dealing with store procedures at the server side instead of modifying clients and resintalling them on the distributed systems.
Thanks,
Hamisi
——–
February 6th, 2007 at 5:08 pm
Hamisi,
I’m afraid I am not much help on Stored Procedures. Dr. Bonat, who regularly visits the forums on this site, is big on Stored Procedures and can probably help you.
I know it is a topic I need to learn, for the very reason you pointed out — but I haven’t had the time lately.
February 7th, 2007 at 6:27 am
Mathew,
Thanks for the hint on Dr. Bonat. What is the easiest way to contact Dr. Bonat other than wait until he sees the post?
Hamisi
——–
February 7th, 2007 at 4:49 pm
Hamisi,
I’m not sure if he would like me posting his e-mail (spammers) — but if you join the forums in this site, you can look up the Members List and email him from there. His username is ebonat2006.
May 1st, 2007 at 12:11 am
This has proved to be the thing I have spent far too many hours looking for. It finally clears up a lot of problems I have been having and steps away from other things while using built in commands. However, I was able to make it to Designing the DataSet : step 6, but right there I had an error. Instead of populating with the table names I got : Yellow triangle & ! “Generated Select statment. ERROR[HY010][MYSQL][ODBC 3.51 Driver][mysqld-5.0.37-community-nt]”
I am wondering if I have something differnt then ODBC 3.51 as my driver. But all the other things worked and it worked when I tested it using the button back when you showed the connection string. In any case this information has helped me out more then anything else I have found online.
May 1st, 2007 at 2:01 am
Daniel,
I appreciate the compliment. The time I put into this has proven very helpful — not just to others but myself as well.
As to your error — you could be correct about your driver — or — there was something wrong with the SELECT statement. One little tick ( ` ) and the whole thing is off — and VB.NET adds extra tick marks and periods in the SELECT statement that need to be removed.
May 2nd, 2007 at 2:38 pm
I have been looking at all of my code and your steps and at the links you have provided. From this I have made sure that all the ( ‘ ) have been removed, so that should not be the problem (SELECT modeninfo.* FROM test.modeminfo). But After looking at everything, I have to ask, in the connection string, what does Option=3 do? And since the code heavy link you provided at the top appears to be very useful, I think I will try to have it all in code becuase I will not be data binding the database to any controls and just working with select statements to grab what I need as I need it.
May 2nd, 2007 at 2:53 pm
I honestly do not know what the OPTION=3 is all about. However, all MyODBC connection string examples I have seen use it, so I do as well.
If you are not planning on using databinding and intend to do everything with SELECT statements, you may want to switch to the MySQL Connector/NET driver mentioned at the top of the article. The owner of this site, Mike Hillyar, has excellent examples on how to use it. The MySQL Connector/NET is newer than the MyODBC driver, and designed specifically for .NET.
May 2nd, 2007 at 4:30 pm
Thank you. His tutorials are proving to be as useful as your work was. I am finally making progress concerning this aspect of the program.
May 21st, 2007 at 9:06 pm
Hi I did all the steps but when I try to configure the table I Remove the tick marks and extra period from the SQL Statement, the application give me this error:
ERROR [HY010][MySQL][ODBC3.51 Driver][mysqld-5.0.24a-community-nt-log]
June 15th, 2007 at 3:17 am
how to use Like in dataset
June 15th, 2007 at 3:18 am
how to use Like in dataset
i am using mysql database
July 1st, 2007 at 7:37 am
hello ,i follow the article step by step to connect mysql db to vb.net using ODBC v3.5 but i face a problem of configure the data tableadpter after i debuge the error that u said .the table feilds didnt appear and an error message tell me
“”the wizard detected a problemm when configuring table adpter :”fill” details : the generated select statment .error [HY010][MYSQL][MYODBCV3.51]column count unknown at this time “”
please help
July 1st, 2007 at 4:45 pm
Mahmoud,
It is difficult to troubleshoot something like this from a distance, but it sounds to me like there is a problem in your SELECT statement. Since VS.NET was not made with MySQL in mind, it has problems generating the correct statements and you have to do it manually — which leaves room for error.
Does it happen on both tables, or just one?
If you reply, perhaps you should copy and paste the SELECT satatements from your Table Adapters into your reply — that might help find the problem.
July 10th, 2007 at 10:44 pm
Having the same problem as Laura and Mahmoud ERROR [HY010][MySQL][ODBC3.51 Driver][mysqld-5.0.24a-community-nt-log]
Install mysql-connector-odbc-3.51.12-win32 instead of mysql-connector-odbc-3.51.16-win32 and thngs will work as described.
July 11th, 2007 at 12:20 am
Thank you, Geert!
August 18th, 2007 at 2:51 am
hi
I am not getting trough with STEP 6 in ‘CREATE A NEW PROJECT above i have already configured ODBC from the control panel and added the ODBC driver to the USER DSN and SYSTEM DSN however when i open VB205 and i create a new project and i go from step 3 when i reach step 6 to change the data source in the dialog box i am not seeing the option to select the Microsoft ODBC DataSource insted i am seeing ‘Microsoft Access Database File’ and ‘Microsoft SQL Server Database File. Please help me out here
Regards
Darr
August 18th, 2007 at 3:11 am
Hi darr,
I believe the problem is that you are using the Express version of VB2005. The free version does not allow you to select the MS ODBC DataSource.
August 20th, 2007 at 10:45 am
HI MATTHEW KING
which version should i use?
Thanks in Advance
Best Regards
August 20th, 2007 at 2:49 pm
Darr,
Basically, any version that is not free. I’m using the “Academic” version, which works fine, and then there is probably a “Standard” and “Professional” versions which I’m sure will also work. I know the “Express” version, however, will not.
September 19th, 2007 at 11:55 am
hello i am turkish
eror
4 Fill in the Connector/ODBC dialog box that pops up next.
selam aleyküm;
ben VB.NEt te MySQL bağlantısı yapmak için
mysql-connector-odbc-
kulanarak odbc kaydı yapmak istiyorum ama bir türlü yapamadım localhost ta denedim olmad bir kaçtane mysql hostta denedim olmadı aşağidaki hatayı veriyor
image http://img221.imageshack.us/img221/5504/denemejr8.jpg
September 19th, 2007 at 1:50 pm
Kenan,
I’m not sure what the error is. Perhaps you are using a newer version of MySQL ODBC Connector that has a different screen? Or maybe you need to re-install it?
The step 4 you are at is optional, however. You do not have to create a DSN to make it work. Instead, you can create a Connection String, which is described later in the tutorial.
Matt
October 16th, 2007 at 12:41 am
Thank you Mathew for an informative tutorial.
I am an experienced programmer, but that doesn’t help when you are using arbitrarily arranged wizards like in VS2005! One note: if you enter ? marks in the New Value columns (for assign), and Filter (for the where clause) the wizard will generate the SQL statements for you. Just a little short cut.
Also, MS has published SQL Server 2005 express for free. It seems like a very robust version and will serve many people’s needs quite well.
And its FREE. That might help with some of the data concurrency issues (Not sure if it will solve all the issues, as I haven’t got that far in my first VS project).
Thanks again
Jeff
May 30th, 2008 at 4:01 am
kok ngak adas tempat untuk ngedownload sih