First of all try adding the ':' to your parameters names:
OracleParameter start = new OracleParameter(":start", OracleType.DateTime);
start.Value = new DateTime(yearInt, monthInt, dayInt, hourInt, minuteInt, secondInt);
cmd.Parameters.Add(start);
OracleParameter end= new OracleParameter(":end", OracleType.DateTime);
end.Value = new DateTime(yearInt, monthInt+1, dayInt, hourInt, minuteInt, secondInt);
cmd.Parameters.Add(end);
Now if you are using ODP.NET (and you should be) I advice you set following property:
cmd.BindByName = true;
This will make sure that ODP.NET will bind parameters by name. This is important in case you are using ':start' or ':end' more than once (I can't tell is that happening in your case as you haven't shown full query).
Please indicate "Mark as Answer" if a post has answered the question.
Yet another developer blog <-- visit my blog
Kovarian
Member
2 Points
10 Posts
Oracle Variable Binding
Nov 05, 2012 10:59 PM|LINK
I'm getting a "Not all variables bound" with the following code. Any ideas what may be causing it? Assume that all the variables are assigned earlier.
***********************************************************************
OracleCommand cmd = connection.CreateCommand();
OracleParameter start = new OracleParameter("start", OracleType.DateTime);
start.Value = new DateTime(yearInt, monthInt, dayInt, hourInt, minuteInt, secondInt);
cmd.Parameters.Add(start);
OracleParameter end= new OracleParameter("end", OracleType.DateTime);
end.Value = new DateTime(yearInt, monthInt+1, dayInt, hourInt, minuteInt, secondInt);
cmd.Parameters.Add(end);
***
SQL.Append("WHERE DATE >= :start");
SQL.Append("AND DATE < :end");
tpeczek
Contributor
2112 Points
260 Posts
Re: Oracle Variable Binding
Nov 06, 2012 06:40 AM|LINK
First of all try adding the ':' to your parameters names:
OracleParameter start = new OracleParameter(":start", OracleType.DateTime); start.Value = new DateTime(yearInt, monthInt, dayInt, hourInt, minuteInt, secondInt); cmd.Parameters.Add(start); OracleParameter end= new OracleParameter(":end", OracleType.DateTime); end.Value = new DateTime(yearInt, monthInt+1, dayInt, hourInt, minuteInt, secondInt); cmd.Parameters.Add(end);Now if you are using ODP.NET (and you should be) I advice you set following property:
This will make sure that ODP.NET will bind parameters by name. This is important in case you are using ':start' or ':end' more than once (I can't tell is that happening in your case as you haven't shown full query).
Yet another developer blog <-- visit my blog
Kovarian
Member
2 Points
10 Posts
Re: Oracle Variable Binding
Nov 06, 2012 02:32 PM|LINK
Ok I found the solution. Wasn't executing on the same OracleCommand object. Thanks!