Re: VS.NET 2003, ADO.NET 1.1 and Access 2000: Getting a Concurrency violation

I am using an Access update query, with parameters, for the
OleDBDataAdapter's UpdateCommand. Because there is roughly 50 parameters, I
won't list them all, but a partial listing follows:

PARAMETERS [Client ID] Text ( 255 ), [Grant ID] Text ( 255 ), [Intake ID]
Long, [Interview Date] Text ( 255 ), [Interview Type] Long, GM_1 Short, GM_2
Short, GM_3 Short, GM_4 Short, GM_5
.... (several more like this)
GS_33 Short, GS_34 Short, GS_35 Short, GS_36 Short;
UPDATE ATR_NM_0009_200604140933_CR_00001_RCV SET
ATR_NM_0009_200604140933_CR_00001_RCV.GM_1 = [GM_1],
ATR_NM_0009_200604140933_CR_00001_RCV.GM_2 = [GM_2],
ATR_NM_0009_200604140933_CR_00001_RCV.GM_3 = [GM_3],
ATR_NM_0009_200604140933_CR_00001_RCV.GM_4 = [GM_4],
ATR_NM_0009_200604140933_CR_00001_RCV.GM_5 = [GM_5],
.... (several more like this)
ATR_NM_0009_200604140933_CR_00001_RCV.GS_33 = [GS_33],
ATR_NM_0009_200604140933_CR_00001_RCV.GS_34 = [GS_34],
ATR_NM_0009_200604140933_CR_00001_RCV.GS_35 = [GS_35],
ATR_NM_0009_200604140933_CR_00001_RCV.GS_36 = [GS_36]
WHERE (((ATR_NM_0009_200604140933_CR_00001_RCV.[Client ID])=[Client ID]) AND
((ATR_NM_0009_200604140933_CR_00001_RCV.[Grant ID])=[Grant ID]) AND
((ATR_NM_0009_200604140933_CR_00001_RCV.[Intake ID])=[Intake ID]) AND
((ATR_NM_0009_200604140933_CR_00001_RCV.[Interview Date])=[Interview Date])
AND ((ATR_NM_0009_200604140933_CR_00001_RCV.[Interview Type])=[Interview
Type]));

Then, a partial listing of the C# code which creates the UpdateCommand
follows:

OleDbCommand cmd = new OleDbCommand("[Update GM and GS]", oleDbConn);

cmd.CommandType = CommandType.StoredProcedure;

OleDbParameterCollection pc = cmd.Parameters;

OleDbParameter param;

//WHERE parameters

param = pc.Add("Client ID", OleDbType.VarChar, 255, "Client ID");

param.SourceVersion = DataRowVersion.Original;

param = pc.Add("Grant ID", OleDbType.VarChar, 255, "Grant ID");

param.SourceVersion = DataRowVersion.Original;

param = pc.Add("Intake ID", OleDbType.Integer, 0, "Intake ID");

param.SourceVersion = DataRowVersion.Original;

param = pc.Add("Interview Date", OleDbType.VarChar, 255, "Interview Date");

param.SourceVersion = DataRowVersion.Original;

param = pc.Add("Interview Type", OleDbType.Integer, 0, "Interview Type");

param.SourceVersion = DataRowVersion.Original;

//new values for parameters

//GM section

pc.Add("GM_1", OleDbType.SmallInt, 0, "GM_1");

pc.Add("GM_2", OleDbType.SmallInt, 0, "GM_2");

pc.Add("GM_3", OleDbType.SmallInt, 0, "GM_3");

pc.Add("GM_4", OleDbType.SmallInt, 0, "GM_4");

pc.Add("GM_5", OleDbType.SmallInt, 0, "GM_5");

//several more like this...

I'd appreciate whatever help/insight you can give.

Rod


"Matt Noonan" <noreply@xxxxxxxxxxx> wrote in message
news:uSDZdSWdGHA.4532@xxxxxxxxxxxxxxxxxxxxxxx
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




.

Relevant Pages

  • Re: upsizing to sql 2005
    ... which include MS SQL Server linked table. ... Query Name: Arcadia - ARC ... are still unable to upsize one of these queries, ...
    (microsoft.public.access.queries)
  • Re: upsizing to sql 2005
    ... A query in Access-Jet will be started and under the supervision of Jet, ... Jet syntax parsing and evaluation. ... so, in this case, into MS SQL Server. ...
    (microsoft.public.access.queries)
  • [REVS] SQL Injection Walkthrough
    ... problems facing them while trying to utilize SQL Injection techniques, ... Many web pages take parameters from web user, and make SQL query to ... MS SQL server ignore the rest of the query, which will get rid of the last ... Microsoft OLE DB Provider for ODBC Drivers error '80040e07' ...
    (Securiteam)
  • Re: Asynchronous Stored Procedure Never Returns - Help?
    ... If you have the Sql Server 2000 or 2005 docs they are thorough and can be ... for Transaction SQL Reference from the drop-down or select a keyword from ... your query in Query Analyzer or Sql Server Management Studio, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Subtracting date and time values
    ... >original query that my client was going to use. ... >>retrieve the previous record, ... >>Please provide the SQL for a query that provides ... >>Tom Ellison ...
    (microsoft.public.access.queries)