Adding parameters to the WHERE clause of a SQL que

问题:

I'm using ADO.NET to connect to an Oracle DB through ODBC. Everything is working fine, besides binding parameters with a simple SQL query:

Connection.Open();
IDbCommand command = Connection.CreateCommand();
command.CommandText = "SELECT length FROM activity_type WHERE name = :name_of_activity";
var parameter = command.CreateParameter();
parameter.ParameterName = ":name_of_activity";
parameter.Value = "Short_break";
command.Parameters.Add(parameter);
int result = Convert.ToInt32(command.ExecuteScalar());
Connection.Close();

It always returns 0 results (a null from ExecuteScalar() - the same from a reader). But if I'd put a straightforward SQL query like this: command.CommandText = "SELECT length FROM activity_type WHERE name = 'Short_break'" it would work like a charm. Whats more, I used similar constructions all over the code for INSERT INTO clauses, and they were OK.

Am I missing something?

回答1:

From the docs for OdbcCommand.Parameters:

When CommandType is set to Text, the .NET Framework Data Provider for ODBC does not support passing named parameters to an SQL statement or to a stored procedure called by an OdbcCommand. In either of these cases, use the question mark (?) placeholder. For example:

SELECT * FROM Customers WHERE CustomerID = ?

In other words, your code should look like this:

Connection.Open();
IDbCommand command = Connection.CreateCommand();
command.CommandText = "SELECT length FROM activity_type WHERE name = ?";
var parameter = command.CreateParameter();
parameter.Value = "Short_break";
command.Parameters.Add(parameter);
int result = Convert.ToInt32(command.ExecuteScalar());
Connection.Close();

(You should probably be considering using using statements, mind you... otherwise if this throws an exception, you won't be closing the connection.)

回答2:

From what you're describing the problem can only be in the parameter.

Maybe some kind of strange mixup like between varchar and nvarchar on SQL Server, in other words try setting the datatype explicitely.

回答3:

Try to change this line:

parameter.ParameterName = ":name_of_activity";

to

parameter.ParameterName = "name_of_activity";

In other words remove colon character