Chut319



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




Re: SQL Server Query/VB.NET Problem

Adamus Turner


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

Adamus







Re: SQL Server Query/VB.NET Problem

Chut319

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

Rich






Re: SQL Server Query/VB.NET Problem

Adamus Turner

That's your answer. The format of the string needs to match the format in SQL.

Adamus






Re: SQL Server Query/VB.NET Problem

Chut319

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





Re: SQL Server Query/VB.NET Problem

Adamus Turner

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






Re: SQL Server Query/VB.NET Problem

MC_in_BigD

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.




Re: SQL Server Query/VB.NET Problem

Chut319

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.






Re: SQL Server Query/VB.NET Problem

Chut319


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