Last post Jun 04, 2014 05:43 PM by PatriceSc
Jun 04, 2014 02:27 PM|bbcompent1|LINK
The query I'm trying to execute is:
SELECT NUMBER, STARTDATE, ENDDATE, DESCRIPTION, STATUS, ASSIGNED from TABLE WHERE STARTDATE > :STARTDATE ORDER BY STARTDATE;
and my code is like this:
string MyQuery = "SELECT NUMBER, STARTDATE, ENDDATE, DESCRIPTION, STATUS, ASSIGNED from TABLE";
string oracon = xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx; // conn string from web.config
OracleConnection con = new OracleConnection(oracon);
OracleDataAdapter da = new OracleDataAdapter(MyQuery, con);
DataSet ds = new DataSet();
if ((start != null ) || (Assigned != null) || (Status != null))
MyQuery = MyQuery + " WHERE ";
if (start != null)
// sets the start date to today by default
MyQuery = MyQuery + "STARTDATE > :STARTDATE ";
da.SelectCommand.Parameters.AddWithValue("STARTDATE", Convert.ToDateTime(start).ToString("MM-dd-yyyy HH:mm:ss"));
MyQuery = MyQuery + " WHERE STARTDATE > " + DateTime.Now.ToString() + "";
if (Assigned != null)
MyQuery = MyQuery + " AND ASSIGNED LIKE :ASSIGNED ";
if (Status != null)
MyQuery = MyQuery + " AND Status = :STATUS ";
MyQuery = MyQuery + " ORDER BY PLANNED_START";
The query looks fine to me, what's wrong?
Jun 04, 2014 02:45 PM|PatriceSc|LINK
What if showing the computed MyQuery string when this error happens ? It might be easier to spot an error directly from the SQL statement you have build.
My guess is that WHERE STARTDATE > " +
+ ""; likely won't create a valid date comparison syntax and depends anyway on your current culture settings. Here you could
use a parameter as you have done already for other values.
Also do use the appropriate data type when using AddWithValue. That is if the parameter is a date, pass a date value. This way the ADO.NET provider is responsible for taking the date and transmitting its value under a suitable format to the server side. If using
ToString() then you transmit the date as a string and YOU are responsible to use a string representation that is usable for a SQL date.
Jun 04, 2014 02:48 PM|bbcompent1|LINK
That's the thing though, I am passing it as MM-dd-yyyy HH:mm:ss and I am in the US. Do I need to get the value in that format and then convert that value to DateTime?
If using ToString() then you transmit the date as a string and YOU are responsible to use a string representation that is usable for a SQL date.
Oh, ok, that makes sense.
Jun 04, 2014 03:31 PM|PatriceSc|LINK
http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm#BABGIGCJ it seems date literal could be written as DATE 'YYYY-MM-DD' that is for example (taken from the same page):
SELECT * FROM my_table WHERE datecol = DATE '2002-10-03';
So as you see if doing this yourself you have to use a particular syntax. If uisng the proper type it should be done the right way for you.
What is the current situation ? Is this fixed ? Do you have another error now ?
Jun 04, 2014 04:55 PM|bbcompent1|LINK
I'm passing my value in like so:
Is this ok for a date/time format?
Jun 04, 2014 05:43 PM|PatriceSc|LINK
And what happens is ?
What is Starttime ? You convert it to a string and then convert it back to a DateTime ? Assuming Starttime is already a DateTime I would use :