VB.NET For beginner : how toCRUD operations (insert,read,update,delete) in vb.net with mysql database and Metro theme UI windows Form? download source code simple CRUD applications in sector code.
VB.NET for beginners - How to create simple CRUD application (Create,Read,Update,Delete) in vb.net and using MySQL database ODBC connection with Metro Framework UI theme design? this tutorial has been create before, Please read CRUD Tutorial with MySQL Database, but today i'll make a new version and change the default theme to a simple interface with metro framework theme.
Read : How to Change default windows form with Metro Framework Theme
How to Create MySQL Database
How to Make A connection Using ODBC Driver
we will create a simple CRUD operations step by step, so you can easy to follow how to make this simple.
Just leave the Form1.vb for a moment, we will create new module for our connections.
With form1.vb, first please import name space of ODBC driver.
Please ask any questions in the comments box below, I would be very glad if can help you and don't forget to follow us in Social media. thanks before.
Read : How to Change default windows form with Metro Framework Theme
How to Create MySQL Database
How to Make A connection Using ODBC Driver
we will create a simple CRUD operations step by step, so you can easy to follow how to make this simple.
Create a New Project
Just create a new project with a name "CRUDmetro", and lets design Form1.vb look like this
Just leave the Form1.vb for a moment, we will create new module for our connections.
Source Code Module Connections
So lets create new module and rename it with "ModuleConnections.vb" and write all source code above.Imports System.Data.Odbc ' import namespace of ODBC
Module ModuleConnections
Public connections As OdbcConnection ' declarations for our connections
Sub OpenConnection()
Try
' you must hasbeen install MySQL ODBC driver on your computer
' then create connection to our database (MySQL)
' just see on the descriptions how to make a connections using ODBC Driver
connections = New OdbcConnection("DSN=delta_db;MultipleActiveResultSets=True")
If connections.State = ConnectionState.Closed Then
connections.Open() ' open the connections
End If
Catch ex As Exception
MsgBox("Connection Failed !") ' if connection is failed
End Try
End Sub
End Module
If you has been done with our module for connections to a database, back to Form1.vb and lets complete all the code.With form1.vb, first please import name space of ODBC driver.
Imports System.Data.Odbc ' import namespace of ODBCand declarations our variable for a new data or old data, so this variable will be show when we will create a new data and when we update the old data. i hope you understand, just write the variable above the class form1.vb
Dim NewData As Boolean ' declarations New data for save new data, or update the data
Source Code Sub Load Data
load data will biand all data from database to a GridViews, just write this code Sub LoadData()
' its for load all data into gridviews
OpenConnection() ' open our connection
Dim da As New OdbcDataAdapter("SELECT * FROM karyawan", connections)
Dim ds As New DataSet
ds.Clear()
da.Fill(ds, "karyawan")
MetroGrid1.DataSource = ds.Tables("karyawan") ' set datasource of our gridview
da.Dispose()
connections.Close()
End Sub
Source Code Sub SenToTextBox
Its will change the textbox value with any value from datagridview Sub senToTextBox(ByVal x As Integer)
'its for get value from datagridview into textbox
Try
MetroTextBox1.Text = MetroGrid1.Rows(x).Cells(0).Value
MetroTextBox2.Text = MetroGrid1.Rows(x).Cells(1).Value
MetroTextBox3.Text = MetroGrid1.Rows(x).Cells(2).Value
MetroTextBox4.Text = MetroGrid1.Rows(x).Cells(3).Value
MetroTextBox5.Text = MetroGrid1.Rows(x).Cells(4).Value
MetroTextBox6.Text = MetroGrid1.Rows(x).Cells(5).Value
Catch ex As Exception
End Try
End Sub
Source Code Sub Clear The TextBox
Sub ClearTextBox()
MetroTextBox1.Text = "" ' its for clear the textbox
MetroTextBox2.Text = ""
MetroTextBox3.Text = ""
MetroTextBox4.Text = ""
MetroTextBox5.Text = ""
MetroTextBox6.Text = ""
MetroTextBox1.Focus()
End Sub
Source Code Setting Width of The DataGridView Columns
Sub SettingGrid()
MetroGrid1.Columns(0).Width = 40 ' setting width of our column
' you can setting more of your datagrid columns
End Sub
Source Code Functions To Save or Update Or Delete data
Private Sub RunSQL(ByVal sql As String) ' create functions for save or update data
Dim cmd As New OdbcCommand
OpenConnection() ' open our connection
Try
cmd.Connection = connections
cmd.CommandType = CommandType.Text
cmd.CommandText = sql
cmd.ExecuteNonQuery()
cmd.Dispose()
connections.Close()
MsgBox("Data Hasbeen saved !", vbInformation)
Catch ex As Exception
MsgBox("Error when saving data !" & ex.Message)
End Try
End Sub
Source Code Form Load
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
LoadData()
SettingGrid()
NewData = True ' its mean we will create a new data with query "INSERT"
' if new data = false, its mean we make a query "UPDATE"
End Sub
Source Code New Button (Button1)
Private Sub MetroButton1_Click(sender As Object, e As EventArgs) Handles MetroButton1.Click
NewData = True
ClearTextBox()
End Sub
Source Code Save Button (Button2)
Private Sub MetroButton2_Click(sender As Object, e As EventArgs) Handles MetroButton2.Click
Dim savaedata As String
Dim massege As String
If NewData Then
massege = MsgBox("Are you sure to add new data to database?", vbYesNo + vbInformation, "Information")
If massege = vbNo Then
Exit Sub
End If
savaedata = "INSERT INTO karyawan(id_karyawan,nama_karyawan,nik,jabatan,no_telphone,alamat) " _
+ "Values('" & MetroTextBox1.Text & "','" & MetroTextBox2.Text & "','" & MetroTextBox3.Text & "','" & MetroTextBox4.Text & "','" & MetroTextBox5.Text & "','" & MetroTextBox6.Text & "')"
Else
massege = MsgBox("Are you sure to UPDATE data to database?", vbYesNo + vbInformation, "Information")
If massege = vbNo Then
Exit Sub
End If
savaedata = "UPDATE karyawan SET nama_karyawan='" & MetroTextBox2.Text & "' " _
+ ",nik='" & MetroTextBox3.Text & "' " _
+ ",jabatan='" & MetroTextBox4.Text & "' " _
+ ",no_telphone='" & MetroTextBox5.Text & "' " _
+ ",alamat='" & MetroTextBox6.Text & "' where id_karyawan='" & MetroTextBox1.Text & "'"
End If
RunSQL(savaedata)
MetroGrid1.Refresh()
LoadData() ' insert data again after you update the data
End Sub
Source Code Delete Button (Button3)
Private Sub MetroButton3_Click(sender As Object, e As EventArgs) Handles MetroButton3.Click
Dim delete As String
Dim message As String
message = MsgBox("Are you sure want to delete this data? ", vbExclamation + vbYesNo, "warning")
If message = vbNo Then Exit Sub
delete = "DELETE from karyawan where id_karyawan='" & MetroTextBox1.Text & "'"
RunSQL(delete) ' delet functions
MetroGrid1.Refresh()
LoadData() ' insert data again after you update the data
End Sub
Source Code Exit Button (Button4)
Private Sub MetroButton4_Click(sender As Object, e As EventArgs) Handles MetroButton4.Click
Me.Close()
End Sub
Source Code DataGridView Cell Click Event
Private Sub MetroGrid1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles MetroGrid1.CellClick
senToTextBox(e.RowIndex) ' send value to textbox
NewData = False
End Sub
So we have done with simple project CRUD Operations with MySQL database and Metro Framework UI Themes, But wait.. i'll show you all complete the Source Code Form1.vb, just view and write all complete code bellow.
Imports System.Data.Odbc
Public Class Form1
Dim NewData As Boolean ' declarations New data for save new data, or update the data
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
LoadData()
SettingGrid()
NewData = True ' its mean we will create a new data with query "INSERT"
' if new data = false, its mean we make a query "UPDATE"
End Sub
Sub LoadData()
' its for load all data into gridviews
OpenConnection() ' open our connection
Dim da As New OdbcDataAdapter("SELECT * FROM karyawan", connections)
Dim ds As New DataSet
ds.Clear()
da.Fill(ds, "karyawan")
MetroGrid1.DataSource = ds.Tables("karyawan") ' set datasource of our gridview
da.Dispose()
connections.Close()
End Sub
Sub senToTextBox(ByVal x As Integer)
'its for get value from datagridview into textbox
Try
MetroTextBox1.Text = MetroGrid1.Rows(x).Cells(0).Value
MetroTextBox2.Text = MetroGrid1.Rows(x).Cells(1).Value
MetroTextBox3.Text = MetroGrid1.Rows(x).Cells(2).Value
MetroTextBox4.Text = MetroGrid1.Rows(x).Cells(3).Value
MetroTextBox5.Text = MetroGrid1.Rows(x).Cells(4).Value
MetroTextBox6.Text = MetroGrid1.Rows(x).Cells(5).Value
Catch ex As Exception
End Try
End Sub
Private Sub MetroGrid1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles MetroGrid1.CellClick
senToTextBox(e.RowIndex) ' send value to textbox
NewData = False
End Sub
Sub SettingGrid()
MetroGrid1.Columns(0).Width = 40 ' setting width of our column
End Sub
Sub ClearTextBox()
MetroTextBox1.Text = "" ' its for clear the textbox
MetroTextBox2.Text = ""
MetroTextBox3.Text = ""
MetroTextBox4.Text = ""
MetroTextBox5.Text = ""
MetroTextBox6.Text = ""
MetroTextBox1.Focus()
End Sub
Private Sub MetroButton1_Click(sender As Object, e As EventArgs) Handles MetroButton1.Click
NewData = True
ClearTextBox()
End Sub
Private Sub RunSQL(ByVal sql As String) ' create functions for save or update data
Dim cmd As New OdbcCommand
OpenConnection() ' open our connection
Try
cmd.Connection = connections
cmd.CommandType = CommandType.Text
cmd.CommandText = sql
cmd.ExecuteNonQuery()
cmd.Dispose()
connections.Close()
MsgBox("Data Hasbeen saved !", vbInformation)
Catch ex As Exception
MsgBox("Error when saving data !" & ex.Message)
End Try
End Sub
Private Sub MetroButton2_Click(sender As Object, e As EventArgs) Handles MetroButton2.Click
Dim savaedata As String
Dim massege As String
If NewData Then
massege = MsgBox("Are you sure to add new data to database?", vbYesNo + vbInformation, "Information")
If massege = vbNo Then
Exit Sub
End If
savaedata = "INSERT INTO karyawan(id_karyawan,nama_karyawan,nik,jabatan,no_telphone,alamat) " _
+ "Values('" & MetroTextBox1.Text & "','" & MetroTextBox2.Text & "','" & MetroTextBox3.Text & "','" & MetroTextBox4.Text & "','" & MetroTextBox5.Text & "','" & MetroTextBox6.Text & "')"
Else
massege = MsgBox("Are you sure to UPDATE data to database?", vbYesNo + vbInformation, "Information")
If massege = vbNo Then
Exit Sub
End If
savaedata = "UPDATE karyawan SET nama_karyawan='" & MetroTextBox2.Text & "' " _
+ ",nik='" & MetroTextBox3.Text & "' " _
+ ",jabatan='" & MetroTextBox4.Text & "' " _
+ ",no_telphone='" & MetroTextBox5.Text & "' " _
+ ",alamat='" & MetroTextBox6.Text & "' where id_karyawan='" & MetroTextBox1.Text & "'"
End If
RunSQL(savaedata)
MetroGrid1.Refresh()
LoadData() ' insert data again after you update the data
End Sub
Private Sub MetroButton3_Click(sender As Object, e As EventArgs) Handles MetroButton3.Click
Dim delete As String
Dim message As String
message = MsgBox("Are you sure want to delete this data? ", vbExclamation + vbYesNo, "warning")
If message = vbNo Then Exit Sub
delete = "DELETE from karyawan where id_karyawan='" & MetroTextBox1.Text & "'"
RunSQL(delete) ' delet functions
MetroGrid1.Refresh()
LoadData() ' insert data again after you update the data
End Sub
Private Sub MetroButton4_Click(sender As Object, e As EventArgs) Handles MetroButton4.Click
Me.Close()
End Sub
End Class
If you are still confused by the tutorial above, just follow step by step this video, i have create a video CRUD tutorial just for you, hope you enjoyed.Video Tutorial CRUD Operations with MySQL Database (English Version)
Please ask any questions in the comments box below, I would be very glad if can help you and don't forget to follow us in Social media. thanks before.
COMMENTS