none
SQL Server Query/VB.NET Problem

    Question


  • Hi there,

    I am having a problem with my ASP.NET web application and was hoping someone out there could help?! (I am using VB.NET 2005 and SQL Server 2005 Standard Edition.)

    The following code works fine - the dataTable is populated with rows.


    Code Snippet

    strSQL = "SELECT * FROM tblAudit "


    ' Create our SQL Connection
     sqlConnection = New SqlConnection(GetConnectionString("ConnName"))

     ' Create our SQL Command object
     sqlCommand = New SqlCommand(strSQL, sqlConnection)

     ' Create a new SQL data adapter - passing in sql command
     sqlDataAdapter = New SqlDataAdapter(sqlCommand)

     ' Create a data table to store the results of our command
     dataTable = New DataTable

     ' Check for invalid search criteria (29th Feb etc)
     Try

     ' Fill the dataTable with the results in the data adapter
     sqlDataAdapter.Fill(dataTable)

     Catch ex As Exception


        ' Error checking here


    End Try


    The following code is almost identical to the previous (working!) code, but when it is run, the dataTable is populated with zero rows.

    Code Snippet

    strSQL = "SELECT * FROM tblAudit "

    strSQL += "WHERE (AuditDateTime >= '" & strDateFrom & "' AND AuditDateTime <= '" & strDateTo & "') "


    ' Create our SQL Connection
     sqlConnection = New SqlConnection(GetConnectionString("ConnName"))

     ' Create our SQL Command object
     sqlCommand = New SqlCommand(strSQL, sqlConnection)

     ' Create a new SQL data adapter - passing in sql command
     sqlDataAdapter = New SqlDataAdapter(sqlCommand)

     ' Create a data table to store the results of our command
     dataTable = New DataTable

     ' Check for invalid search criteria (29th Feb etc)
     Try

     ' Fill the dataTable with the results in the data adapter
         sqlDataAdapter.Fill(dataTable)

     Catch ex As Exception


        ' Error checking here


    End Try



    Here is the real head-scratcher though; both queries, when written into Query Analyser (I set a breakpoint during execution and copy the strSQL value and paste it into QA) work fine - they both return record sets. So, seeing as the SQL query is correct, why am I getting an empty dataTable?!

    Please can someone help - I have been pulling my hair out over this all day (and I don't have a lot to spare!)

    Many thanks,

    Rich

    Tuesday, May 08, 2007 4:11 PM

Answers


  • All sorted now. As previously suggested, it was the dates in the incorrect format. Rookie mistake!

    Thanks for everyone's help - it's very much appreciated.

    Cheers,

    Rich

    Wednesday, May 09, 2007 11:44 AM

All replies

  • What format are strDateFrom and strDateTo in? Is it 01-01-2001 or 01/01/01?

     

    Adamus

    Tuesday, May 08, 2007 4:20 PM
  •  

    The date's are entered YYYY/MM/DD, so today's date would be 2007/05/08

     

    Rich

    Tuesday, May 08, 2007 4:23 PM
  • That's your answer. The format of the string needs to match the format in SQL.

     

    Adamus

    Tuesday, May 08, 2007 4:27 PM
  •  

    But it works "as is" within Query Analyser. My dates are stored like "2007-04-19 13:49:45.000" as a datetime field.

     

    Does QA allow you to get away with things like this then?

     

    When I change my dates to look for 08/05/2007 instead of 2007/05/08 the server assumes it is an American date and not a UK date. Anyway around this (short of changing server settings)

     

    Thanks for your help,

    Rich

    Tuesday, May 08, 2007 4:38 PM
  • QA has no magical functionality. If it works in QA, it'll work anywhere as long as the string is identical.

     

    My only other guess would be one of the strings is null.

     

    Adamus

    Tuesday, May 08, 2007 4:58 PM
  • What is the value of strSQL when you break into the code and look at the string value? Do you take that exact string and run it in Query Analyzer? Please post the string value for us to see.
    Wednesday, May 09, 2007 4:41 AM
  • Yes, it's the exact string run in Query Analyser:

    Code Snippet

    SELECT * FROM tblAudit WHERE (AuditDateTime >= '2007/05/07' AND AuditDateTime <= '2007/05/09') 


    Having made the changes to the date format as suggested (DD/MM/YYYY), my code now works correctly and produces a record set, but the query doesn't return anything within Query Analyser.


    Wednesday, May 09, 2007 8:11 AM

  • All sorted now. As previously suggested, it was the dates in the incorrect format. Rookie mistake!

    Thanks for everyone's help - it's very much appreciated.

    Cheers,

    Rich

    Wednesday, May 09, 2007 11:44 AM