Concurrency control in updating Access records?

  • Thread starter Thread starter pedestrian
  • Start date Start date
P

pedestrian

I'm using OleDbDataAdapter to update records to the Access database.
Refer to the "Introduction to Data Concurrency in ADO.NET" topic in MSDN:
"The Saving-All-Values Approach" section

ms-help://MS.MSDNQTR.v80.en/MS.MSDN.v80/MS.VisualStudio.v80.
en/dv_raddata/html/d5293098-4a88-4110-abd2-34d9e6661664.htm

The Update command text is in the following format:
UPDATE Customers SET CustomerID = @currCustomerID,
CompanyName = @currCompanyName, .......
WHERE (CustomerID = @origCustomerID) AND .....
(CompanyName = @origCompanyName OR @origCompanyName IS NULL AND
CompanyName IS NULL) ....... ;
SELECT CustomerID, ......
FROM Customers WHERE (CustomerID = @currCustomerID)

Regarding the named parameter @origCompanyName, there are two occurrences in
the statement (similarly for all other columns that allow null). However,
since OleDb use question marks (?) as parameter placeholders, adding
parameters for the above equivalent of OleDbDataAdapter.UpdateCommand would
logically cause the parameter mis-position problem (unless we are allowed to
add the same parameter twice gracefully).

It seems there's no way to correctly add the same parameters or is there any
way to achieve that?

Thanks for your help...
 
¤ I'm using OleDbDataAdapter to update records to the Access database.
¤ Refer to the "Introduction to Data Concurrency in ADO.NET" topic in MSDN:
¤ "The Saving-All-Values Approach" section
¤
¤ ms-help://MS.MSDNQTR.v80.en/MS.MSDN.v80/MS.VisualStudio.v80.
¤ en/dv_raddata/html/d5293098-4a88-4110-abd2-34d9e6661664.htm
¤
¤ The Update command text is in the following format:
¤ UPDATE Customers SET CustomerID = @currCustomerID,
¤ CompanyName = @currCompanyName, .......
¤ WHERE (CustomerID = @origCustomerID) AND .....
¤ (CompanyName = @origCompanyName OR @origCompanyName IS NULL AND
¤ CompanyName IS NULL) ....... ;
¤ SELECT CustomerID, ......
¤ FROM Customers WHERE (CustomerID = @currCustomerID)
¤
¤ Regarding the named parameter @origCompanyName, there are two occurrences in
¤ the statement (similarly for all other columns that allow null). However,
¤ since OleDb use question marks (?) as parameter placeholders, adding
¤ parameters for the above equivalent of OleDbDataAdapter.UpdateCommand would
¤ logically cause the parameter mis-position problem (unless we are allowed to
¤ add the same parameter twice gracefully).
¤
¤ It seems there's no way to correctly add the same parameters or is there any
¤ way to achieve that?

No. As you have determined, named parameters for OLEDB under ADO.NET are not supported so there
needs to be a parameter that maps to each placeholder in the SQL string.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Thanks Paul.
I wonder is there an alternative for concurrency control in updating Access
database.

Paul said:
¤ I'm using OleDbDataAdapter to update records to the Access database.
¤ Refer to the "Introduction to Data Concurrency in ADO.NET" topic in MSDN:
¤ "The Saving-All-Values Approach" section
¤
¤ ms-help://MS.MSDNQTR.v80.en/MS.MSDN.v80/MS.VisualStudio.v80.
¤ en/dv_raddata/html/d5293098-4a88-4110-abd2-34d9e6661664.htm
¤
¤ The Update command text is in the following format:
¤ UPDATE Customers SET CustomerID = @currCustomerID,
¤ CompanyName = @currCompanyName, .......
¤ WHERE (CustomerID = @origCustomerID) AND .....
¤ (CompanyName = @origCompanyName OR @origCompanyName IS NULL AND
¤ CompanyName IS NULL) ....... ;
¤ SELECT CustomerID, ......
¤ FROM Customers WHERE (CustomerID = @currCustomerID)
¤
¤ Regarding the named parameter @origCompanyName, there are two occurrences in
¤ the statement (similarly for all other columns that allow null). However,
¤ since OleDb use question marks (?) as parameter placeholders, adding
¤ parameters for the above equivalent of OleDbDataAdapter.UpdateCommand would
¤ logically cause the parameter mis-position problem (unless we are allowed to
¤ add the same parameter twice gracefully).
¤
¤ It seems there's no way to correctly add the same parameters or is there any
¤ way to achieve that?

No. As you have determined, named parameters for OLEDB under ADO.NET are not supported so there
needs to be a parameter that maps to each placeholder in the SQL string.

Paul
~~~~
Microsoft MVP (Visual Basic)
Click to expand...

--
Regards,
Pedestrian, Penang.

Message posted via DotNetMonster.com
http://www.dotnetmonster.com/Uwe/Forums.aspx/dotnet-ado-net/200705/1
 
¤ Thanks Paul.
¤ I wonder is there an alternative for concurrency control in updating Access
¤ database.
¤

You would have to code your own, as suggested by the article. The default under ADO.NET is
"optimistic" so the row (or page) is only locked at the time of update. Another user could
potentially overwrite the updated information if they have not refreshed their data since the last
update (last in wins scenario).


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Thanks, Paul.

Paul said:
¤ Thanks Paul.
¤ I wonder is there an alternative for concurrency control in updating Access
¤ database.
¤

You would have to code your own, as suggested by the article. The default under ADO.NET is
"optimistic" so the row (or page) is only locked at the time of update. Another user could
potentially overwrite the updated information if they have not refreshed their data since the last
update (last in wins scenario).

Paul
~~~~
Microsoft MVP (Visual Basic)
Click to expand...
 
Back
Top