Inserting MySQL Records Via ODBC With Visual Basic .NET
We tried a bunch of different methods to try and access a MySQL database via ODBC for our AreWeDown service. The code below is available here in a fully functioning application that starts as a service. The most reliable method we found to connect to MySQL databases from Windows clients via a VB app appears to be a wee bit dated. We were unable to get the newer libraries to work quite right, so we went back to the older Microsoft.Data.Odbc.
To start with, you will need .NET framework. You also need the MySQL ODBC client and the ODBC .NET Data Provider from these sources:
MySQL Connector/ODBC 3.51 Downloads
ODBC .NET Data Provider
Microsoft Data Access Components (MDAC) version 2.6 is required, and 2.7 is recommended. Version 2.8 SP1 works, and is available here.
Instructions for installing and configuring the MySQL ODBC client are here, and here.
After you add the ODBC .NET Data Provider, you need to make sure that the Microsoft.Data.Odbc namespace is checked by customizing the toolbox in the Visual Basic .NET IDE. Right-click on the toolbox and choose Add/Remove Items. Also, you may need to add the reference under Project->Add Reference. I used Microsoft.Data.ODBC.dll version 1.0.3300.0.
You will need these imports:
Imports System.ServiceProcess
Imports System
Imports Microsoft.Data.Odbc
|
Here is the VB code that will insert a record into a MySQL database table:
Dim MyConString As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=hostname;" & _
"DATABASE=databasename;" & _
"UID=user;" & _
"PASSWORD=password;" & _
"OPTION=3;"
Dim MyConnection As New OdbcConnection(MyConString)
MyConnection.Open()
Dim MyCommand As New OdbcCommand
MyCommand.Connection = MyConnection
MyCommand.CommandText = "INSERT INTO tablename VALUES("val1","val2","val3)"
MyCommand.ExecuteNonQuery()
MyConnection.Close()
|
This was written in Visual Basic .NET 2003. The connection string is in the code in clear text. This worked well in this case because the data wasn't really that sensitive, and we gained some ease of client installation by coding it this way.
We found these resources quite useful in figuring out how to do the above:
The VB.NET-MySQL Tutorial - Part 6
Creating a SQL Server Database Programmatically using VB.NET
OdbcCommand.ExecuteNonQuery Method (System.Data.Odbc)
.NET Framework Class Library: OdbcConnection Class
.NET Framework Class Library: SqlConnection Class
MySQL Reference Manual :: 25.1.20.2 ODBC.NET: VB
Understanding ODBC .NET Data Provider
Configuring ISA Server 2000 MySQL Database Logging
PHP Tutorial - Inserting data into a MySQL Database
Using MySQL with Visual Studio
|
|