Dataadabter update command throws error

[William LaMartin]

In the past, I have filled a datagrid with data from an Access database
table using a data adapter and dataset, where I used the OleDbCommandBuilder
to have the program create the insert, delete and update commands
automatically for the dataadapter. I think those database tables had no
more than 10 or 12 fields. Then to save the changes in the datagrid to the
database, I simply evoked the dataadapter update command. No problems.

Yesterday, I tried the same scenario with a database someone sent me and had
no luck. When I should try to save the data after changes had been made in
the datagrid using the dataadapter update command, I got the following
error:

Update requires a valid UpdateCommand when passed DataRow collection with
modified rows.

Short of trying to write my own update command, I can find no way of
correcting this. By the way, this new database table has 22 fields. This
makes me wonder if the OleDbCommandBuilder goes flakey when you have more
than just a few fields?

Also, has anyone else encountered this and have a solution?

To get a program that would do what I needed, I added a reference to ADODB
to the project and used that to do my save with no problems. But I would
like to use the simpler solution of the dataadapter update.

 

[Cor Ligthert [MVP]]

William,

All datawizards in Visual StudioNet (I am not sure for version 2005) go
until 100 datafields in a database. Therefore your commandbuilder should
normally work. (All wizards as the commandbuilder can only create straight
single table update, insert and deletecommands)

If you cannot find it, show than some code around that commandbuilder.
(first copy it in a notebook, than back in the message or use the program
from Armin Zingler)

http://people.freenet.de/armin.zingler/ClipboardPlainText.zip

I hope this helps,

Cor

 

[William LaMartin]

Everything is done in code, so I see no way to get any text associated with
the dataadapter update command since it doesn't know that fields it will
have to deal with until I run the code and the command builder does its
thing.

Here is the code that loads the database and fills a data set, ds2, with the
data from the table Main which I then use as the datasource for a datagrid.
The datagrid displays the data fine. But as mentioned in the previous
message, the dataadapter cannot update the database because it claims there
is no update command.


dc.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
"AddressData.mdb"
dc.Open()
Dim StrSQL As String = "SELECT * from Main"
Dim objCommand As New Data.OleDb.OleDbCommand(StrSQL, dc)
Dim da As New Data.OleDb.OleDbDataAdapter
da.Fill(ds2, "Main")
Dim custCB As Data.OleDb.OleDbCommandBuilder = New
Data.OleDb.OleDbCommandBuilder(da)
custCB.QuotePrefix = "["
custCB.QuoteSuffix = "]"

 

[Cor Ligthert [MVP]]

William,

This is correct except if the dataadapter.update is in another method.

(The dataadapter has now only scoop inside the method and will be given to
the GC to release at the end of the method)

Therefore where do you have this and where do you have the update located?

Cor

Everything is done in code, so I see no way to get any text associated
with the dataadapter update command since it doesn't know that fields it
will have to deal with until I run the code and the command builder does
its thing.

Here is the code that loads the database and fills a data set, ds2, with
the data from the table Main which I then use as the datasource for a
datagrid. The datagrid displays the data fine. But as mentioned in the
previous message, the dataadapter cannot update the database because it
claims there is no update command.


dc.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
"AddressData.mdb"
dc.Open()
Dim StrSQL As String = "SELECT * from Main"
Dim objCommand As New Data.OleDb.OleDbCommand(StrSQL, dc)
Dim da As New Data.OleDb.OleDbDataAdapter
da.Fill(ds2, "Main")
Dim custCB As Data.OleDb.OleDbCommandBuilder = New
Data.OleDb.OleDbCommandBuilder(da)
custCB.QuotePrefix = "["
custCB.QuoteSuffix = "]"

William,

All datawizards in Visual StudioNet (I am not sure for version 2005) go
until 100 datafields in a database. Therefore your commandbuilder should
normally work. (All wizards as the commandbuilder can only create
straight single table update, insert and deletecommands)

If you cannot find it, show than some code around that commandbuilder.
(first copy it in a notebook, than back in the message or use the program
from Armin Zingler)

http://people.freenet.de/armin.zingler/ClipboardPlainText.zip

I hope this helps,

Cor

 

[William LaMartin]

You hit the nail on the head. Thanks very much.

I had given the dataadapter scope of the entire form (Protected da As New
Data.oledb.OleDbDataAdapter at the top of the form). Then without realizing
it, I placed the code "Dim da As New Data.oledb.OleDbDataAdapter" in the
button_click event for loading the data. Once I removed that redundant
code, everything worked fine. I must have looked at that code for hours in
various permutations without noticing the problem

Thanks again

William,

This is correct except if the dataadapter.update is in another method.

(The dataadapter has now only scoop inside the method and will be given to
the GC to release at the end of the method)

Therefore where do you have this and where do you have the update located?

Cor

Everything is done in code, so I see no way to get any text associated
with the dataadapter update command since it doesn't know that fields it
will have to deal with until I run the code and the command builder does
its thing.

Here is the code that loads the database and fills a data set, ds2, with
the data from the table Main which I then use as the datasource for a
datagrid. The datagrid displays the data fine. But as mentioned in the
previous message, the dataadapter cannot update the database because it
claims there is no update command.


dc.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
"AddressData.mdb"
dc.Open()
Dim StrSQL As String = "SELECT * from Main"
Dim objCommand As New Data.OleDb.OleDbCommand(StrSQL, dc)
Dim da As New Data.OleDb.OleDbDataAdapter
da.Fill(ds2, "Main")
Dim custCB As Data.OleDb.OleDbCommandBuilder = New
Data.OleDb.OleDbCommandBuilder(da)
custCB.QuotePrefix = "["
custCB.QuoteSuffix = "]"

William,

All datawizards in Visual StudioNet (I am not sure for version 2005) go
until 100 datafields in a database. Therefore your commandbuilder should
normally work. (All wizards as the commandbuilder can only create
straight single table update, insert and deletecommands)

If you cannot find it, show than some code around that commandbuilder.
(first copy it in a notebook, than back in the message or use the
program from Armin Zingler)

http://people.freenet.de/armin.zingler/ClipboardPlainText.zip

I hope this helps,

Cor