This tutorial will guide you on how to connect MySQL Database to your visual basic 2010 express. Visual basic 2010 express doesn’t show a wizard connection to MySQL database; please see the picture in figure 1 it shows 3 data sources only Microsoft Access Database File, Microsoft SQL Server Compact 3.5 and Microsoft SQL Database File. It means you cannot connect your MySQL Database file using the data source wizard.
Figure 1.
Follow these few steps to connect your MySQL Database to Visual Basic 2010 Express
1. Download and install the MySQL Connector Net 6.4.3 which is can be downloaded at this link
http://dev.mysql.com/downloads/connector/net/
2. Open your visual basic 2010 express and create a new project as shown in the picture below.
3. Next step, go to the ‘Project’ menu and click the ‘Add Reference’, check the picture below.
4. Add Reference form will show and by default Project tab, select the Browse tab and locate the MySql.Data.dll
For Windows Vista file located at Program Files->MySQL->MySQL Connector Net 6.4.3->Assemblies->v4.0->MySql.Data.dll
For Windows 7 file located at Program Files (x86)->MySQL->MySQL Connector Net 6.4.3.->Assemblies->v4.0->MySql.Data.dll
5. After adding the MySql.Data.dll reference then you are now ready to connect your MySQL database with visual basic 2010 express. Please try it to yourself or you may follow the sample code provided below.
‘Code for VB.Net 2010 Express
Imports MySql.Data.MySqlClient
Public Class Form1
Dim con As MySqlConnection = New MySqlConnection(“Data Source=192.168.1.104;Database=test;User ID=root;Password=mypassword;”)
Dim sql As MySqlCommand = New MySqlCommand(“SELECT * FROM Employee”, con)
Dim ds As DataSet = New DataSet()
Dim DataAdapter1 As MySqlDataAdapter = New MySqlDataAdapter()
Dim Comb As MySqlCommandBuilder
‘Please add datagridview to your form and name it datagrid1
‘This will display the data to datagrid view
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
con.Open()
DataAdapter1.SelectCommand = sql
DataAdapter1.Fill(ds, “Employee”)
DataGridView1.DataSource = ds
DataGridView1.DataMember = “Employee”
con.Close()
End Sub
‘Update database. Change any data from datagrid then click this button it will save any changes from the grid
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Comb = New MySqlCommandBuilder(DataAdapter1)
Dim i As Integer = DataAdapter1.Update(ds.Tables(“Employee”))
MessageBox.Show(“modify the number “ & i.ToString & ” rows”)
End Sub
‘Sample insert record to MySQL database
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim MyCommand As New MySqlCommand
con.Open()
MyCommand.Connection = con
MyCommand.CommandText = “INSERT INTO Employee(IDNo,FirstName,LastName,email) VALUES( ‘” & txtID.Text & “‘,’” & txtfirstname.Text & “‘,’” & txtlastname.Text & “‘,’” & txtemail.Text & “‘)”
MyCommand.ExecuteNonQuery()
con.Close()
MsgBox(“Record successfully added!”)
End Sub
End Class
