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

Question

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.

I've never used *Positional Parameters* with ADO.NET, but see [Configuring Parameters and Parameter Data Types](http://msdn.microsoft.com/en-us/library/yy6y35y8(v=vs.110).aspx) - it appears the correct/allowed form varies ("The syntax for parameter placeholders depends on the data source."), shame. — user2864740, Aug 22 '14 at 18:26

score 3 · Accepted Answer · answered Aug 22 '14 at 18:31

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.

So in that case, even if I try to use named parameters it would work as if I were using a `?` In that case my question doesn't make a lot sense. Thank you! — Guranjan Singh, Aug 22 '14 at 18:42
@GuranjanSingh, no your question is fine. You didn't see the difference because of only single parameter, you would have noticed the difference, if you used multiple parameters and their order was mixed up. Remember this is only true for `OleDbCommand`, with `SqlCommand` order of parameters (named parameters) doesn't matter. — Habib, Aug 22 '14 at 18:44

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

1 Answers1