Organizing MySQL Connection and CRUD in Visual Basic 2008 and MySQL Database

Submitted by janobe on Sunday, December 15, 2013 - 16:03.
In this tutorial I will show you how to organize MySQL Connection and CRUD by using a module. Module is one of the ways on how to organize your system, refining the code and it lessens your work. You can also access your code in different places. Lets’ begin: 1. First create a MySQL Database named “userdb”.
  1. CREATE TABLE IF NOT EXISTS `users` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `name` varchar(30) NOT NULL,
  4.   `username` varchar(30) NOT NULL,
  5.   `Pass` varchar(60) NOT NULL,
  6.   `type` varchar(30) NOT NULL,
  7.   PRIMARY KEY (`id`)
2. Open your Visual Basic 2008 and create a project. After that, in the solution explorer, right click and create “New Folder” , named “Modules”. I made this, to separate the Module and the Windows Form. Solution Explorer 3. Under the folder, create a Module and name it “concrud”. Solution Explorer2 4. After that, create a Connection of MySQL Database to Visual Basic 2008.
  1. Imports MySql.Data.MySqlClient
  2. Module concrud
  3.     'setting up your connection
  4.     Public Function mysqlconnection() As MySqlConnection
  5.         Return New MySqlConnection("server=localhost;user id=root;database=userdb")
  6.     End Function
  7.     Public con As MySqlConnection = mysqlconnection()
  8.  
  9. End Modude
5. After creating a Connection. Do all the Declarations in Public.
  1.     ''declaring a string
  2.     Public result As String
  3.  
  4.     ''declaring the class
  5.     Public cmd As New MySqlCommand
  6.     Public da As New MySqlDataAdapter
  7.     Public dt As New DataTable
6. Create the CRUD event.
  1.  ''for inserting the data to the database
  2.     Public Sub create(ByVal sql As String)
  3.  
  4.         Try
  5.             'open the connection
  6.             con.Open()
  7.             'holds the data
  8.             With cmd
  9.                 .Connection = con
  10.                 .CommandText = sql
  11.  
  12.                 'execute the data
  13.                 result = cmd.ExecuteNonQuery
  14.  
  15.                 If result = 0 Then
  16.                     MsgBox("no data save", MsgBoxStyle.Information)
  17.                 Else
  18.                     MsgBox("data save to the database")
  19.                 End If
  20.             End With
  21.  
  22.         Catch ex As Exception
  23.             MsgBox(ex.Message)
  24.         End Try
  25.         con.Close()
  26.     End Sub
  27.  
  28.     'for holding the data to retrieve.
  29.     Public Sub reload(ByVal sql As String)
  30.         Try
  31.             dt = New DataTable
  32.             con.Open()
  33.             With cmd
  34.                 .Connection = con
  35.                 .CommandText = sql
  36.             End With
  37.  
  38.         Catch ex As Exception
  39.             MsgBox(ex.Message)
  40.         End Try
  41.         con.Close()
  42.         da.Dispose()
  43.     End Sub
  44.  
  45.     'filling the table and retrieving the data to the datagridview from the database
  46.     Public Sub filltable(ByVal dtg As DataGridView)
  47.         Try
  48.             con.Open()
  49.             ''''''''
  50.             '''''''''''''''fill table
  51.             da.SelectCommand = cmd
  52.             da.Fill(dt)
  53.             dtg.DataSource = dt
  54.         Catch ex As Exception
  55.             MsgBox(ex.Message)
  56.         End Try
  57.         con.Close()
  58.         da.Dispose()
  59.     End Sub
  60.  
  61.     'updating the data from the database.
  62.     Public Sub updates(ByVal sql As String)
  63.         Try
  64.             con.Open()
  65.             With cmd
  66.                 .Connection = con
  67.                 .CommandText = sql
  68.                 result = cmd.ExecuteNonQuery
  69.                 If result = 0 Then
  70.                     MsgBox("no updated data", MsgBoxStyle.Information)
  71.                 Else
  72.                     MsgBox("data in the database has been updated")
  73.                 End If
  74.             End With
  75.  
  76.         Catch ex As Exception
  77.             MsgBox(ex.Message)
  78.         End Try
  79.         con.Close()
  80.     End Sub
  81.  
  82.     'deleting the data from the database
  83.     Public Sub delete(ByVal sql As String)
  84.         Try
  85.             con.Open()
  86.             With cmd
  87.                 .Connection = con
  88.                 .CommandText = sql
  89.  
  90.                 result = cmd.ExecuteNonQuery
  91.                 If result = 0 Then
  92.                     MsgBox("Error query for deleting a data.", MsgBoxStyle.Critical)
  93.                 Else
  94.                     MsgBox("data in the database has been deleted")
  95.                 End If
  96.  
  97.             End With
  98.  
  99.         Catch ex As Exception
  100.             MsgBox(ex.Message)
  101.         End Try
  102.         con.Close()
  103.     End Sub
7. Go back to Form and create a User Entry just like this. User Entry Form 8. Double click the Form and do this code.
  1. Imports MySql.Data.MySqlClient
  2. Public Class Form1
  3.  
  4.     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
  5.         ''"try" is for catching errors
  6.         'inserting of data.
  7.         Try
  8.             create("INSERT INTO users (`name`, `username`, `Pass`,`type`) VALUES ('" & TextBox1.Text & "','" _
  9.                    & TextBox2.Text & "','" & TextBox3.Text & "','" & ComboBox1.Text & "')")
  10.  
  11.  
  12.         Catch ex As Exception
  13.             MsgBox(ex.Message)
  14.         End Try
  15.     End Sub
  16.  
  17.     Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
  18.         'for retreiving the from the database
  19.         Try
  20.             reload("SELECT * FROM users")
  21.             filltable(DataGridView1)
  22.         Catch ex As Exception
  23.             MsgBox(ex.Message)
  24.         End Try
  25.     End Sub
  26.  
  27.     Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
  28.         'for updating data from the database..
  29.         Try
  30.             updates("UPDATE users SET `name` = '" & TextBox1.Text & "', `username` = '" & TextBox2.Text _
  31.                     & "', `Pass` = '" & TextBox3.Text & "',`type` = '" & ComboBox1.Text & "' where id = '" & lblid.Text & "'")
  32.         Catch ex As Exception
  33.             MsgBox(ex.Message)
  34.         End Try
  35.     End Sub
  36.     Private Sub DataGridView1_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.DoubleClick
  37.         'assigning data from the datagridview to the Label ,TextBox and ComboBox
  38.         lblid.Text = DataGridView1.CurrentRow.Cells(0).Value
  39.         TextBox1.Text = DataGridView1.CurrentRow.Cells(1).Value
  40.         TextBox2.Text = DataGridView1.CurrentRow.Cells(2).Value
  41.         TextBox3.Text = DataGridView1.CurrentRow.Cells(3).Value
  42.         ComboBox1.Text = DataGridView1.CurrentRow.Cells(4).Value
  43.     End Sub
  44.  
  45.     Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
  46.         'for deleting the data from the database
  47.         Try
  48.             delete("DELETE FROM users WHERE id = '" & lblid.Text & "'")
  49.         Catch ex As Exception
  50.             MsgBox(ex.Message)
  51.         End Try
  52.     End Sub
  53.  
  54. End Class
9. Run your project. You can download the complete Source Code and run it on your computer.
Tags
Organizing MySQL Connection and CRUD in Visual Basic 2008 MySQL Database

Add new comment

About text formats
  • 279 views