42 Create a form using MySql Data Base Data Adapter, Data Binding and Command Builder in VB.NET

DataBase.:

Form Design:

Code :

Imports MySql.Data.MySqlClient

Public Class Form1

    Dim cmd As MySqlCommand
    Dim dr As MySqlDataReader
    Dim conn As MySqlConnection
    Dim builder As MySqlCommandBuilder
    Dim adpt As New MySqlDataAdapter
    Dim ds As New DataSet
    Dim row As DataRow

    'Dim last As Long
    'Dim con As Control
    'Dim lastFormSize As Integer = (Me.Size.Height * Me.Size.Width)


    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Connect_Button.Click
        conn = New MySqlConnection("server=localhost;database=test;uid=root;password=;SSL Mode=None")
        conn.Open()
    End Sub


    ' For inserting Data
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Show_Button.Click

        ' cmd = New MySqlCommand("Insert into staff values('" & ComboBox1.Text & "','" & ComboBox2.Text & "','" & ComboBox3.Text & "'," & ComboBox4.Text & ",'" & ComboBox5.Text & "')", conn)
        'cmd.ExecuteNonQuery()
        ds = New DataSet()

        adpt = New MySqlDataAdapter("select * from staff", conn)

        adpt.Fill(ds, "staff")

        DataGridView1.DataSource = ds
        DataGridView1.DataMember = "staff"


        ' For clearing text from combobox
        For Each i In Controls
            If TypeOf i Is ComboBox Then
                i.Text = ""
            End If
        Next

        conn.Close()
    End Sub

    ' TO insert
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Insert_Button.Click
        builder = New MySqlCommandBuilder(adpt)

        builder.GetInsertCommand()

        row = ds.Tables("staff").NewRow()

        row(0) = ComboBox1.Text
        row(1) = ComboBox2.Text
        row(2) = ComboBox3.Text
        row(3) = ComboBox4.Text
        row(4) = ComboBox5.Text

        ds.Tables("staff").Rows.Add(row)
        adpt.Update(ds, "staff")
    End Sub

    ' To Delete
    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Delete_Button.Click
        builder = New MySqlCommandBuilder(adpt)
        builder.GetDeleteCommand()
        Dim r As String
        Dim i As Integer = 0

        For Each row In ds.Tables("staff").Rows

            r = row.Item(0)
            If r = ComboBox1.Text Then
                ds.Tables("staff").Rows(i).Delete()
            End If
            i = i + 1
        Next

        MsgBox(" Record Deleted ")
        adpt.Update(ds, "staff")
    End Sub


    ' To Update
    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Update_Button.Click
        builder = New MySqlCommandBuilder(adpt)
        builder.GetUpdateCommand()

        Dim row1 As DataRow
        Dim i As Integer = 0

        For i = 0 To ds.Tables("staff").Rows.Count - 1
            If ds.Tables("staff").Rows(i).Item("staff_id") = ComboBox1.Text Then
                row1 = ds.Tables("staff").Rows(i)
            End If
        Next

        If Not row1 Is Nothing Then
            row1(1) = ComboBox2.Text
            row1(2) = ComboBox3.Text
            row1(3) = ComboBox4.Text
            row1(4) = ComboBox5.Text
            MsgBox("Update")
        End If
        adpt.Update(ds, "staff")

    End Sub

    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
        ComboBox1.Text = DataGridView1.Rows(e.RowIndex).Cells(0).Value
        ComboBox2.Text = DataGridView1.Rows(e.RowIndex).Cells(1).Value
        ComboBox3.Text = DataGridView1.Rows(e.RowIndex).Cells(2).Value
        ComboBox4.Text = DataGridView1.Rows(e.RowIndex).Cells(3).Value
        ComboBox5.Text = DataGridView1.Rows(e.RowIndex).Cells(4).Value
    End Sub

    Private Sub Button1_Click_1(sender As Object, e As EventArgs) Handles Button1.Click
        'Dim data As DataGridViewBindingCompleteEventArgs

        adpt = New MySqlDataAdapter("select * from staff", conn)

        adpt.Fill(ds, "staff")

        ComboBox1.DataBindings.Add("text", ds, "staff.staff_id")
        ComboBox2.DataBindings.Add("text", ds, "staff.name")
        ComboBox3.DataBindings.Add("text", ds, "staff.department")
        ComboBox4.DataBindings.Add("text", ds, "staff.mobile_number")
        ComboBox5.DataBindings.Add("text", ds, "staff.history")

        DataGridView1.DataSource = ds
        DataGridView1.DataMember = "staff"

        ListBox1.DataSource = ds
        ListBox1.DisplayMember = "staff.name"

        ComboBox6.DataSource = ds
        ComboBox6.DisplayMember = "staff.staff_id"


    End Sub

    Private Sub ComboBox6_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox6.SelectedIndexChanged
        TextBox1.Text = ComboBox6.SelectedValue.ToString
    End Sub

    Private Sub Form1_Closed(sender As Object, e As EventArgs) Handles Me.Closed
        conn.Close()

    End Sub

End Class

Output:

Show DataBase Record:

Insert Using Data Adapter: 

Data Binding Show:

• Tweet
• Share
• Share
• Share

Related Post