none
Problem using query to get records between two dates RRS feed

  • Question

  • I´m trying to select records which are between two dates. I use the following statement.

     

    qry = System:Tongue Tiedtring::Format("SELECT sum(breakfast), sum(colacao), sum(lunch), sum(snacks), sum(dinner) FROM alunos, logtable WHERE alunos.cad_matr=logtable.studentid and alunos.cad_matr="+tbStudentId->Text+" and dateofmeal >=#"+dt->ToString("dd/MM/yyyy 00:00:00" )+"# and dateofmeal <=#"+dt2->ToString("dd/MM/yyyy 00:00:00" )+"#" );

     

    Although the records exists the query does not get these records. If I go to the Query Design and use the same query it works but only if I enter the dates manually (dateofmel >=?).

     

    Can anybody help me to solve thih \/

     

    Thanks

    • Moved by Bob Beauchemin Friday, November 23, 2012 10:58 AM Moved to the appropriate forum for client-side SQLClient questions (From:.NET Framework inside SQL Server)
    Sunday, February 10, 2008 2:57 PM

All replies

  • You should consider changing your concatenations out for Parameters, and then in your SQL Command bind the values to the parameters.  You are at risk for SQL injection by anyone who uses the application other wise.  Also using the parameters, you bind to a datatype as well, so @StudentID is a datetime datatype when created as a parameter.  Can you access the SQL Profiler, and run a trace to see exactly what is being executed from the application?

     

    This is what it would be with parameters.  The SQL itself seems fine.  You would have to see what the app is creating as the string

    Code Snippet
    qry = "SELECT sum(breakfast), sum(colacao), sum(lunch), sum(snacks), sum(dinner) FROM alunos, logtable WHERE alunos.cad_matr=logtable.studentid and alunos.cad_matr = @StudentID and dateofmeal >= @StartDate and dateofmeal <=@EndDate";

     

     

     

     

    Monday, February 11, 2008 4:06 PM
  • Something else to consider is the format of the dates. If the database contains the time as well as the date, and you query the date alone, it assumes 12:00 am as the time and it will have an effect on the results of your query.

     

    Steve

     

    Wednesday, March 19, 2008 6:44 PM
  • As one of the replies said, you should be sure to create a DBFactory or a class with a function that will take these values and call a stored procedure with the dates as parameters. Concatenated SQL puts you at risk of SQL injection. If you are unable to do so, be sure to at least strip out suspect characters.

    More importantly, be sure to use yyyy-mm-dd date format if you are doing BETWEEN or date ranges (or at least consider).

    Friday, November 23, 2012 3:50 AM