How to Organize the CRUD in VB.Net and MySQL Database
This tutorial is all about How to Organize the CRUD in VB.Net and MySQL Database.
Today, I’m going to teach you how to organize MySQL Connection and CRUD by using a Module in VB.Net. The Module is one of the ways on how to organize your system, reduce the code and it will lessen your work.
What you will learn in this tutorial is that you will able to create a basic application like a simple login form.
What is Visual Basic’s purpose?
The third-generation programming language was created to aid developers in the creation of Windows applications. It has a programming environment that allows programmers to write code in.exe or executable files. They can also utilize it to create in-house front-end solutions for interacting with huge databases. Because the language allows for continuing changes, you can keep coding and revising your work as needed.
However, there are some limits to the Microsoft Visual Basic download. If you want to make applications that take a long time to process, this software isn’t for you. That implies you won’t be able to use VB to create games or large apps because the system’s graphic interface requires a lot of memory and space. Furthermore, the language is limited to Microsoft and does not support other operating systems.
What are the most important characteristics of Visual Basic?
Microsoft Visual Basic for Applications Download, unlike other programming languages, allows for speedier app creation. It has string processing capabilities and is compatible with C++, MFC, and F#. Multi-targeting and the Windows Presentation Framework are also supported by the system, allowing developers to create a variety of Windows apps, desktop tools, metro-style programs, and hardware drivers.
If you want crud in vb using ms access, you can see it here in our previous visual basic tutorials.
Lets’ begin:
1. First, create a Database and name it “dbuser” in MySQL Database.
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, `type` varchar(30) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB;
2. Open the Visual Basic and create a new Windows Form Application.
3. After that, in the solution explorer, right-click and create “New Folder” , name it “Modules”.
This is the way to separate the Module and the Windows Form.
4. In the folder, create a Module and name it “CRUD”.
5. After creating a Module, You have to set the connection string of MySQL Database. Then, Declare all the classes ad variables that are needed. You read it here how Create a MySQL Connection using VB.net.
Imports 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
6. After setting up the connection and the declaration. Create a VB.NET CRUD event.
'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 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
'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 UPPDATE.", 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
'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 Sub7. Go back to the Form Design and create a User’s Registration Form. It will look like this.
8. After setting up the Form, double click the Form and copy this code and put this above the Form1_Load event handler of the Form.
'THIS PROCEEDURE IS FOR THE CREATE BUTTON
Private Sub CREATE_Click(ByVal sender As System.Object, 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 (`name`, `username`, `Pass`,`type`) VALUES ('" & TXTNAME.Text & "','" _
& TXTUSERNAME.Text & "','" & TXTPASSWORD.Text & "','" & CBOTYPE.Text & "')")
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
'THIS PROCEEDURE IS FOR THE RELOAD BUTTON
Private Sub RELOAD_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
updatess("UPDATE users SET `name` = '" & TXTNAME.Text & "', `username` = '" & TXTUSERNAME.Text _
& "', `Pass` = '" & TXTPASSWORD.Text & "',`type` = '" & CBOTYPE.Text & "' where id = '" & lblid.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
lblid.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
CBOTYPE.Text = DTGLIST.CurrentRow.Cells(4).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 = '" & lblid.Text & "'")
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
9. Press “F5” to run your project.
You can download HERE the complete CRUD VB NET MySQL Source Code and run it on your computer.
Freebies!!!!!!!
How about il teach you about How to Master VB.net and MySQL Database in 30 minutes? You can download the sample vb.net projects with source code together with the Database.
Aside from this, I have included the video tutorials in these articles.
Readers might read also:
CRUD stands for Create, Read, Update, and Delete function using visual basic or visual basic .net.