Help with query sintax C# 1

[chilly442]

I am converting an application that was written in VB to C#. I am trying to test this query to pull data from Oracle. This query gives me anything = or > STime, but ignores the year.

So if STime = 01/01/2010 12:00:00
I get 01/01/1998 12:00:00, 03/03/1995 11:00:00
I need anything that is greater than STime.
I think that there is just a synxat error and I am not sure what I am missing.

Any help is greatly appreciated!

string query; 
OdbcConnection Con = new OdbcConnection();
DateTime STime;

query = ("SELECT * FROM Events WHERE(Unit = '" + Unit + "') 
          AND StartTime >= TO_DATE('" + STime + "', 'MM/DD/YYYY HH:MI:SS')");

        try 
        {
         OdbcCommand CMD = new OdbcCommand(query, Con);
         OdbcDataAdapter da = new OdbcDataAdapter();
         da.SelectCommand = CMD;
         Con.Open();
         DataSet ds = new DataSet();
         da.Fill(ds, "Events");
         GridView1.DataSource = ds; 
         GridView1.DataBind(); 
         Con.Close();
        } 
        catch (Exception ex) 
        { 
            MessageBox.Show(ex.ToString());
        }

Thanks,
Chilly442
---------------------------------------
If I lived anywhere else I'd be Sunny442

 

[jmeckley]

1. MessageBox is a WinForms object it isn't meant for use with asp.net. the code runs on the server, not the client. Instead you want to log the error message (database, text file, message queue, email, etc) to review the errors. Log4Net is an excellent logging library.
2. you are not properly managing your connections and commands. Most notably you are not disposing of them in a try/finally or using block.
3. you are using sql injection. Use a parametrized query instead.

I have an FAQ on database connection management about how you can effectively manage your database connections with asp.net. See my signature below for the link. If you have any questions please ask.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[chilly442]

When I run the following query, it returns dates that are before 2010 and I am not sure why.

SELECT * FROM Events WHERE (Unit = 'D5') AND StartTime >= TO_DATE('01/01/2010', 'MM/DD/YYYY')

Is this because the Oracle DB has the dates stored as "MM/dd/yyyy HH:MM:SS"?

If so what do I need to change to get the correct format?

Thanks,
Chilly442
---------------------------------------
If I lived anywhere else I'd be Sunny442

 

[jmeckley]

Is the value stored as a date type, or something else (number, text). If the data type is date the format of the date doesn't matter. If the value is stored as something other than a date (text for a formatted date) then you are comparing strings, not dates and this could effect the results.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[chilly442]

It is stored as a Date.

Thanks for taking the time to help me.

Thanks,
Chilly442
---------------------------------------
If I lived anywhere else I'd be Sunny442