Tutorial CRUD Program With MySQL Database In Visual Studio

---

Preview

Hello guys, this post related to the previous post is about creating a CRUD program (Create Read Update Delete) in Microsoft Visual Studio 2010 using MySQL database. This program contains 5 User Data Fields, User Name, User Address, User Phone and User Photos.

CRUD is a program that is generally always present in large or small applications. Because this program is responsible for managing user data that is entered so it is very important to learn if it is just starting to create a basic program.

You can create and connect MySQL database with projects that you will create later. If you do not know how to create a database connection, please watch the previous post

How To Connect Database MySQL With Visual Studio 2010

In this post I use the database used in the previous post by adding 1 Table is the users table.

Now open Visual Studio and create a new project then create a module like in the previous tutorial. Then you start designing the form, which is more or less like in the picture below

NOTES :

The components used are:

• Label
• Textbox
• Button
• PictureBox
• DataGridView
• OpenFileDialog

Now we need to add some variables needed in Module.

Imports System.Data.Odbc
Module koneksi
    Public da As Odbc.OdbcDataAdapter
    Public dr As Odbc.OdbcDataReader
    Public cmd As Odbc.OdbcCommand
    Public conn As Odbc.OdbcConnection
    Public ds As New DataSet

    Sub koneksinya()
        conn = New Odbc.OdbcConnection("Dsn=crud")
        If conn.State = ConnectionState.Closed Then
            conn.Open()
        End If
    End Sub
End Module

Now go back to Form Users and Import ODBC Connection and create a global variable to store the value of the future photo location of the user.

Imports System.Data.Odbc 
Public Class Form1
   Public foto As String   

End Class

Then create a function to display existing data in the database to the DataGridView1

Sub grid()
    Call koneksinya()
    da = New OdbcDataAdapter("select code as CODE, name as NAME, address as ADDRESS ,phone as PHONE, photo as PHOTO from users", conn) 
    ds = New DataSet()
    da.Fill(ds)
    DataGridView1.DataSource = ds.Tables(0)
    DataGridView1.Columns("CODE").Width = 100
    DataGridView1.Columns("NAME").Width = 250
    DataGridView1.Columns("ADDRESS").Width = 250
    DataGridView1.Columns("PHONE").Width = 100
    DataGridView1.Columns("PHOTO").Width = 200
End Sub

Then, create a function again to empty or restore all the components

Sub clean()
    Button1.Enabled = True
    PictureBox2.Image = Nothing
    Button2.Enabled = False
    Button3.Enabled = False
    TextBox5.Enabled = True
    TextBox5.Clear()
    TextBox1.Clear()
    TextBox2.Clear()
    TextBox3.Clear()
    TextBox4.Clear()
    TextBox5.Focus()
End Sub

Now we call the function we have made into the Form Load and Disable the Edit Button and the Delete Button

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load  
        clean()
        grid()
        Button2.Enabled = False
        Button3.Enabled = False
End Sub

Next we type the code on the button to locate the user's photo

Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click   
        OpenFileDialog1.FileName = ""
        OpenFileDialog1.InitialDirectory = Application.StartupPath & "\member\"
        OpenFileDialog1.Filter = "Images File |*.jpg; *.png *.bmp;"
        OpenFileDialog1.ShowDialog()
        PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage

        If OpenFileDialog1.FileName = "" Then
            Exit Sub
        Else
            foto = OpenFileDialog1.FileName.ToString
            PictureBox1.Image = Image.FromFile(foto)
        End If
End Sub

NOTES :

member : Location / User photo folder

Now double click the Save Button and enter the code to store the user data

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Call koneksinya()
        If TextBox1.Text = "" Or TextBox2.Text = "" Or TextBox3.Text = "" Or TextBox5.Text = "" Or PictureBox1.Image Is Nothing Then
            MessageBox.Show("Please Complete All Fields", "CRUD", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Else
            Try
                cmd = New OdbcCommand("insert into users (code,name,address,phone,photo) Values ('" & TextBox5.Text & "','" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & foto.Replace("\", "\\").ToString & "')", conn)   
                cmd.ExecuteNonQuery()
                MessageBox.Show("Data Successfully Saved", "CRUD", MessageBoxButtons.OK, MessageBoxIcon.Information)
                clean()
                grid()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End If
End Sub

Now, click DataGridView1 and then enter the code in the Cell Click event in the properties to display the data that is inside DataGridView1 into the Textbox of each field.

Private Sub DataGridView1_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick   
        On Error Resume Next
        Button1.Enabled = False
        Button2.Enabled = True
        Button3.Enabled = True
        Dim row As DataGridViewRow = DataGridView1.CurrentRow
        Me.TextBox5.Text = row.Cells(0).Value.ToString()
        Me.TextBox5.Enabled = False
        Me.TextBox1.Text = row.Cells(1).Value.ToString()
        Me.TextBox2.Text = row.Cells(2).Value.ToString()
        Me.TextBox3.Text = row.Cells(3).Value.ToString()
        Me.PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage
        foto = row.Cells(4).Value.ToString()
        Me.PictureBox1.Image = Image.FromFile(foto)
End Sub

Now, double-click Edit Button and enter the code to change user data based on user code

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Call koneksinya()
        If TextBox1.Text = "" Or TextBox2.Text = "" Or TextBox3.Text = "" Or TextBox5.Text = "" Or PictureBox1.Image Is Nothing Then
            MessageBox.Show("Please Complete All Fields", "CRUD", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Else
            Try
                cmd = New OdbcCommand("update users set name = '" & TextBox1.Text & "', address = '" & TextBox2.Text & "', photo = '" & foto.Replace("\", "\\").ToString & "', phone = '" & TextBox3.Text & "' where code = '" & TextBox5.Text & "'", conn)   
                cmd.ExecuteNonQuery()
                MessageBox.Show("Data Successfully Modified", "CRUD", MessageBoxButtons.OK, MessageBoxIcon.Information)
                clean()
                grid()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End If
End Sub

Now, double-click Delete Button and enter the code to delete user data based on user code

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Call koneksinya()
        If TextBox5.Text = "" Then
            MessageBox.Show("Please Complete All Fields", "CRUD", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Else
            Try
                cmd = New OdbcCommand("delete from users where code = '" & TextBox5.Text & "'", conn)
                cmd.ExecuteNonQuery()
                MessageBox.Show("Data Successfully Deleted", "CRUD", MessageBoxButtons.OK, MessageBoxIcon.Information)   
                clean()
                grid()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End If
End Sub

Now double-click the Reset Button and enter the code to clear all Fields and then refresh the data in DataGridView1

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click   
        grid()
        clean()
End Sub

Finally, double-click Search Textbox and enter the code to search user data based on the similarity of the user name typed in Textbox search

Private Sub TextBox4_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox4.TextChanged
        Call koneksinya()
        da = New OdbcDataAdapter("select code as CODE, name as NAME, address as ADDRESS, phone as PHONE, photo as PHOTO from users where NAME like '%" & TextBox4.Text & "%'", conn)   
        ds = New DataSet()
        da.Fill(ds)
        DataGridView1.DataSource = ds.Tables(0)
        DataGridView1.Columns("CODE").Width = 100
        DataGridView1.Columns("NAME").Width = 250
        DataGridView1.Columns("ADDRESS").Width = 250
        DataGridView1.Columns("PHONE").Width = 100
        DataGridView1.Columns("PHOTO").Width = 200
End Sub

After all the code is inserted and there is no error, you immediately run the program
more or less the initial appearance like this picture below

Untuk lebih jelasnya, silahkan nonton video dibawah