Issues with saving changes to database

[ohok]

I have buttons working that cycles through all the records and everything shows up fine. I also made a simple DB program to test what I was doing without all the extra stuff my current program has and it works fine. Updates the dataset and then saves to the database and reflects the changes. When I tried adding it to this program, I get errors when I click "Save" and "Commit".

The "Save" (updates DS) error is: "Additional information: Update requires a valid UpdateCommand when passed DataRow collection with modified rows."

The "Commit" (commits changes to DB) error is: "Additional information: Syntax error in INSERT INTO statement."

Not sure what either of these mean, the same code worked fine in the standalone program. Looking at another site, they show the exact Save error I'm getting and say it's because I don't have a command builder, but it's up there. Here is all relevant code to the DB stuff:

'''Testing DB functionality
    Dim inc As Integer
    Dim MaxRows As Integer
 
    Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim data As OleDb.OleDbDataAdapter
    Dim sql As String
 
     con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\work\EyeBase.mdb"
        con.Open()
 
 
        sql = "SELECT * FROM History"
        data = New OleDb.OleDbDataAdapter(sql, con)
        data.Fill(ds, "History")
 
        con.Close()
 
 
        MaxRows = ds.Tables("History").Rows.Count
        inc = -1
 
Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
        If inc <> MaxRows - 1 Then
            inc = inc + 1
            NavigateRecords()
        Else
            MsgBox("No More Rows")
        End If
    End Sub
 
    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        If inc <> MaxRows - 1 Then
            inc = MaxRows - 1
            NavigateRecords()
        End If
    End Sub
 
    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        If inc <> 0 Then
            inc = 0
            NavigateRecords()
        End If
    End Sub
 
    Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
        If inc > 0 Then
            inc = inc - 1
            NavigateRecords()
        ElseIf inc = -1 Then
            MsgBox("No Records Yet")
        ElseIf inc = 0 Then
            MsgBox("First Record")
        End If
    End Sub
 
 
    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        If inc = -1 Then
            MsgBox("Can't update yet")
        Else
 
 
            ds.Tables("History").Rows(inc).Item(3) = Chief_ComplaintTextBox.Text
            'ds.Tables("AddressBook").Rows(inc).Item(2) = txtLast.Text
            'ds.Tables("AddressBook").Rows(inc).Item(3) = txtAddress1.Text
            'ds.Tables("AddressBook").Rows(inc).Item(4) = txtAddress2.Text
            'ds.Tables("AddressBook").Rows(inc).Item(5) = txtAddress3.Text
            'ds.Tables("AddressBook").Rows(inc).Item(6) = txtZIP.Text
            'ds.Tables("AddressBook").Rows(inc).Item(7) = txtPhone.Text
            'ds.Tables("AddressBook").Rows(inc).Item(8) = txtEmail.Text
            'ds.Tables("AddressBook").Rows(inc).Item(9) = txtNotes.Text
 
            data.Update(ds, "History")
 
            MsgBox("Data updated")
        End If
    End Sub
 
    Private Sub btnCommit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCommit.Click
        If inc <> -1 Then
 
            Dim cb As New OleDb.OleDbCommandBuilder(data)
            Dim dsNewRow As DataRow
 
            dsNewRow = ds.Tables("History").NewRow()
 
            dsNewRow.Item("Chief Complaint") = Chief_ComplaintTextBox.Text
            'dsNewRow.Item("Surname") = txtLast.Text
            'dsNewRow.Item("Address1") = txtAddress1.Text
            'dsNewRow.Item("Address2") = txtAddress2.Text
            'dsNewRow.Item("Address3") = txtAddress3.Text
            'dsNewRow.Item("Postcode") = txtZIP.Text
            'dsNewRow.Item("Phone") = txtPhone.Text
            'dsNewRow.Item("Email") = txtEmail.Text
            'dsNewRow.Item("Notes") = txtNotes.Text
 
            ds.Tables("History").Rows.Add(dsNewRow)
 
            data.Update(ds, "History")
 
            MsgBox("New Record added to the Database")
        End If
    End Sub

[stanav]

The errors are telling you exactly what the problem are. For the 1st one, you need to provide a valid update query to the UpdateCommnad object. With the 2nd error, you have to examine your insert query to see where the syntax error is and fix it.
I highly recommend you to learn more about ADO.Net. There are a lot of tutorials on that on this site as well as on the internet.

[ohok]

I also read that you can get the Syntax Error if a column in your table is a reserved word in the Jet 4.0 reserved words. One of the columns is named Date and that's a reserved word, so I wasn't sure if that was the problem for that.

I would also like to know why code that works fine in another program doesn't work in this one. I thought if I renamed things to match this program and made sure I had the row information correct that it would work. It scrolls through fine using the other code, but the save and update won't work.

I have the data.Update in there, which I thought was supposed to add those commands. As far as my insert query, I assumed that if it was pulling data from every column fine that it was set up correctly, I had it insert every column in the table.

[si_the_geek]

Reserved words do cause problems like that, so you should change the field/table names that are reserved words (in any part of the process, not just Jet).

For more information (including links to lists of reserved words), see the article What names should I NOT use for tables/fields/views/stored procedures/...? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)

[ohok]

Reserved words do cause problems like that, so you should change the field/table names that are reserved words (in any part of the process, not just Jet).

For more information (including links to lists of reserved words), see the article What names should I NOT use for tables/fields/views/stored procedures/...? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)

Thanks. Only problem is it won't let me change the name of the column because the table is linked somewhere. I'll have to figure that out.

[stanav]

Yes, that's the problem. As a rule of thumb, never name a column using a reserved key word. If you don't have any control over that (like, it's not your database or changing it will break other applications that also use that database), you wrap the column name in square bracket, something like

Select [Date], userId from tablename

A data adapter has 3 command objects: insert, update and delete. If your table has a primary key, the data adapter will try to generate the insert, update and delete queries automatically based on that primary key. It knows if a table has a primary key or not from the select query. For any reason that it cannot generate those commands automatically, you will have to provide them yourself. It's just a matter of supplying the right insert, update and delete SQL statement to the command objects.

[ohok]

Yep, I didn't create this database.

So, for this line:

  data.Update(ds, "History")

Would I have to get rid of the Update portion and declare a new variable with my custom insert, update, and delete statements so I could reference it, or is it done a different way?