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