UPDATE problems with Parameters

[MW]

Hello,

I have a problem with parameters in OleDbCommand in Access 2000 database.
For example this code works and updates the row:
OleDbCommand comm = new OleDbCommand("UPDATE DataTable SET ContactID =
'Marcin', Test = 'just a test' WHERE RowID = @RowID ;",
this.databaseConnection);

comm.Parameters.Add("@RowID", OleDbType.Integer);

comm.Parameters["@RowID"].Value = 14;



But if I just change immediate substitution from ContactID to a parameter,
nothing works. Does anyone have any idea why it happens this way ?

OleDbCommand comm = new OleDbCommand("UPDATE DataTable SET ContactID =
@ContactID, Test = 'just a test' WHERE RowID = @RowID ;",
this.databaseConnection);

comm.Parameters.Add("@RowID", OleDbType.Integer);

comm.Parameters.Add("@ContactID", OleDbType.VarWChar, 50);

comm.Parameters["@RowID"].Value = 14;

comm.Parameters["@ContactID"].Value = "Marcin Mastah";



Above code, strangly doesn't work. Any ideas why?

Thanks a lot for all the help

 

[Rogas69]

try to change order in which you add parameters to

comm.Parameters.Add("@ContactID", OleDbType.VarWChar, 50);
comm.Parameters.Add("@RowID", OleDbType.Integer);

HTH

Peter

 

[MW]

It work! It is very strange BTW, but what can we do

Dzieki stary za pomoc, jestes w tym naprawde dobry.

 

[Norman Yuan]

It is not stronge, it is the way OleDb namespace works: parameter name is
not used, instead, parameter's value is read in the sequence when it is
added to Parameters collection.

There was discussion on this topic in this group. Since most ADO.NET
books/examples use SQL Server, this issue is rarely mentioned. Oddly enough,
many new programmers starts .NET using Access, because it is easier to get
satred than SQL Server/MSDE, and they (including me several years ago) run
into this issue and cannot figure out what is wrong.

 

[Rogas69]

Yes, the problem is that this information seems to be omitted by help, at
least it is not emphasized. I found this page which has a tiny statement
about order of oledb parameters
http://msdn.microsoft.com/library/d...tml/cpconusingstoredprocedureswithcommand.asp

" When using parameters with an OleDbCommand or OdbcCommand, the order of
the parameters added to the Parameters collection must match the order of
the parameters defined in your stored procedure. "

Peter