Why won't this update my database?

Hi Experts,
I'm trying to update a record in a table in an Access database. My code doesn't throw me any error messages and stepping through the code I can see the value of the field gets changed in the datarow. But somehow it doesn't get saved in the database itself. What am I doing wrong?
thanks,
Michiel
Dim _dbCon As New OleDb.OleDbConnection
        Dim _dataSet As New DataSet
        Dim _dataAdap As OleDb.OleDbDataAdapter
        Dim _command As String
        Dim _row As DataRow

        Try
            _dbCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\michiel\Desktop\2011\Test.mdb"
            _dbCon.Open()

            _command = "SELECT * FROM tbl_Table WHERE RecID=1"
            _dataAdap = New OleDb.OleDbDataAdapter(_command, _dbCon)
            _dataAdap.Fill(_dataSet, "tbl_Table")
            _row = _dataSet.Tables("tbl_Table").Rows(0)

            _row("Field1") = "Modiefied!"
            _row.AcceptChanges()
            _dataAdap.Update(_dataSet, "tbl_Table")
            _dbCon.Close()
        Catch _ex As Exception
            MessageBox.Show(_ex.Message)
        End Try

Open in new window

altiplanoAsked:
Who is Participating?
 
lucius_theCommented:
The statement creates the command objects, that your dataadapter needs to update the datatable to the DB backend. You can either write them manually or use the commandbuilder which does it automatically.
0
 
CodeCruiserCommented:
Remove the

 _row.AcceptChanges()
0
 
CodeCruiserCommented:
I hope this is just for testing code as other wise its a terrible way to update a record.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
altiplanoAuthor Commented:
Thanks for the very quick reply! Removing the line as you suggested throws me the following error:
"Update requires a valid UpdateCommand when passed DataRow collection with modified rows."

Yes, this is for testing purposes. In the real application I have to update 25 fields and I just don't like to build such a long SQL-string. But if you have a cleaner method I would be very interested.
regards,
Michiel
0
 
lucius_theCommented:
you also need to create an oledbcommandbuilder:

dim cb as new oledbcommandbuilder (dataAdap)

before the line:
_dataAdap.Update(_dataSet, "tbl_Table")
0
 
CodeCruiserCommented:
You would need to build the sql. But to get the above code to work, make sure you have a primary key field in your select statement.
0
 
altiplanoAuthor Commented:
Hi Lucius,
At first your suggestion did not work, but then, on a hunch, I compacted the database and all of a sudden it worked. I really don't have a clue what this statement does, but it seems to be necessary.
thanks,
Michiel
0
 
altiplanoAuthor Commented:
Hi Codecruiser,

I agree with you that this is a very convoluted way of updating racords. Could you please show me what would be the most efficient way of achieving the following?

1. Check if a record exist by searching for the key in field RecID
2. If the record doesn't exist, create a new one and add 25 values to it
3. If the record exists, update the same 25 fields with new values.

(If you feel this requires a separate question, than that's fine with me, the points for this question go to Lucious).

regards,
Michiel
0
 
altiplanoAuthor Commented:
Hello gentlemen,
I created a separate question for the above: Q_27483388. I would greatly apreciate your help.
regards,
Michiel
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.