Reputation: 1625
Command.CommandText = "SELECT SUM(REQHOURS), SUM(REQQUANTITY) / COUNT(*)
FROM ORDERS WHERE ORDERNUMBER = :OrderNumber AND
OPERATION = (SELECT MIN(OPERATION) FROM ORDERS
WHERE ORDERNUMBER = :OrderNumber AND OPERATION > :Operation)";
Command.Parameters.Add("OrderNumber", order.OrderNumber);
Command.Parameters.Add("Operation", order.Operation);
The query above is working fine in the Oracle SQL Developer (OSQLD for further reference) application but when I run it in C# I get the following error:
ORA-01008: not all variables bound
When I debugged parts of the query in C# I found out that if I remove AND OPERATION > :Operation
the query executes but the DataReader
is Null
. If I than run it in OSQLD I do get values back.
For people wondering, order.OrderNumber
and order.Operation
are certainly not Null
.
I have multiple queries in my application and all of them work fine. It's only this one that gives me problems.
Upvotes: 0
Views: 1562
Reputation: 1625
I found the solution after some thinking basically...
The code I started with was as follow:
Command.CommandText = "SELECT SUM(REQHOURS), SUM(REQQUANTITY) / COUNT(*)
FROM ORDERS WHERE ORDERNUMBER = :OrderNumber AND
OPERATION = (SELECT MIN(OPERATION) FROM ORDERS
WHERE ORDERNUMBER = :OrderNumber AND OPERATION > :Operation)";
Command.Parameters.Add("OrderNumber", order.OrderNumber);
Command.Parameters.Add("Operation", order.Operation);
In the query are 3 parameters :OrderNumber
twice and :Operation
once so I added both to the parameter list. After all, you only have to assign a variable to a parameter once in the Oracle SQL Developer application.
Here's the tricky part. The reason why I got error ORA-01008
was justified. I got 3 parameters and I only added 2 to the parameter list hence the error.
The piece of code below is not a working version of the query:
Command.CommandText = "SELECT SUM(REQHOURS), SUM(REQQUANTITY) / COUNT(*)
FROM ORDERS WHERE ORDERNUMBER = :OrderNumber AND
OPERATION = (SELECT MIN(OPERATION) FROM ORDERS
WHERE ORDERNUMBER = :OrderNumber AND OPERATION > :Operation)";
Command.Parameters.Add("OrderNumber", order.OrderNumber);
Command.Parameters.Add("OrderNumber", order.OrderNumber);
Command.Parameters.Add("Operation", order.Operation);
Upvotes: 1
Reputation: 11820
You need to specify parameter name with :
. I thing you just missed that because you mentioned you have already working queries :). So in your case:
Command.Parameters.Add(":OrderNumber", order.OrderNumber);
Command.Parameters.Add(":Operation", order.Operation);
Upvotes: 0