ADO Update Problem

[Chris Nash]

Greetings all,

I am using an Access Database to store question information for a
questionare application. I am using VB.net as the front end, and
ADO.net to retrieve the information from the database. My data is bring
displayed in a DataGrid bound to a dataset. All of my calls to the
database are to parameter queries.

The problem that I am having is with the update command. When I call
the update method for my data adapter, and pass a dataset containing
only the changed records, only one record shows the change. I know that
parameter queries work because data is being returned and update, just
not in the quantities that I want.

From what I have read, the update method in ADO.NET should be able to
process a batch change, but the only specific information I have been
able to find concerns SQL Server and not MS Access. The only
information I have been able to find regarding the update method and MS
Access were for single records.

Does ADO.net send each update request separately, waiting for
conformations between requests, or does it send all of the changes at
once? I know, if you write the queries properly, SQL server can process
the data in a lump sum, but I do not believe Access has that ability.

I have been working on this for several days. Any help you can provide
would be greatly appreciated


Thanks in advance,
Edison

 

[William Ryan eMVP]

--

Greetings all,

I am using an Access Database to store question information for a
questionare application. I am using VB.net as the front end, and
ADO.net to retrieve the information from the database. My data is bring
displayed in a DataGrid bound to a dataset. All of my calls to the
database are to parameter queries.

The problem that I am having is with the update command. When I call
the update method for my data adapter, and pass a dataset containing
only the changed records, only one record shows the change.

Where? Have you used GetChanges ? If so, what does the DataTable's
Rows.Count property say on the chagnes datatable? If you can update one
record then everything apparently is working. The only thing I can think of
is that the other rows are still being 'edited' or you are trapping the
update, it's throwing an exception and you are eating it w/ no notification.
Also, have you changed ContinueUpdateOnError?

I know that

parameter queries work because data is being returned and update, just
not in the quantities that I want.

Ok, but how have you verfied what they should be?

From what I have read, the update method in ADO.NET should be able to
process a batch change, but the only specific information I have been
able to find concerns SQL Server and not MS Access. The only
information I have been able to find regarding the update method and MS
Access were for single records.

The adapter loops through the datatable and processes the records one at a
time. In ADO.NET 2.0 batch updates will be supported as long as your DB
supports them, which access doesn't.

Does ADO.net send each update request separately, waiting for
conformations between requests,

Yes
or does it send all of the changes at

once?

No
I know, if you write the queries properly, SQL server can process

the data in a lump sum, but I do not believe Access has that ability. That's true

I have been working on this for several days. Any help you can provide
would be greatly appreciated

Posting the code would be helpful. and use some assertions so you verify
the counts of rows, Debug.Assert(tblChanges.Rows.count =1, "More than one
Row or 0 Rows Modified")

Then check you exception handlers, eating an exception is a prime symptom of
this problem.

Let me know, hopefully post the code and we'll get you through it.

HTH,

Bill

Thanks in advance,
Edison

W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/