DataAdapter Fails to Update Access 2002 database

V

v.maggs

In Visual Studio 2005, I have a snippet of VB 2005 code which runs
without error but does not update the Access 2002 database. Any ideas?




Dim strSQL As String = "SELECT [Batch.Comments], [Batch.Is Active],
[Batch.Is Pre Entered] FROM BATCH WHERE [Batch.Batch Key] = 4643"

Dim strBatchComments As String = "WL 939, GFPC: Sr-90, Vam sr-90"

Dim da As New OleDbDataAdapter(strSQL, My.Settings.Oxford_1)

Dim dbtbl As New DataTable()

da.Fill(dbtbl)
dbtbl.Rows(0).Item(0) = strBatchComments
dbtbl.Rows(0).Item(1) = True
dbtbl.Rows(0).Item(2) = True

dbtbl.AcceptChanges()
da.Update(dbtbl)
 
R

Robert Simpson

In Visual Studio 2005, I have a snippet of VB 2005 code which runs
without error but does not update the Access 2002 database. Any ideas?




Dim strSQL As String = "SELECT [Batch.Comments], [Batch.Is Active],
[Batch.Is Pre Entered] FROM BATCH WHERE [Batch.Batch Key] = 4643"

Dim strBatchComments As String = "WL 939, GFPC: Sr-90, Vam sr-90"

Dim da As New OleDbDataAdapter(strSQL, My.Settings.Oxford_1)

Dim dbtbl As New DataTable()

da.Fill(dbtbl)
dbtbl.Rows(0).Item(0) = strBatchComments
dbtbl.Rows(0).Item(1) = True
dbtbl.Rows(0).Item(2) = True

dbtbl.AcceptChanges()
da.Update(dbtbl)
Click to expand...

Remove the dbtbl.AcceptChanges() line. da.Update() needs to know whats
changed in order to do the update, and AcceptChanges() is screwing you up.
da.Update() will take care of accepting the changes once the update is
finished.

Robert
 
E

evint

Robert said:
In Visual Studio 2005, I have a snippet of VB 2005 code which runs
without error but does not update the Access 2002 database. Any ideas?




Dim strSQL As String = "SELECT [Batch.Comments], [Batch.Is Active],
[Batch.Is Pre Entered] FROM BATCH WHERE [Batch.Batch Key] = 4643"

Dim strBatchComments As String = "WL 939, GFPC: Sr-90, Vam sr-90"

Dim da As New OleDbDataAdapter(strSQL, My.Settings.Oxford_1)

Dim dbtbl As New DataTable()

da.Fill(dbtbl)
dbtbl.Rows(0).Item(0) = strBatchComments
dbtbl.Rows(0).Item(1) = True
dbtbl.Rows(0).Item(2) = True

dbtbl.AcceptChanges()
da.Update(dbtbl)
Click to expand...

Remove the dbtbl.AcceptChanges() line. da.Update() needs to know whats
changed in order to do the update, and AcceptChanges() is screwing you up.
da.Update() will take care of accepting the changes once the update is
finished.

Robert
Click to expand...


Thanks for the thought, Robert. Commenting out the line you suggested
causes a new error: "Update requires a valid UpdateCommand when passed
DataRow collection with modified rows."
 
E

evint

Robert said:
In Visual Studio 2005, I have a snippet of VB 2005 code which runs
without error but does not update the Access 2002 database. Any ideas?




Dim strSQL As String = "SELECT [Batch.Comments], [Batch.Is Active],
[Batch.Is Pre Entered] FROM BATCH WHERE [Batch.Batch Key] = 4643"

Dim strBatchComments As String = "WL 939, GFPC: Sr-90, Vam sr-90"

Dim da As New OleDbDataAdapter(strSQL, My.Settings.Oxford_1)

Dim dbtbl As New DataTable()

da.Fill(dbtbl)
dbtbl.Rows(0).Item(0) = strBatchComments
dbtbl.Rows(0).Item(1) = True
dbtbl.Rows(0).Item(2) = True

dbtbl.AcceptChanges()
da.Update(dbtbl)
Click to expand...

Remove the dbtbl.AcceptChanges() line. da.Update() needs to know whats
changed in order to do the update, and AcceptChanges() is screwing you up.
da.Update() will take care of accepting the changes once the update is
finished.

Robert
Click to expand...


Thanks for the thought, Robert. Commenting out the line you suggested
causes a new error: "Update requires a valid UpdateCommand when passed
DataRow collection with modified rows."
 
R

Robert Simpson

Well, ya -- I assumed you have a commandbuilder and its been assigned a data
adapter. Otherwise you will need to hand-craft an UpdateCommand and assign
it to the DataAdapter yourself.

evint said:
Robert said:
In Visual Studio 2005, I have a snippet of VB 2005 code which runs
without error but does not update the Access 2002 database. Any ideas?




Dim strSQL As String = "SELECT [Batch.Comments], [Batch.Is Active],
[Batch.Is Pre Entered] FROM BATCH WHERE [Batch.Batch Key] = 4643"

Dim strBatchComments As String = "WL 939, GFPC: Sr-90, Vam sr-90"

Dim da As New OleDbDataAdapter(strSQL, My.Settings.Oxford_1)

Dim dbtbl As New DataTable()

da.Fill(dbtbl)
dbtbl.Rows(0).Item(0) = strBatchComments
dbtbl.Rows(0).Item(1) = True
dbtbl.Rows(0).Item(2) = True

dbtbl.AcceptChanges()
da.Update(dbtbl)
Click to expand...

Remove the dbtbl.AcceptChanges() line. da.Update() needs to know whats
changed in order to do the update, and AcceptChanges() is screwing you
up.
da.Update() will take care of accepting the changes once the update is
finished.

Robert
Click to expand...


Thanks for the thought, Robert. Commenting out the line you suggested
causes a new error: "Update requires a valid UpdateCommand when passed
DataRow collection with modified rows."
Click to expand...
 
C

Cor Ligthert [MVP]

Evint,

Removing accept changes that line means that there stay rows to be updated.
With that line all accept changes line all datarows will be set to not
changed.
And therefore that next error will not be showed.

Therefore you need update commands in your dataaadapter.

The most easy for such a simple program as you show is the commandbuilder as
Robert wrote already.

In this case to test you can set it before the update.
dim cmb as new OleDb.OleDbCommandbuilder(da)

I hope this helps,

Cor



evint said:
Robert said:
In Visual Studio 2005, I have a snippet of VB 2005 code which runs
without error but does not update the Access 2002 database. Any ideas?




Dim strSQL As String = "SELECT [Batch.Comments], [Batch.Is Active],
[Batch.Is Pre Entered] FROM BATCH WHERE [Batch.Batch Key] = 4643"

Dim strBatchComments As String = "WL 939, GFPC: Sr-90, Vam sr-90"

Dim da As New OleDbDataAdapter(strSQL, My.Settings.Oxford_1)

Dim dbtbl As New DataTable()

da.Fill(dbtbl)
dbtbl.Rows(0).Item(0) = strBatchComments
dbtbl.Rows(0).Item(1) = True
dbtbl.Rows(0).Item(2) = True

dbtbl.AcceptChanges()
da.Update(dbtbl)
Click to expand...

Remove the dbtbl.AcceptChanges() line. da.Update() needs to know whats
changed in order to do the update, and AcceptChanges() is screwing you
up.
da.Update() will take care of accepting the changes once the update is
finished.

Robert
Click to expand...


Thanks for the thought, Robert. Commenting out the line you suggested
causes a new error: "Update requires a valid UpdateCommand when passed
DataRow collection with modified rows."
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Have you used Microsoft's ADO.NET DataAdapter Builder? 1
Urgent ! Can't update the database by using DataAdapter 1
DataAdapter,SQL Sp's and update 2
DataAdapter fails to update the server database 9
Error on adding a record to an Access Database. 13
Database Update 3
problem adding new record to access database table 2
Update to Visual FoxPro 6 Table is failing 1

Top