Re: VS.NET 2003, ADO.NET 1.1 and Access 2000: Getting a Concurrency violation
- From: "Matt Noonan" <noreply@xxxxxxxxxxx>
- Date: Thu, 11 May 2006 21:37:20 -0400
Rod wrote:
I have written a Visual Studio .NET 2003 app, using ADO.NET to
retrieve several hundred records out of an Access 2000 database, and
putting them all into a DataSet. Then my app goes against a SQL
Server 2000 database, retrieves other data and updates about 500
records in the DataSet. I am only interested in updating about 50 of
the columns in each row (there are about 150 columns in the table), I
decided to write my own update command and associate it with the
OleDbDataAdapter that I use to retrieve the original table from the
Access database. And I also thought I would determine how many
records there are to update, before I retrieve anything from SQL
Server.
I have seen this error when using Access because the OleDbDataAdapter treats
parameter queries differently than the SqlDataAdapter. If you are writing
your UPDATE queries like this (based on SQL Server constructs):
UPDATE [table] SET [col1] = @col1, [col2] = @col2 WHERE [pk1] = @pk1 AND
[pk2] = @pk2
You have to be careful, because although the SQL driver will correctly
interpret the parameter names (@col1), the OleDb driver will not. To OleDb,
the query looks like this:
UPDATE [table] SET [col1] = ?, [col2] = ? WHERE [pk1] = ? AND [pk2] = ?
So your named parameters for SQL Server suddenly become anonymous
placeholders for Access. So when sending this type of query to Access, the
order in which you add the parameters to the Command object becomes more
important than the parameter name.
If you add the parameter for @pk2 before the parameter for @pk1, then you
can end up with the concurrency error because the values are switched for
the two columns. Note that you can still give the parameters names, but the
OleDb provider will simply ignore them.
--
Matt Noonan
EasyObjects.NET: The O/RM for the Enterprise Library
http://www.easyobjects.net
.
- Follow-Ups:
- References:
- Prev by Date: Re: Old topic scratched again -- "already an open DataReader"
- Next by Date: Re: .NET Transactions across multiple business Objects
- Previous by thread: VS.NET 2003, ADO.NET 1.1 and Access 2000: Getting a Concurrency violation
- Next by thread: Re: VS.NET 2003, ADO.NET 1.1 and Access 2000: Getting a Concurrency violation
- Index(es):
Relevant Pages
|
|
