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

Maybe there's error happening, try this. On Visual Studio enable Common Language Runtime Exception, hit CTRL + ALT + E and Check Common Language Runtime Exception then OK. Run again your program and see if there's error.

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.