I’m trying to get interesting with MySQL to connect my Visual Basic.Net. First, you will need to download the MySQL Connector/Net from http://dev.mysql.com/downloads/connector/net/ and http://dev.mysql.com/downloads/workbench/5.2.html for the MySQL GUI Tools Bundle (Administrator, Query Browser, and Migration Toolkit). Once the connector is installed, now begin a new project in VB.Net. Choose "Add Reference" from the Project menu, then select "Browse" and browse to the installation folder where the connector was installed, choose "MySQL.Data.dll". Now imports the Connector/NET to use its Namespace shown below.
Imports MySql.Data.MySqlClient
Public Class Form1
It includes how connect MySQL to VB.NET and it enable to SELECT, UPDATE, DELETE, and UPDATE records. Then, it can perform following tasks such as ADD, DELETE, UPDATE, SAVE, and SEARCH records that populate using ListView.
In other tutorials, they use DataGridView because that’s the easy way how to load data from database. First, I’ve already created a module. To do that, from the project menu, select Add Module. Here is the code.
In other tutorials, they use DataGridView because that’s the easy way how to load data from database. First, I’ve already created a module. To do that, from the project menu, select Add Module. Here is the code.
Imports MySql.Data.MySqlClient
Module MyModule
Public ConnString As String = "server=localhost;user id=root;password=;database=dbinformation"
Public MyConnection As New MySqlConnection(ConnString)
End Module
The above code is MySQL connection. Actually, you can create a connection without the help of module. The technique here is, create a method name for connection and simply call the connection if needed. Once the method name is created, simply put myconnection() in every form load to enable to connect from your database.
Note: there are different styles of coding to connect from database, it’s up to the programmer.
Dim sqlserver As String = "Server=127.0.0.1;User ID=root;Password=;Database=DbTest" 'or you may used localhost instead 127.0.0.1
Dim sqlcon As New MySqlConnection
sqlcon.ConnectionString = sqlserver
Try
If sqlcon.State = ConnectionState.Closed Then
sqlcon.Open()
MessageBox.Show("Open")
Else
sqlcon.Close()
MessageBox.Show("closed")
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
Under construction yet! continue
Form 1
Imports MySql.Data.MySqlClient
Public Class Form1
Public ds As New DataSet
Public strSQL As String
Public cmd As New MySqlCommand
Public dr As MySqlDataReader
Public table As New DataTable
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'list()
mylistview()
End Sub
Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
strSQL = "Insert into tblinformation(Firstname,Lastname,Middlename,Gender) values ('" & txtfname.Text &
"', '" & txtlname.Text & "', '" & txtmname.Text & "', '" & cbogender.Text & "')"
Dim da As New MySqlDataAdapter(strSQL, MyConnection)
da.Fill(ds)
txtfname.Clear()
txtlname.Clear()
txtmname.Clear()
cbogender.Text = ""
txtfname.Focus()
list()
End Sub
Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
If lvinfo.Items.Count > 0 Then
For i = lvinfo.Items.Count - 1 To 0 Step -1
If lvinfo.Items(i).Checked = True Then
strSQL = "DELETE FROM tblinformation WHERE ID = '" & lvinfo.Items(i).Text & "'"
Dim da As New MySqlDataAdapter(strSQL, MyConnection)
da.Fill(ds)
End If
Next i
End If
list()
End Sub
Private Sub TextBox3_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtsearch.TextChanged
strSQL = "SELECT * FROM tblinformation WHERE Firstname LIKE '%" & txtsearch.Text & "%' OR Lastname LIKE '%" & txtsearch.Text & "%'"
MyConnection.Open()
cmd = New MySqlCommand(strSQL, MyConnection)
dr = cmd.ExecuteReader()
lvinfo.Items.Clear()
Do While dr.Read()
Dim a = (dr.Item("ID").ToString())
Dim b = (dr.Item("Firstname").ToString())
Dim c = (dr.Item("Lastname").ToString())
Dim d = (dr.Item("Middlename").ToString())
Dim f = (dr.Item("Gender").ToString())
Dim lv As ListViewItem = lvinfo.Items.Add(a)
lv.SubItems.Add(b)
lv.SubItems.Add(c)
lv.SubItems.Add(d)
Loop
dr.Close()
MyConnection.Close()
MyConnection.Dispose()
End Sub
Private Sub ListView1_DoubleClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lvinfo.DoubleClick
If lvinfo.Items.Count > 0 Then
Form2.lblid.Text = lvinfo.SelectedItems(0).Text
Form2.txtfname.Text = lvinfo.SelectedItems(0).SubItems(1).Text
Form2.txtlname.Text = lvinfo.SelectedItems(0).SubItems(2).Text
Form2.txtmname.Text = lvinfo.SelectedItems(0).SubItems(3).Text
Form2.cbogender.Text = lvinfo.SelectedItems(0).SubItems(4).Text
End If
Form2.Show()
End Sub
Public Sub list()
strSQL = "SELECT * FROM tblinformation"
MyConnection.Open()
cmd = New MySqlCommand(strSQL, MyConnection)
dr = cmd.ExecuteReader()
lvinfo.Items.Clear()
Do While dr.Read()
Dim a = (dr.Item("ID").ToString())
Dim b = (dr.Item("Firstname").ToString())
Dim c = (dr.Item("Lastname").ToString())
Dim d = (dr.Item("Middlename").ToString())
Dim f = (dr.Item("Gender").ToString())
Dim lv As ListViewItem = lvinfo.Items.Add(a)
lv.SubItems.Add(b)
lv.SubItems.Add(c)
lv.SubItems.Add(d)
lv.SubItems.Add(f)
Loop
dr.Close()
cmd.Dispose()
MyConnection.Close()
End Sub
Private Sub btncancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncancel.Click
txtfname.Clear()
txtlname.Clear()
txtmname.Clear()
cbogender.Text = ""
txtfname.Focus()
End Sub
#Region "My Other Option to call listview"
Public Sub mylistview()
Dim sqladpater As New MySqlDataAdapter
Dim i As Integer
strSQL = "SELECT * FROM tblinformation"
With cmd
.CommandText = strSQL
.Connection = MyConnection
End With
With sqladpater
.SelectCommand = cmd
.Fill(table)
End With
lvinfo.Items.Clear()
For i = 0 To table.Rows.Count - 1
With lvinfo
.Items.Add(table.Rows(i)("ID"))
With .Items(.Items.Count - 1).SubItems
.Add(table.Rows(i)("Firstname"))
.Add(table.Rows(i)("Lastname"))
.Add(table.Rows(i)("Middlename"))
.Add(table.Rows(i)("Gender"))
End With
End With
Next
End Sub
#End Region
#Region "Listview properties"
Public Sub ListViewProperties()
' Set ListView Properties
lvinfo.View = View.Details
lvinfo.GridLines = True
lvinfo.FullRowSelect = True
lvinfo.HideSelection = False
lvinfo.MultiSelect = False
End Sub
#End Region
End Class
Module created
Imports MySql.Data.MySqlClient
Module MyModule
Public ConnString As String = "server=localhost;user id=root;password=;database=dbinformation"
Public MyConnection As New MySqlConnection(ConnString)
End Module
Form2
Imports MySql.Data.MySqlClient
Public Class Form2
Dim strsql As String
Dim ds As New DataSet
Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
strsql = "Update tblinformation set Firstname = '" & txtfname.Text & "', Lastname = '" &
txtlname.Text & "', Middlename = '" &
txtmname.Text & "', Gender = '" & cbogender.Text & "' where ID = '" & lblid.Text & "'"
Dim da As New MySqlDataAdapter(strsql, MyConnection)
da.Fill(ds)
Me.Hide()
Form1.list()
End Sub
Private Sub btncancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncancel.Click
txtfname.Clear()
txtlname.Clear()
txtmname.Clear()
cbogender.Text = ""
txtfname.Focus()
Me.Hide()
End Sub
Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
lblid.Visible = False
End Sub
Private Sub cbogender_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbogender.SelectedIndexChanged
End Sub
End Class
Completely run! Add new record(s), Delete Record(s), Update Record(s), and Search Record(s)
Completely run! Add new record(s), Delete Record(s), Update Record(s), and Search Record(s)
Learned a lot of
ReplyDeletenew things from your post!Good creation ,thanks for good info .
Net Online Training Hyderabad