locked
Query against Progress DataBase

    Question

  • I need to query and Progess database with a "selected date" and cannot make it work.

    string Getit = "select count(*) from pub.oeeh" +

     

    " where cono = " + jmhp.Cono +

     

    " and whse = '" + jmhp.WHID + "'" +

     

    " and enterdt >= " + Convert.ToDateTime(jmhp.SelDate);

     

    OdbcCommand SxProd_sqlcmd = new OdbcCommand();

    SxProd_sqlcmd.CommandText = Getit;

    SxProd_sqlcmd.Connection = SxProd_Connection;

     

    int cnt = 0;

    cnt = (

    int)SxProd_sqlcmd.ExecuteScalar();
     

     

    Error Message:

    {"ERROR [HY000] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about \"12:00:00 AM\" (10713)"}

     

    Does anyone work with Progress that can help me on this?

    Monday, September 19, 2011 10:11 PM

Answers

  • Hi,
    Looks like that you have a problem with Convert.ToDateTime statement when you are building up your query string: The returned DateTime is implicitly converted back to a string. I would suggest also to use the SQL function TO_DATE() since you are passing a string without parameter to the OdbcCommand:
    string Getit = String.Format( "select count(*) from pub.oeeh where cono = {0} and whse = '{1}' and enterdt >= TO_TIME('{2}')",
                                             jmhp.Cono,
                                             jmhp.WHID,
                                             mhp.SelDate);
    
    

    Although I would prefer to pass typed parameters to the OdbcCommand since I consider this as a better programming style and it also helps to avoid SQL injections. Alternatively you could use something like this:
     
                OdbcCommand cmd = new OdbcCommand();
                cmd.CommandText = "select count(*) from pub.oeeh where cono = ? and whse = ? and enterdt >= ?)";
                cmd.Parameters.Add("", OdbcType.Int).Value = jmhp.Cono;
                cmd.Parameters.Add("", OdbcType.Text).Value =  jmhp.WHID;
                cmd.Parameters.Add("", OdbcType.DateTime).Value = System.Convert.ToDateTime(mhp.SelDate);
    
    

    Hope that helps,
    Stefan
     

    Tuesday, September 20, 2011 5:58 AM
  • Change query to:

    string Getit = @"select count(*) from pub.oeeh " +
    "where cono = '" + jmhp.Cono + "' " +
    "and whse = '" + jmhp.WHID + "' " +
    "and enterdt >= '" + Convert.ToDateTime(jmhp.SelDate) + "'";
    


    Important is to use single and double quotes when passing variables inside the string.

    ---------------------------

    But as Heesch explained, when connection to an sql server using Odbc connection, you have to use "?" mark as parameter in the sql query. Check out his 2nd code snippet.!


    Mitja
    • Proposed as answer by Capoor,Tushar - MVP Tuesday, September 20, 2011 7:19 AM
    • Marked as answer by JHab Tuesday, September 20, 2011 8:13 PM
    Tuesday, September 20, 2011 6:55 AM
  • Both of you were correct.   I looked at the field in the database and it was defined as a date field, not datetime.   By converting the input date to a "short date" and then adding the ' in the query it now works.  Thanks to both of you.

    jmhp

    .SelDate = CalSelDate.SelectionStart.Date.ToShortDateString();

     

    string

    Getit = "select count(*) from pub.oeeh" +

     

    " where cono = " + jmhp.Cono +

     

    " and whse = '" + jmhp.WHID + "'" +

     

    " and enterdt >= '" + jmhp.SelDate + "'";
    Tuesday, September 20, 2011 8:17 PM

All replies

  • Hi,
    Looks like that you have a problem with Convert.ToDateTime statement when you are building up your query string: The returned DateTime is implicitly converted back to a string. I would suggest also to use the SQL function TO_DATE() since you are passing a string without parameter to the OdbcCommand:
    string Getit = String.Format( "select count(*) from pub.oeeh where cono = {0} and whse = '{1}' and enterdt >= TO_TIME('{2}')",
                                             jmhp.Cono,
                                             jmhp.WHID,
                                             mhp.SelDate);
    
    

    Although I would prefer to pass typed parameters to the OdbcCommand since I consider this as a better programming style and it also helps to avoid SQL injections. Alternatively you could use something like this:
     
                OdbcCommand cmd = new OdbcCommand();
                cmd.CommandText = "select count(*) from pub.oeeh where cono = ? and whse = ? and enterdt >= ?)";
                cmd.Parameters.Add("", OdbcType.Int).Value = jmhp.Cono;
                cmd.Parameters.Add("", OdbcType.Text).Value =  jmhp.WHID;
                cmd.Parameters.Add("", OdbcType.DateTime).Value = System.Convert.ToDateTime(mhp.SelDate);
    
    

    Hope that helps,
    Stefan
     

    Tuesday, September 20, 2011 5:58 AM
  • Change query to:

    string Getit = @"select count(*) from pub.oeeh " +
    "where cono = '" + jmhp.Cono + "' " +
    "and whse = '" + jmhp.WHID + "' " +
    "and enterdt >= '" + Convert.ToDateTime(jmhp.SelDate) + "'";
    


    Important is to use single and double quotes when passing variables inside the string.

    ---------------------------

    But as Heesch explained, when connection to an sql server using Odbc connection, you have to use "?" mark as parameter in the sql query. Check out his 2nd code snippet.!


    Mitja
    • Proposed as answer by Capoor,Tushar - MVP Tuesday, September 20, 2011 7:19 AM
    • Marked as answer by JHab Tuesday, September 20, 2011 8:13 PM
    Tuesday, September 20, 2011 6:55 AM
  • Both of you were correct.   I looked at the field in the database and it was defined as a date field, not datetime.   By converting the input date to a "short date" and then adding the ' in the query it now works.  Thanks to both of you.

    jmhp

    .SelDate = CalSelDate.SelectionStart.Date.ToShortDateString();

     

    string

    Getit = "select count(*) from pub.oeeh" +

     

    " where cono = " + jmhp.Cono +

     

    " and whse = '" + jmhp.WHID + "'" +

     

    " and enterdt >= '" + jmhp.SelDate + "'";
    Tuesday, September 20, 2011 8:17 PM
  •  

    Hi JHab,

     

    We're glad to hear that you got it working. Thanks for your feedback.  

    If you have any difficulty in future programming, you are welcome to post here again.


    Thanks Stefan and Mitja for your great help and suggestions.


    Martin Xie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, September 21, 2011 2:56 AM
    Moderator