i am using employee table to test if i am doing the whole program correct. I have 5 buttons. one is to add records and another to save(commit), to update, to delete and to clear. I am having trouble with the code to save the record to the data set. when i click add, it lets me add the new name but when i press save(commit), it wont save. i already added the data adapeter. this is my code:
Public Class Form1
Dim inc As Integer
Dim MaxRows As Integer
Dim con As New OleDb.OleDbConnection
Dim dbProvider As String
Dim dbSource As String
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql As String
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
dbProvider = "Provider = Microsoft.ACE.OLEDB.12.0;"
dbSource = "Data Source = C:\Users\lyndalocks\Desktop\TESTPROJECT1.accdb; Persist Security Info=false;"
con.ConnectionString = dbProvider & dbSource
con.Open()
sql = "SELECT * FROM [Employees]"
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "TEST PROJECT 1")
'MsgBox("Database is now open")
con.Close()
'MsgBox("Database is now closed")
MaxRows = ds.Tables("TEST PROJECT 1").Rows.Count
inc = -1
End Sub
Private Sub NavigateRecords()
txtFirstName.Text = ds.Tables("TEST PROJECT 1").Rows(inc).Item(1)
txtSurname.Text = ds.Tables("TEST PROJECT 1").Rows(inc).Item(2)
End Sub
Private Sub Button3_Click(sender As Object, e As EventArgs) Handles btnNext.Click
If inc <> MaxRows - 1 Then
inc = inc + 1
NavigateRecords()
Else
MsgBox("No More Rows")
End If
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles btnPrevious.Click
If inc > 0 Then
inc = inc - 1
NavigateRecords()
Else
MsgBox("First Record")
End If
End Sub
Private Sub Button4_Click(sender As Object, e As EventArgs) Handles btnLast.Click
If inc <> MaxRows - 1 Then
inc = MaxRows - 1
NavigateRecords()
End If
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles btnfirst.Click
If inc <> 0 Then
inc = 0
NavigateRecords()
End If
End Sub
Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
Dim cb As New OleDb.OleDbCommandBuilder(da)
ds.Tables("Test Project 1").Rows(inc).Item(1) = txtFirstName.Text
ds.Tables("Test Project 1").Rows(inc).Item(2) = txtSurname.Text
MsgBox("Data updated")
End Sub
Private Sub btnAddNew_Click(sender As Object, e As EventArgs) Handles btnAddNew.Click
btnCommit.Enabled = True
btnAddNew.Enabled = False
btnUpdate.Enabled = False
btnDelete.Enabled = False
txtFirstName.Clear()
txtSurname.Clear()
End Sub
Private Sub btnClear_Click(sender As Object, e As EventArgs) Handles btnClear.Click
btnCommit.Enabled = False
btnAddNew.Enabled = True
btnUpdate.Enabled = True
btnDelete.Enabled = True
inc = 0
NavigateRecords()
End Sub
Private Sub btnCommit_Click(sender As Object, e As EventArgs) Handles btnCommit.Click
If inc <> -1 Then
Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim dsNewRow As DataRow
dsNewRow = ds.Tables("Test Project 1").NewRow()
dsNewRow.Item("FirstName") = txtFirstName.Text
dsNewRow.Item("LastName") = txtSurname.Text
ds.Tables("Test Project 1").Rows.Add(dsNewRow)
da.Update(ds, "Test Project 1")
MsgBox("New Record added to the Database")
btnCommit.Enabled = False
btnAddNew.Enabled = True
btnUpdate.Enabled = True >
btnDelete.Enabled = True
End If
End Sub
