How to perform Create, Read, Update and Delete (CRUD) operation in Visual basic using MYSQL database? In this Visual Basic tutorial let’s create a simple VB.net application to perform crud operation with MYSQL.
What is CRUD?
CRUD Meaning: CRUD is a combination of words that are created, READ, UPDATE, and DELETE that is actually a basic operation with a database.
Data Insertion, reading, updating, and deleting are the basic operation to perform communication between an application and DataBase.
CRUD operations in VB(Visual Basic) with MySQL
Let’s create a Visual Basic window Application and Database then we will see how to connect them together so we can perform the CRUD operations on it.
Create VB.NET Window form
Create a windows application and add controls to the form and create a database and tables.
- First, create a Database and name it “userdb” in MySQL Database write a table creation query as given below in the SQL window to create a new “users” table.
CREATE TABLE IF NOT EXISTS `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) NOT NULL, `username` varchar(30) NOT NULL, `Pass` varchar(60) NOT NULL, PRIMARY KEY (`id`) )
- Open the Visual Studio and in visual basic create a new Windows Form Application.
- In the solution explorer, right-click and create “New Folder”, and name it “Modules”. This is the way to separate the Module from Windows Form.
- In a folder, create a Module and name it “CRUD”.
The module is created, You have to set the connection string of the Database. Then Declare all the classes and variables that are needed.
Database Connection with vb.Net
MySql.Data.MySqlClient
Module strconcrud
'SETTING UP THE CONNECTION
Public Function strstrconnection() As MySqlConnection
Return New MySqlConnection("server=localhost;user id=root;database=userdb")
End Function
Public strcon As MySqlConnection = strstrconnection()
'DECLARING CLASSES AND VARIABLE
Public result As String
Public cmd As New MySqlCommand
Public da As New MySqlDataAdapter
Public dt As New DataTable
End Module
- No connection has been set. Next, Create a VB.NET CRUD event.
Insert data into database in VB
'THIS METHOD IS FOR INSERTING DATA IN THE DATABASE
Public Sub create(ByVal sql As String)
Try
strcon.Open()
'HOLDS THE DATA TO BE EXECUTED
With cmd
.Connection = strcon
.CommandText = sql
'EXECUTE THE DATA
result = cmd.ExecuteNonQuery
'CHECKING IF THE DATA HAS EXECUTED OR NOT AND THEN THE POP UP MESSAGE WILL APPEAR
If result = 0 Then
MsgBox("FAILED TO SAVE THE DATA", MsgBoxStyle.Information)
Else
MsgBox("DATA HAS BEEN SAVE IN THE DATABASE")
End If
End With
Catch ex As Exception
MsgBox(ex.Message)
End Try
strcon.Close()
End Sub
This query will check if there is any data filled in the TextBoxes or not if true then it will show the message “Data Has Been Saved In The Database” else it will show “Failed To Save The Data“.
Read Data from MYSQL in VB
'THIS METHOD IS FOR RETRIEVING DATA IN THE DATABASE Public Sub reload(ByVal sql As String, ByVal DTG As Object) Try dt = New DataTable strcon.Open() With cmd .Connection = strcon .CommandText = sql End With da.SelectCommand = cmd da.Fill(dt) dtg.DataSource = dt Catch ex As Exception MsgBox(ex.Message) End Try strcon.Close() da.Dispose() End Sub
By this query, it will show all the data from the database and display it in grid view.
Update data from MySQL database in VB
'THIS METHOD IS FOR UPDATING THE DATA IN THE DATABASE.
Public Sub updates(ByVal sql As String)
Try
strcon.Open()
With cmd
.Connection = strcon
.CommandText = sql
result = cmd.ExecuteNonQuery
If result = 0 Then
MsgBox("DATA IS FAILED TO UPDATE.", MsgBoxStyle.Information)
Else
MsgBox("THE DATA HAS BEEN UPDATED IN THE DATABASE.")
End If
End With
Catch ex As Exception
MsgBox(ex.Message)
End Try
strcon.Close()
End Sub
The above query work by clicking on the update button we can update any existing record from the database if the record is found then rewriting them and clicking on this button will show the message box “THE DATA HAS BEEN UPDATED IN THE DATABASE” else it will show “DATA IS FAILED TO UPDATE“.
Delete data from MySQL database in VB
'THIS METHOD IS FOR DELETING THE DATA IN THE DATABASE
Public Sub delete(ByVal sql As String)
Try
strcon.Open()
With cmd
.Connection = strcon
.CommandText = sql
result = cmd.ExecuteNonQuery
If result = 0 Then
MsgBox("FAILED TO DELETE THE DATA IN THE DATABASE.", MsgBoxStyle.Critical)
Else
MsgBox("DATA HAS BEEN DELETED IN THE DATABASE.")
End If
End With
Catch ex As Exception
MsgBox(ex.Message)
End Try
strcon.Close()
End Sub
Delete query work when we enter any record that is found in the database and by clicking on the delete button will delete the record of that data from the database and show the message box “DATA HAS BEEN DELETED IN THE DATABASE” else it will show “FAILED TO DELETE THE DATA IN THE DATABASE“.
- Go back to the Form Design and create a Users Registration Form. It will look like this.
- After setting up the Form, double click the Form and copy this code, and put this before the Form1_Load event handler of the Form.
'THIS PROCEEDURE IS FOR THE CREATE BUTTON
Private e SubCREATE_Click(ByVal sender As System.Objec, ByVal e As System.EventArgs) Handles BTNCREATE.Click
Try
'CALL THE METHOD THAT YOU HAVE CREATED
'AND PUT YOUR QUERY IN THE PARAMETER FOR INSERTING THE DATA IN THE DATABASE
create("INSERT INTO users (`id`,`name`, `username`, `Pass` VALUES ('" & TXTID.Text & "','" & TXTNAME.Text & "','" & TXUSERNAME.Text & "','" & TXTPASSWORD.Text & "'))")
Catch ex As Exception MsgBox(ex.Message)
End Try
End Sub
'THIS PROCEEDURE IS FOR THE RETREIVE BUTTON
Private Sub RETREIVE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNRELOAD.Click
Try 'CALL THE METHOD THAT YOU HAVE CREATED.
'PUT YOUR QUERY AND THE NAME OF THE DATAGRIDVIEW IN THE PARAMETERS.
'THIS IS METHOD IS FOR RETREIVING THE DATA IN THE DATABASE TO THE DATAGRIDVIEW reload("SELECT * FROM users", DTGLIST)
Catch ex As Exception MsgBox(ex.Message)
End Try
End Sub
'THIS PROCEEDURE IS FOR THE UPDATE BUTTON Private Sub UPDATE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNUPDATE.Click
Try
'CALL THE METHOD THAT YOU HAVE CREATED
'AND PUT YOUR QUERY IN THE PARAMETER FOR UPDATING THE DATA IN THE DATABASE
updates("UPDATE users SET `name` = '" & TXTNAME.Text & "', `username` = '" & TXTUSERNAME.Text _ & "', `Pass` = '" & TXTPASSWORD.Text & "',"' where id = '" & TXTID.Text & "'") Catch ex As Exception MsgBox(ex.Message)
End Try
End Sub
'THIS PROCEEDURE IS FOR THE DOUBLE CLICK EVENT OF THE DATAGRID VIEW Private Sub DataGridView1_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles DTGLIST.DoubleClick
'SET THE RECORDS ON THE LIST OF THE DATAGRIDVIEW TO ITS SPECIFIC FEILDS TXTID.Text=DTGLIST.CurrentRow.Cells(0).Value TXTNAME.Text=DTGLIST.CurrentRow.Cells(1).Value TXTUSERNAME.Text=DTGLIST.CurrentRow.Cells(2).Value TXTPASSWORD.Text=DTGLIST.CurrentRow.Cells(3).Value
End Sub
'THIS PROCEEDURE IS FOR THE DELETE BUTTON
Private Sub DELETE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNDELETE.Click
Try
'CALL THE METHOD THAT YOU HAVE CREATED
'AND PUT YOUR QUERY IN THE PARAMETER FOR DELETING THE DATA IN THE DATABASE
delete("DELETE FROM users WHERE id = '" & TXTID.Text & "'") Catch ex As Exception MsgBox(ex.Message)
End Try
End Sub
- Press “F5” to run your project.
When we click on Create button this will add the data we have entered in textboxes on the connected database table.
Clicking on the Retrieve button will show the table data in the below box.
If we enter any Id no. which is already saved in the table then retyping of new data and clicking on the update button will update the data row in a table.
Typing of any Id no. and clicking on the delete button will delete the specific row of that Id from the table.
So in this way, we can easily do the CRUD operations in the VB.NET application.
