i am using vb.net application with OleDb database. In a form i have Search,Add,Edit,Delete buttons for the data. Although search data is working but Add,Edit,Delete are not working. Here is my code.

Imports System.Data.OleDb

Public Class Form1
    Dim CN As New OleDb.OleDbConnection

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Me.RefreshData()
    CN = New OleDb.OleDbConnection
    CN.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Satyam\Documents\Satyam.accdb;"
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Src.Click

    Dim CN As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Satyam\Documents\Satyam.accdb;" & "Persist Security Info=False;" & "Jet OLEDB:Database Password=" & "your pass" & ";")
    Dim cmd As OleDbCommand
    Dim dr As OleDbDataReader
    Try
        CN.Open()
        cmd = New OleDbCommand("SELECT *  from DailyWorkLoadRegister WHERE ID=" & txtID.Text & "", CN)

        dr = cmd.ExecuteReader
        If dr.Read Then
            Me.txtID.Text = dr("ID")
            Me.txtAge.Text = dr("Age")
            Me.txtSex.Text = dr("Sex")
            Me.txtFullName.Text = dr("FullName")
            Me.txtWard.Text = dr("Ward")

            dr.Close()
        Else
            MsgBox("No Record")
        End If
    Catch
    End Try

    CN.Close()
End Sub

Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
    Me.Close()
End Sub

Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
    Me.txtID.Text = ""
    Me.txtAge.Text = ""
    Me.txtFullName.Text = ""
    Me.txtSex.Text = ""
    Me.txtWard.Text = ""
    Me.txtID.Tag = ""

    Me.btnedit.Enabled = True

    Me.btnAdd.Text = "Add"

End Sub

Private Sub RefreshData()
    If Not CN.State = ConnectionState.Open Then
        CN.Open()
    End If
    Dim da As New OleDb.OleDbDataAdapter("SELECT ID as [ID], " & _
                                        "FullName as [Name], Sex, Age, Ward " & _
                                        " FROM DailyWorkLoadRegister ORDER BY ID", CN)
    Dim dt As New DataTable

    da.Fill(dt)

    Me.dgvData.DataSource = dt

    CN.Close()
End Sub
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

    Dim cmd As New OleDb.OleDbCommand
    If Not CN.State = ConnectionState.Open Then

        CN.Open()
    End If

    cmd.Connection = CN

    If Me.txtID.Tag & "" = "" Then

        cmd.CommandText = "INSERT INTO DailyWorkLoadRegister(ID,Age,FullName,Sex,Ward) " & _
                        "VALUES(" & Me.txtID.Text & ",'" & Me.txtAge.Text & "','" & _
                        Me.txtFullName.Text & "','" & Me.txtSex.Text & "','" & Me.txtWard.Text & "')"
        cmd.ExecuteNonQuery()
    Else

        cmd.CommandText = "Update DailyWorkLoadRegister" & _
                    "SET ID=" & Me.txtID.Text & _
                    ", FullName='" & Me.txtFullName.Text & "'" & _
                     ", Sex='" & Me.txtSex.Text & "'" & _
                      ", Age='" & Me.txtAge.Text & "'" & _
                       ", Ward='" & Me.txtWard.Text & "'" & _
                       "WHERE ID=" & Me.txtID.Tag
        cmd.ExecuteNonQuery()

    End If

    RefreshData()

    Me.btnClear.PerformClick()

    CN.Close()

End Sub

Private Sub btnedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnedit.Click

    If Me.dgvData.Rows.Count > 0 Then
        If Me.dgvData.SelectedRows.Count > 0 Then
            Dim intID As Integer = Me.dgvData.SelectedRows(0).Cells("id").Value

            If Not CN.State = ConnectionState.Open Then
                CN.Open()
            End If

            Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM DailyWorkLoadRegister " & _
                                                 "where ID=" & intID, CN)
            Dim dt As New DataTable
            da.Fill(dt)

            Me.txtID.Text = intID
            Me.txtFullName.Text = dt.Rows(0).Item("FullName")
            Me.txtSex.Text = dt.Rows(0).Item("Sex")
            Me.txtAge.Text = dt.Rows(0).Item("Age")
            Me.txtWard.Text = dt.Rows(0).Item("Ward")
            Me.txtID.Tag = intID

            Me.btnAdd.Text = "Update"

            Me.btnedit.Enabled = False
            CN.Close()
        End If
    End If
End Sub

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click

    If Me.dgvData.Rows.Count > 0 Then
        If Me.dgvData.SelectedRows.Count > 0 Then
            Dim intID As Integer = Me.dgvData.SelectedRows(0).Cells("id").Value
            If Not CN.State = ConnectionState.Open Then
                CN.Open()
            End If

            Dim cmd As New OleDb.OleDbCommand
            cmd.Connection = CN
            cmd.CommandText = " DELETE FROM DailyWorkLoadRegister WHERE ID=" & intID
            cmd.ExecuteNonQuery()

            Me.RefreshData()


            CN.Close()

        End If
    End If
End Sub
End Class
cmd.CommandText = "INSERT INTO DailyWorkLoadRegister(ID,Age,FullName,Sex,Ward) " & "VALUES(" & Me.txtID.Text & ",'" & Me.txtAge.Text & "','" & Me.txtFullName.Text & "','" & Me.txtSex.Text & "','" & Me.txtWard.Text & "')"
cmd.ExecuteNonQuery()

//the above code confirms the fields Age,FullName,Sex,Ward are like varchar type..
//if age is numeric means 
//use:          " & Cint(Me.txtAge.Text) & "
//instead of    :   '" & Me.txtAge.Text & "'
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.