Perform Insert,Update,Delete Using Command Builder

Imports System.Data.OleDb
Public Class DB_CommandBuilder
    Dim objcon As New System.Data.OleDb.OleDbConnection(WindowsApplication1.My.Settings.db2_2010ConnectionString)
    Dim objadp As OleDb.OleDbDataAdapter
    Dim ds As Data.DataSet
    Dim objCommandBuilder As OleDb.OleDbCommandBuilder
    Dim rec As Integer

    Dim flgAdd As Boolean
    Private Sub DB_CommandBuilder_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        rec = 0
        objcon.Open()
        objadp = New OleDbDataAdapter("Select [rno],[name],[classid] from [stud]", objcon)
        ds = New DataSet
        objadp.Fill(ds, "Stud")
        objCommandBuilder = New OleDb.OleDbCommandBuilder(objadp)

        objadp = New OleDbDataAdapter("Select [classid],[cname] from [class]", objcon)
        objadp.Fill(ds, "Class")

        objcon.Close()

        cmbclass.DisplayMember = "cname"
        cmbclass.ValueMember = "classid"
        cmbclass.DataSource = ds.Tables("Class") ''''''''''''''''bind data table to combobox
        display(rec)



        readOnlyTextBox(True)
        EnableButton(True, True, True, False, True, True, True, True, True)
    End Sub
    Public Sub ClearTextBox()
        txtrno.Text = ""
        txtname.Text = ""
    End Sub
    Public Sub readOnlyTextBox(ByVal bl As Boolean)
        txtrno.ReadOnly = bl
        txtname.ReadOnly = bl
    End Sub
    Public Sub EnableButton(ByVal bAdd As Boolean, ByVal bDelete As Boolean, ByVal bEdit As Boolean, ByVal bSave As Boolean, ByVal bFirst As Boolean, ByVal blast As Boolean, ByVal bprev As Boolean, ByVal bnext As Boolean, ByVal bFind As Boolean)
        btnAdd.Enabled = bAdd
        btnDelete.Enabled = bDelete
        btnEdit.Enabled = bEdit
        btnSave.Enabled = bSave
        btnfirst.Enabled = bFirst
        btnLast.Enabled = blast
        btnPrev.Enabled = bprev
        btnmoveNext.Enabled = bnext
        btnFind.Enabled = bFind
    End Sub
    Public Sub display(ByVal r As Integer)
        If (ds.Tables("Stud").Rows.Count > 0) Then
            txtrno.Text = ds.Tables("Stud").Rows(r).Item("rno").ToString()
            txtname.Text = ds.Tables("Stud").Rows(r).Item("name").ToString()
            cmbclass.SelectedValue = ds.Tables("Stud").Rows(r).Item("classid").ToString()
            DataGridView1.DataSource = ds.Tables("Stud")
        End If
    End Sub

    Private Sub btnFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFind.Click
        Dim eno As String = InputBox("Enter rno:", "FIND")
        Dim i As Integer
        Dim flg As Boolean
        flg = False
        For i = 0 To ds.Tables("Stud").Rows.Count - 1
            If (eno.Trim() = ds.Tables("Stud").Rows(i).Item("rno").ToString()) Then
                rec = i
                display(rec)
                flg = True
                Exit For
            End If
        Next
        If (flg = False) Then
            MsgBox("Record not found")
        End If
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        display(rec)
        readOnlyTextBox(True)
        EnableButton(True, True, True, False, True, True, True, True, True)
    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        flgAdd = True
        readOnlyTextBox(False)
        ClearTextBox()
        EnableButton(False, False, False, True, False, False, False, False, False)
    End Sub
    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        If (flgAdd = True) Then
            If (MsgBox("Sure U want to Add New Record?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes) Then

                Dim dr As DataRow = ds.Tables("Stud").NewRow()
                dr("rno") = txtrno.Text
                dr("name") = txtname.Text
                dr("classid") = cmbclass.SelectedValue
                ds.Tables("Stud").Rows.Add(dr)
                objadp.InsertCommand = objCommandBuilder.GetInsertCommand()
                objadp.Update(ds, "Stud")
                ds.AcceptChanges()

            End If
        Else
            If (MsgBox("Sure U want to Update This Record?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes) Then

                Dim dr As DataRow = ds.Tables("Stud").Rows(rec)
                dr("rno") = txtrno.Text
                dr("name") = txtname.Text
                dr("classid") = cmbclass.SelectedValue
                objadp.UpdateCommand = objCommandBuilder.GetUpdateCommand()
                objadp.Update(ds, "Stud")
                ds.AcceptChanges()

            End If
        End If
        readOnlyTextBox(True)
        EnableButton(True, True, True, False, True, True, True, True, True)
    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        If (MsgBox("Sure U want to Delete?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes) Then

            ds.Tables("Stud").Rows.RemoveAt(rec)
            objadp.DeleteCommand = objCommandBuilder.GetDeleteCommand()
            objadp.Update(ds, "Stud")
            ds.AcceptChanges()

        End If
        Button1_Click(sender, e)
    End Sub

    Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
        flgAdd = False
        readOnlyTextBox(False)
        txtrno.ReadOnly = True

        EnableButton(False, False, False, True, False, False, False, False, False)
    End Sub

    Private Sub btnfirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnfirst.Click
        rec = 0
        display(rec)
    End Sub

    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        rec = ds.Tables("Stud").Rows.Count - 1
        display(rec)
    End Sub

    Private Sub btnmoveNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnmoveNext.Click
        rec = rec + 1
        If (ds.Tables("Stud").Rows.Count = rec) Then
            rec = 0
        End If
        display(rec)
    End Sub

    Private Sub btnPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrev.Click
        rec = rec - 1
        If (rec <= 0) Then
            rec = 1
        End If
        display(rec)
    End Sub
End Class

Imports System.Data.OleDb
Public Class DB_CommandBuilder
    Dim objcon As New System.Data.OleDb.OleDbConnection(WindowsApplication1.My.Settings.db2_2010ConnectionString)
    Dim objadp As OleDb.OleDbDataAdapter
    Dim ds As Data.DataSet
    Dim objCommandBuilder As OleDb.OleDbCommandBuilder
    Dim rec As Integer

    Dim flgAdd As Boolean
    Private Sub DB_CommandBuilder_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        rec = 0
        objcon.Open()
        objadp = New OleDbDataAdapter("Select [rno],[name],[classid] from [stud]", objcon)
        ds = New DataSet
        objadp.Fill(ds, "Stud")
        objCommandBuilder = New OleDb.OleDbCommandBuilder(objadp)

        objadp = New OleDbDataAdapter("Select [classid],[cname] from [class]", objcon)
        objadp.Fill(ds, "Class")

        objcon.Close()

        cmbclass.DisplayMember = "cname"
        cmbclass.ValueMember = "classid"
        cmbclass.DataSource = ds.Tables("Class") ''''''''''''''''bind data table to combobox
        display(rec)



        readOnlyTextBox(True)
        EnableButton(True, True, True, False, True, True, True, True, True)
    End Sub
    Public Sub ClearTextBox()
        txtrno.Text = ""
        txtname.Text = ""
    End Sub
    Public Sub readOnlyTextBox(ByVal bl As Boolean)
        txtrno.ReadOnly = bl
        txtname.ReadOnly = bl
    End Sub
    Public Sub EnableButton(ByVal bAdd As Boolean, ByVal bDelete As Boolean, ByVal bEdit As Boolean, ByVal bSave As Boolean, ByVal bFirst As Boolean, ByVal blast As Boolean, ByVal bprev As Boolean, ByVal bnext As Boolean, ByVal bFind As Boolean)
        btnAdd.Enabled = bAdd
        btnDelete.Enabled = bDelete
        btnEdit.Enabled = bEdit
        btnSave.Enabled = bSave
        btnfirst.Enabled = bFirst
        btnLast.Enabled = blast
        btnPrev.Enabled = bprev
        btnmoveNext.Enabled = bnext
        btnFind.Enabled = bFind
    End Sub
    Public Sub display(ByVal r As Integer)
        If (ds.Tables("Stud").Rows.Count > 0) Then
            txtrno.Text = ds.Tables("Stud").Rows(r).Item("rno").ToString()
            txtname.Text = ds.Tables("Stud").Rows(r).Item("name").ToString()
            cmbclass.SelectedValue = ds.Tables("Stud").Rows(r).Item("classid").ToString()
            DataGridView1.DataSource = ds.Tables("Stud")
        End If
    End Sub

    Private Sub btnFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFind.Click
        Dim eno As String = InputBox("Enter rno:", "FIND")
        Dim i As Integer
        Dim flg As Boolean
        flg = False
        For i = 0 To ds.Tables("Stud").Rows.Count - 1
            If (eno.Trim() = ds.Tables("Stud").Rows(i).Item("rno").ToString()) Then
                rec = i
                display(rec)
                flg = True
                Exit For
            End If
        Next
        If (flg = False) Then
            MsgBox("Record not found")
        End If
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        display(rec)
        readOnlyTextBox(True)
        EnableButton(True, True, True, False, True, True, True, True, True)
    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        flgAdd = True
        readOnlyTextBox(False)
        ClearTextBox()
        EnableButton(False, False, False, True, False, False, False, False, False)
    End Sub
    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        If (flgAdd = True) Then
            If (MsgBox("Sure U want to Add New Record?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes) Then

                Dim dr As DataRow = ds.Tables("Stud").NewRow()
                dr("rno") = txtrno.Text
                dr("name") = txtname.Text
                dr("classid") = cmbclass.SelectedValue
                ds.Tables("Stud").Rows.Add(dr)
                objadp.InsertCommand = objCommandBuilder.GetInsertCommand()
                objadp.Update(ds, "Stud")
                ds.AcceptChanges()

            End If
        Else
            If (MsgBox("Sure U want to Update This Record?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes) Then

                Dim dr As DataRow = ds.Tables("Stud").Rows(rec)
                dr("rno") = txtrno.Text
                dr("name") = txtname.Text
                dr("classid") = cmbclass.SelectedValue
                objadp.UpdateCommand = objCommandBuilder.GetUpdateCommand()
                objadp.Update(ds, "Stud")
                ds.AcceptChanges()

            End If
        End If
        readOnlyTextBox(True)
        EnableButton(True, True, True, False, True, True, True, True, True)
    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        If (MsgBox("Sure U want to Delete?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes) Then

            ds.Tables("Stud").Rows.RemoveAt(rec)
            objadp.DeleteCommand = objCommandBuilder.GetDeleteCommand()
            objadp.Update(ds, "Stud")
            ds.AcceptChanges()

        End If
        Button1_Click(sender, e)
    End Sub

    Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
        flgAdd = False
        readOnlyTextBox(False)
        txtrno.ReadOnly = True

        EnableButton(False, False, False, True, False, False, False, False, False)
    End Sub

    Private Sub btnfirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnfirst.Click
        rec = 0
        display(rec)
    End Sub

    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        rec = ds.Tables("Stud").Rows.Count - 1
        display(rec)
    End Sub

    Private Sub btnmoveNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnmoveNext.Click
        rec = rec + 1
        If (ds.Tables("Stud").Rows.Count = rec) Then
            rec = 0
        End If
        display(rec)
    End Sub

    Private Sub btnPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrev.Click
        rec = rec - 1
        If (rec <= 0) Then
            rec = 1
        End If
        display(rec)
    End Sub
End Class


Average Rating (1)
by Firdosh Jahan   Memon  in VB.Net  on 3/24/2015 3:07:28 AM  


Post Your Comment
*


nice
by   bhavesh   on  3/17/2015 4:55:51 AM