Hi
I'm really hoping someone can help with this,

I am trying to add parammeters to a sql statement that is inside a OracleCommand CommandText.
I get them from the queryString initially, I know that in oracle you must put a : before the variable name
What would the syntax of MyCommand.Paramters.Add be in the case of a sql string being used?

It is the  values '01-MAY-2007' and '10-MAY-2007' I'm trying to paramterize, replacing them with variables from the query string
Can I do this without having to write stored procedures?
Thank you very very much

     

1   // ...
2            string  startdate = "";
3            string  enddate = "";
4            if (Request.QueryString["dtstart"] != "")
5                {
6                   startdate = Request.QueryString["dtstart"];
7                }
8            if (Request.QueryString["dtend"] != "")
9                {
10                   enddate = Request.QueryString["dtend"];
11               }
12        
13          
14         //  ....
15          
16           OracleConnection oracleConn = new OracleConnection();
17            oracleConn.ConnectionString = "Data Source=Z;User ID=xxx;Password=xxx;Unicode=True";
18           oracleConn.Open();
19    
20           OracleCommand myCommand = oracleConn.CreateCommand();
21           myCommand.CommandText="SELECT COUNT(*) FROM TBLThings WHERE enteredDate  BETWEEN '01-MAY-2007' AND '10-MAY-07')";
22           //myCommand.commandType = CommandType.Text;
23           myCommand.ExecuteNonQuery(); 

           myCommand.CommandText="SELECT COUNT(*) FROM TBLThings WHERE enteredDate  BETWEEN :StartDate AND :EndDate)";
           myCommand.Parameters.AddWithValue(":StartDate", startdate);
           myCommand.Parameters.AddWithValue(":EndDate", enddate);

 thank you Star, I'll try that . looks so simple when you kno how right !

 Hi Star

Thanks for your help. it was very useful, however when I tried to modify it for an update statement it didnt work.

Any thoughts on  why parameterizing this way  does not work for me?

Once again , thank you 

//get values from query string ...

myCommand.CommandText = "UPDATE totals SET count1= (SELECT COUNT(*) FROM things WHERE date BETWEEN :StartDate AND :EndDate) ";
 
myCommand.Parameters.AddWithValue(":StartDate", startdate);
myCommand.Parameters.AddWithValue(":EndDate", enddate);
myCommand.ExecuteNonQuery();
 

 Your update statement will update count1 column of all the records in Totals table. Is that what you want?

I can't see anything wrong with the code you have posted (provided you want to update all rows).

Why do you think this is not working?

When adding the parameters you should omit the : colon, it's only needed in your update statement.

myCommand.CommandText = "UPDATE totals SET count1= (SELECT COUNT(*) FROM things WHERE date BETWEEN :StartDate AND :EndDate) ";

myCommand.Parameters.AddWithValue("StartDate", startdate);
myCommand.Parameters.AddWithValue("EndDate", enddate);
myCommand.ExecuteNonQuery();