CURD Operation into My_Sql Database Using VB.NET

 

Create, Update, Read, Delete and Search Operation into My_Sql Database Using VB.NET

I am Creating Windows application using VB.Net. In this Article you will learn about Select, Insert, Update, delete and Search Operation in VB.Net and MySQL. Here I am displaying Records in Datagridview from MySQL Database.

I am Creating windows application in vb.net named CURD Operation into My_Sql Database Using VB.NET. In this application I am using Four Textbox, one Datagridview, One PictureBox, Five Button, One ComboBox, One CheckBox, One Datetimepicker controls and Six Labels.

Step 1 => Create My-SQl Database "curd.sql"

Download Database Here

Step 2 => Make a Form Design

I am using Following Controls and it's name
txt_proCode.Text : It is used to get the Product Code.
txt_ProName.Text : It is used to get the ProductName.
Combo_proGroup.Text : It is used to get the Product Group.
txt_Price.Text : It is used to get the Price.
dtp_ExpDate.Value : It is used to get the Expaired.
Check_Status.Checked.ToString : It is used to get the Status Available or Not Available.
Pic_ProductPic : It is used to get the Product Images.

Step 3 => Import "MysqlData.dll"

"MysqlData.dll" Download Here

Following Coding

Imports MySql.Data.MySqlClient
Imports System.IO

Step 4 => Connect My_Sql Database into VB.NET

Following Coding

    Dim conn As New MySqlConnection("server=localhost;port=3306;username=root;password=;database=curd")
    Dim i As Integer
    Dim dr As MySqlDataReader

Step 5 => Browse Images Load to PictureBox

Following Coding

        Dim pop As OpenFileDialog = New OpenFileDialog
        If pop.ShowDialog  Windows.Forms.DialogResult.Cancel Then
        Pic_ProductPic.Image = Image.FromFile(pop.FileName)
        End If

Step 6 => Insert Record to My_Sql Database Using VB.NET

Following Coding

       Public Sub save()
        Try
            conn.Open()
            Dim cmd As New MySqlCommand("INSERT INTO `tbl_curd`(`PROID`, `PRONAME`, `PROGROUP`, `PRICE`, `EXPDATE`, `STATUS`,`IMG`) VALUES (@PROID,@PRONAME,@PROGROUP,@PRICE,@EXPDATE,@STATUS,@IMG)", conn)
            cmd.Parameters.Clear()
            cmd.Parameters.AddWithValue("@PROID", txt_proCode.Text)
            cmd.Parameters.AddWithValue("@PRONAME", txt_ProName.Text)
            cmd.Parameters.AddWithValue("@PROGROUP", Combo_proGroup.Text)
            cmd.Parameters.AddWithValue("@PRICE", CDec(txt_Price.Text))
            cmd.Parameters.AddWithValue("@EXPDATE", CDate(dtp_ExpDate.Value))
            cmd.Parameters.AddWithValue("@STATUS", CBool(Check_Status.Checked.ToString))

            'image convert to binary format

            Dim FileSize As New UInt32
            Dim mstream As New System.IO.MemoryStream
            Pic_ProductPic.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)
            Dim picture() As Byte = mstream.GetBuffer
            FileSize = mstream.Length
            mstream.Close()
            cmd.Parameters.AddWithValue("@IMG", picture)

            i = cmd.ExecuteNonQuery
            If i > 0 Then
                MessageBox.Show("Record Save Success !", "CURD", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Else
                MessageBox.Show("Record Save Failed !", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            conn.Close()
        End Try
        clear()
    End Sub

Step 7 => Update Record to My_Sql Database Using VB.NET

Following Coding

    Public Sub edit()
        Try
            conn.Open()
            Dim cmd As New MySqlCommand("UPDATE `tbl_curd` SET `PRONAME`=@PRONAME,`PROGROUP`=@PROGROUP,`PRICE`=@PRICE,`EXPDATE`=@EXPDATE,`STATUS`=@STATUS,`IMG`=@IMG WHERE `PROID`=@PROID", conn)
            cmd.Parameters.Clear()

            cmd.Parameters.AddWithValue("@PRONAME", txt_ProName.Text)
            cmd.Parameters.AddWithValue("@PROGROUP", Combo_proGroup.Text)
            cmd.Parameters.AddWithValue("@PRICE", CDec(txt_Price.Text))
            cmd.Parameters.AddWithValue("@EXPDATE", CDate(dtp_ExpDate.Value))
            cmd.Parameters.AddWithValue("@STATUS", CBool(Check_Status.Checked.ToString))

            'image convert to binary format

            Dim FileSize As New UInt32
            Dim mstream As New System.IO.MemoryStream
            Pic_ProductPic.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)
            Dim picture() As Byte = mstream.GetBuffer
            FileSize = mstream.Length
            mstream.Close()
            cmd.Parameters.AddWithValue("@IMG", picture)
            cmd.Parameters.AddWithValue("@PROID", txt_proCode.Text)
            i = cmd.ExecuteNonQuery
            If i > 0 Then
                MessageBox.Show("Record Update Success !", "CURD", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Else
                MessageBox.Show("Record Update Failed !", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            conn.Close()
        End Try
        clear()
        DGV_load()
    End Sub

Step 8 => Delete Record to My_Sql Database Using VB.NET

Following Coding

Public Sub delete()

        If MsgBox("Are You Sure Delete This Record", MsgBoxStyle.Question + vbYesNo) = vbYes Then
            Try
                conn.Open()
                Dim cmd As New MySqlCommand("DELETE FROM `tbl_curd` WHERE `PROID`=@PROID", conn)
                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@PROID", txt_proCode.Text)

                i = cmd.ExecuteNonQuery
                If i > 0 Then
                    MessageBox.Show("Record Deleted Success !", "CURD", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Else
                    MessageBox.Show("Record Deleted Failed !", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
                End If

            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                conn.Close()
            End Try
            clear()
            DGV_load()
        Else
            Return
        End If
    End Sub

Step 9 => Clear Record from Controls

Following Coding

    Public Sub clear()
        txt_proCode.Clear()
        txt_Proname.Clear()
        txt_price.Clear()
        combo_proGroup.Text = ""
        dtp_ExpDate.Value = Now
        Check_Status.CheckState = False
        Pic_ProductPic.Image = Nothing

        txt_proCode.ReadOnly = False
        btn_save.Enabled = True
    End Sub

Step 10 => Search Record to My_Sql Database Using VB.NET

Following Coding

TextChanged Events Selected

DataGridView1.Rows.Clear()
        Try
            conn.Open()
            Dim cmd As New MySqlCommand("SELECT * FROM tbl_curd WHERE PROID like '%" & txt_search.Text & "%' or PRONAME like '%" & txt_search.Text & "%'", conn)
            dr = cmd.ExecuteReader
            While dr.Read
                DataGridView1.Rows.Add(dr.Item("PROID"), dr.Item("PRONAME"), dr.Item("PROGROUP"), dr.Item("PRICE"), dr.Item("EXPDATE"), Format(CBool(dr.Item("STATUS"))), dr.Item("IMG"))
            End While
            dr.Dispose()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            conn.Close()
        End Try
    End Sub

Step 11 => DataGridView Cell Click Then Send data to Controls

Following Coding

CellClick Events Selected

        txt_proCode.Text = DataGridView1.CurrentRow.Cells(0).Value
        txt_ProName.Text = DataGridView1.CurrentRow.Cells(1).Value
        Combo_proGroup.Text = DataGridView1.CurrentRow.Cells(2).Value
        txt_Price.Text = DataGridView1.CurrentRow.Cells(3).Value
        dtp_ExpDate.Text = DataGridView1.CurrentRow.Cells(4).Value
        Check_Status.Checked = DataGridView1.CurrentRow.Cells(5).Value

        Dim bytes As [Byte]() = DataGridView1.CurrentRow.Cells(6).Value
        Dim ms As New MemoryStream(bytes)
        Pic_ProductPic.Image = Image.FromStream(ms)

        txt_proCode.ReadOnly = True
        Btn_save.Enabled = False

Step 12 => DataGridView Load Mysql Data

Following Coding

    Public Sub DGV_load()
        DataGridView1.Rows.Clear()
        Try
            conn.Open()
            Dim cmd As New MySqlCommand("SELECT * FROM tbl_curd", conn)
            dr = cmd.ExecuteReader
            While dr.Read
                DataGridView1.Rows.Add(dr.Item("PROID"), dr.Item("PRONAME"), dr.Item("PROGROUP"), dr.Item("PRICE"), dr.Item("EXPDATE"), Format(CBool(dr.Item("STATUS"))), dr.Item("IMG"))
            End While
            dr.Dispose()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            conn.Close()
        End Try
    End Sub

Thanks

Happy Coding.....

Subscribe For More Projects...