Saturday, November 5, 2011

Visual Basic 2010 Express Connect to MySQL Database



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 theMySql.Data.dll
For Windows Vista file located at c:Program FilesMySQLMySQL Connector Net 6.4.3Assembliesv4.0MySql.Data.dll
For Windows 7 file located at c:Program Files (x86)MySQLMySQL Connector Net 6.4.3Assembliesv4.0MySql.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 and you may follow the sample code providedbelow.
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.ObjectByVal e As System.EventArgsHandles 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.ObjectByVal e As System.EventArgsHandles 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.ObjectByVal e As System.EventArgsHandles 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

No comments:

Post a Comment

Post a Comment