How to perform insert,update,delete and nevigation query based using Vb.net ?

Imports System.Data.OleDb
Public Class dbBuilder
    Dim objcon As New System.Data.OleDb.OleDbConnection

(WindowsApplication1.My.Settings.db2_2010ConnectionString)
    Dim objadp As OleDbDataAdapter
    Dim ds As Data.DataSet
    Dim rec As Integer
    Dim flgAdd As Boolean


    Private Sub db_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        rec = 0
        objadp = New OleDbDataAdapter("Select [eno],[ename],[skill],[salary],[address] from [emp]", objcon)
        ds = New DataSet
        objadp.Fill(ds, "E")
        display(rec)
        readOnlyTextBox(True)
        EnableButton(True, True, True, False, True, True, True, True, True)
    End Sub
    Public Sub ClearTextBox()
        txtEno.Text = ""
        txtename.Text = ""
        txtaddress.Text = ""
        txtskill.Text = ""
        txtsalary.Text = ""

    End Sub
    Public Sub readOnlyTextBox(ByVal bl As Boolean)
        txtEno.ReadOnly = bl
        txtename.ReadOnly = bl
        txtaddress.ReadOnly = bl
        txtskill.ReadOnly = bl
        txtsalary.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("E").Rows.Count > 0) Then
            txtEno.Text = ds.Tables("E").Rows(r).Item("eno").ToString()
            txtename.Text = ds.Tables("E").Rows(r).Item("ename").ToString()
            txtsalary.Text = ds.Tables("E").Rows(r).Item("salary").ToString()
            txtskill.Text = ds.Tables("E").Rows(r).Item("skill").ToString()
            txtaddress.Text = ds.Tables("E").Rows(r).Item("address").ToString()

        End If
    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("E").Rows.Count - 1
        display(rec)
    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 btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
        flgAdd = False
        readOnlyTextBox(False)
        txtEno.ReadOnly = True

        EnableButton(False, False, False, True, False, False, False, False, False)
    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 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
            Dim query As String
            query = "Delete from [emp] where [eno]=" + txtEno.Text
            objcon.Open()
            objadp.DeleteCommand = New OleDbCommand
            objadp.DeleteCommand.CommandText = query
            objadp.DeleteCommand.Connection = objcon
            objadp.DeleteCommand.ExecuteNonQuery()

            ds.Tables("E").Clear()
            objadp.Fill(ds, "E")
            objcon.Close()
        End If
        Button1_Click(sender, e)
    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 query As String
                query = "insert into [emp]([eno],[ename],[skill],[salary],[address]) values(" + txtEno.Text + ",''''" + txtename.Text + "'''',''''" + txtskill.Text + "''''," + txtsalary.Text + ",''''" + txtaddress.Text + "'''')"

                objcon.Open()
                objadp.InsertCommand = New OleDbCommand
                objadp.InsertCommand.CommandText = query
                objadp.InsertCommand.Connection = objcon
                objadp.InsertCommand.ExecuteNonQuery()
                ds.Tables("E").Clear()
                objadp.Fill(ds, "E")
                objcon.Close()
            End If
        Else
            If (MsgBox("Sure U want to Update This Record?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes) Then
                Dim query As String
                query = "Update [emp] set [ename]=''''" + txtename.Text + "'''',[skill]=''''" + txtskill.Text + "'''',[salary]=" + txtsalary.Text + ",[address]=''''" + txtaddress.Text + "'''' where [eno]=" + txtEno.Text
                objcon.Open()
                objadp.UpdateCommand = New OleDbCommand
                objadp.UpdateCommand.CommandText = query
                objadp.UpdateCommand.Connection = objcon
                objadp.UpdateCommand.ExecuteNonQuery()
                ds.Tables("E").Clear()
                objadp.Fill(ds, "E")
                objcon.Close()

            End If
        End If
        Button1_Click(sender, e)
    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("E").Rows.Count = rec) Then
            rec = 0
        End If
        display(rec)

    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 Eno:", "FIND")
        Dim i As Integer
        Dim flg As Boolean
        flg = False
        For i = 0 To ds.Tables("E").Rows.Count - 1
            If (eno.Trim() = ds.Tables("E").Rows(i).Item("Eno").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 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 (0)
by Swapnalee   Patil  in VB.Net  on 3/3/2015 10:16:42 PM  


Post Your Comment
*