ads_header

Tutorial CRUD Program With MySQL Database In Visual Studio

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


    Powered by Blogger.