.Net ramblings
# Saturday, 03 April 2004
ADO.NET parameter quirks with ODBC and OleDb

The OleDb Provider

If anyone is using the Oledb provider with .Net, when you are adding parameters to an OleDbCommand in code, make sure you add them in the order that they belong in the sql string. 

For example, if your sql string is:
"insert into table1 (username, password) values(?,?)"
and you have parameters added to the command (usually done through a configure OleDbDataAdapter wizard), called "@Username" and "@Password".  Your code must add the parameters in the correct order as follows:
this.oledbCommand1.Parameters("@Username").Value = username;
this.oledbCommand1.Parameters("@Password").Value = password;

If you don't do it this way, you may get a jet error: "No value given for one or more required parameters.". 

This quirk does not apply for the Sql provider in ADO.NET, because you can name the parameters in the sql string, e.g. 
"insert into table1 (username, password) values(@Username, @Password)"

The ODBC Provider

If you're using the ODBC provider with .NET (probably for MySQL), there is one oddity that you should know about too.  In sql strings for odbcCommand objects, you can't use the @ParamName syntax.  You have to use question marks as placeholders for the parameters as follows:
"insert into table1 (username, password) values(?,?)"
Similarly, you have to add the parameters in the right order. 

Saturday, 03 April 2004 18:07:08 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  Database