Adding parameters to the WHERE clause of a SQL query

Question

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?

Community · Accepted Answer · 2020-06-20 09:12:55Z

7

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.)

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 26, 2010 at 20:44

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mcmil Over a year ago

Thank you very much for your answer. I've tried the '?' placeholder before, with no luck. Still the same result. Named parameters work in every other part of the code. I've extracted the code from the project, originally Connection.Close() is in a finally clause.

Jon Skeet Over a year ago

@M_F: Is this genuinely an OdbcCommand? It's hard to tell from the fairly generic code given. Unfortunately different providers support different forms of parameterized queries. Have you tried looking at your database logs to see what's getting through?

mcmil Over a year ago

Yes it is an OdbcCommand. Connection is created as a IdbConnection Connection = new OdbcConnection("DSN=oracle; UID=xxx; PWD=xxx");. I try to get to the logs, thanks.

Jon Skeet Over a year ago

@M_F: Have you tried explicitly setting the command type to Text? I wouldn't expect that to make a difference, but you never know...

Vitalogy · Accepted Answer · 2010-12-27 02:03:51Z

0

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.

answered Dec 27, 2010 at 2:03

Vitalogy

11 bronze badge

Comments

Tony Kh · Accepted Answer · 2010-12-27 12:20:28Z

0

Try to change this line:

parameter.ParameterName = ":name_of_activity";

to

parameter.ParameterName = "name_of_activity";

In other words remove colon character

answered Dec 27, 2010 at 12:20

Tony Kh

1,58211 silver badges8 bronze badges

Collectives™ on Stack Overflow

Adding parameters to the WHERE clause of a SQL query

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related