DataAdapter problem (UpdateCommand, DeleteCommand)

[Sébastien]

Here is my code :
oldOleDbDataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM " &
strTable, strConnection)
oldOleDbCommandBuilder = New
OleDb.OleDbCommandBuilder(oldOleDbDataAdapter)

oldOleDbDataAdapter.Update(dtsDataSet, strTable)
dtsDataSet.Tables.Item(strTable).AcceptChanges

That code work perfectly when I add a new row (in ALL cases). But if I start
my software with no data in the strTable I want to update, and I add a new
row in that strTable, then I update it, it throw an exception (Concurrency
Exception 0 rows affected). Same thing if I start my software with an empty
strTable, I add a new row in strTable then I delete it, I get the same
error. BUT, if when I start the software with 1 or more rows in strTable, it
don't throw me any exception whatever I do.

That is a strange case. The only way I found to solve it is to create a
DataAdapter using the Wizard (so I drag and drop the OleDbDataAdapter from
the Toolbox on my form). Then in my code, I add something like :

oldOleDbDataAdapter.UpdateCommand = DataAdapterOnTheForm.UpdateCommand

I don't need to change the Insert statement created with the CommandBuilder.

Does anybody have an idea about this problem ?

Thank you for your help.

 

[Miha Markic]

Hi Sébastien,

To begin with, I would avoid using command builder.
Rather build the dataadapter at design time (you might drag&drop the table
from server explorer).

 

[Sébastien]

It is kinda stupid to do that in my case since I dunno what database I'll
connect on, what will be the table in the database I want to connect to and
a lot of other factor. I have a generic class that construct a Dataset with
relations and all the stuff we need in a dataset... it work from any source
using some standard in the Database. So as I want a software who connect on
any database, I can't just drag and drop the DataAdapter.... I guess if the
Drag and Drop work fine, I could do it in code also (since code give more
control about what we do).

Thank you for your answer, but if you can help me to find what is wrong with
the coded version of my DataAdapter, it would help me better.

 

[David Sceppa]

Concurrency exceptions occur when the "original" values in
the DataRow used in the update attempt don't match the current
values in the database row.

This problem generally occurs in the situation you described
if the server is generating new data for the row (auto-increment,
default, timestamp values) that you're not retrieving after
submitting the initial update attempt.

CommandBuilders do not provide a way to fetch new server
generated values after successful inserts or updates. The
DataAdapter Configuration Wizard, does add such logic for SQL
Server databases. That's likely the cause for the difference in
behavior.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.

 

[Miha Markic [MVP C#]]

Hi,

I think David figured it out.
Did he?