Search code examples
sqlasp.netcommandparameter

Difference between "?" and "@NAME" when specifying Parameter placeholders?

If any, what is the difference between the following ways passing parameters.

SQLStr = "SELECT * FROM TABLE_NAME WHERE ID = ? "
command = new oleDbCommand(SQLStr, conn)
Command.Parameters.AddWithValue("@ID", Request.Querystring("ID"))

Vs. 

SQLStr = "SELECT * FROM TABLE_NAME WHERE ID = @ID "
Command = new oleDbCommand(SQLStr, conn)
Command.Parameters.AddWithValue("@ID", Request.Querystring("ID"))

Maybe not in this example but could these two methods have different meanings? Perhaps when I need to pass the same value twice and I would be tempted to use the same variable name?

Thanks.

Solution

  • OleDbCommand does not support named parameters. Even if you use named parameter with @ in your current query, their order will only matter. Currently you have only one parameter so you won't see the difference.

    See: OleDbCommand.Parameters Property

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

    SELECT * FROM Customers WHERE CustomerID = ?

    Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

    Consider the following examples with multiple parameters:

    SQLStr = "SELECT * FROM TABLE_NAME WHERE ID = @ID  AND NAME = @Name";
Command = new oleDbCommand(SQLStr, conn);
Command.Parameters.AddWithValue("@Name", "ABC");
Command.Parameters.AddWithValue("@ID", Request.Querystring("ID")); //'A1'

    Since @Name is added before @ID in the parameter collection, the query would look like :

    SELECT * FROM TABLE_NAME WHERE ID = 'ABC' AND NAME = 'A1`; //assuming ID is A1

    Note that ID got the value of NAME parameter and so as NAME got the value of ID, which is wrong.