DataAdapter Fails to Update Access 2002 database

  • Thread starter Thread starter v.maggs
  • Start date Start date
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)
 
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
 
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."
 
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."
 
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...
 
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...
 
Back
Top