parameterized queries in C# - Must declare the variable

[Wajih-ur-Rehman]

I am using the following query:
query = "insert into SystemEventsProperties (ID,PName,PValue) values
(@id,@pName,@pValue)";

OdbcParameter param1 = new OdbcParameter(); param1.DbType =
DbType.Int32;

param1.ParameterName = "@id"; param1.Value = maxId;



OdbcParameter param2 = new OdbcParameter(); param2.DbType =
DbType.String;

param2.ParameterName = "@pName"; param2.Value = "abc" ;



OdbcParameter param3 = new OdbcParameter(); param3.DbType =
DbType.String;

param3.ParameterName = "@pValue"; param3.Value = "def";



cmd.Parameters.Add(param1); cmd.Parameters.Add(param2);
cmd.Parameters.Add(param3);



cmd.CommandText = query;

cmd.ExecuteNonQuery(); //AT THIS LINE, IT THROWS AN EXCEPTION THAT SAYS
"ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare
the variable '@id'



What is the problem? Thanx in advance

 

[D Cameron]

It looks like you're using the CommandType = Text (which is the default)
in which case:

"The ODBC .NET Provider does not support named parameters for passing
parameters to an SQL statement or a stored procedure called by an
OdbcCommand when CommandType is set to Text. In this case, the question
mark (?) placeholder must be used. For example:

SELECT * FROM Customers WHERE CustomerID = ?"

Taken from the Visual Studio help... I wish I could somehow send you a
help link.

If you're only writing against SQL Server, you could use the
System.Data.SqlClient namespace instead of the general ODBC one. It has
significantly nicer funtionality.