Somewhat strange .NET error involving datetime

Question

I am getting this error on a remote server, but the same code executes fine locally. Please refrain from saying it sucks, or giving me your rant on dynamic sql, I didn't write it, just trying to figure out why it's throwing an exception. The highlighted error is line 56.

Protected Sub drpDateRange_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles drpRange.SelectedIndexChanged
    Dim sql As String = "SELECT postedDate, inspectionType FROM tInspectionRequest WHERE source_lookupID = 'IRS_WEB' "
    If _franchiseID > 0 Then sql &= " and franchiseeID = " & _franchiseID.ToString
    Dim db As New Database
    Dim ds As DataSet = db.selectQuery(sql)
    Dim dv As New DataView(ds.Tables(0))
    dv.RowFilter = "inspectionType='Buyer' AND postedDate >= #" & DateTime.Now.AddDays(-1) & "#"
    lblB1.Text = dv.Count
End Sub

Here is the exception, it seems like DateTime.Now.AddDays(-1) is failing being cast as a datetime? Regardless if it's a casting issue / date to string error, it's strange it fails only on the remote server, and not locally.

String was not recognized as a valid DateTime. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: String was not recognized as a valid DateTime.

Source Error:

Line 55: Dim dsInspectionHistory As DataSet = objDB.selectQuery(sqlInspectionHistory)

Line 56: Dim dvInspectionHistory As New DataView(dsInspectionHistory.Tables(0))

Line 57: dvInspectionHistory.RowFilter = "inspectionType='Buyer' AND postedDate >= #" & DateTime.Now.AddDays(-1).ToString & "#"

Line 58: lblB1.Text = dvInspectionHistory.Count

Line 59: dvInspectionHistory.RowFilter = "inspectionType='Seller' AND postedDate >= #" & DateTime.Now.AddDays(-1) & "#"

[FormatException: String was not recognized as a valid DateTime.] System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles) +2291962 System.DateTime.Parse(String s, IFormatProvider provider) +26 System.Data.ConstNode..ctor(DataTable table, ValueType type, Object constant, Boolean fParseQuotes) +485 System.Data.ExpressionParser.Parse() +830 System.Data.DataExpression..ctor(DataTable table, String expression, Type type) +124 System.Data.DataView.set_RowFilter(String value) +161 controls_inspectionRequestChart.drpRange_SelectedIndexChanged(Object sender, EventArgs e) in xxxx controls_inspectionRequestChart.Page_Load(Object sender, EventArgs e) in xxxx System.Web.UI.Control.OnPreRender(EventArgs e) +2117788 System.Web.UI.Control.PreRenderRecursiveInternal() +86 System.Web.UI.Control.PreRenderRecursiveInternal() +170 System.Web.UI.Control.PreRenderRecursiveInternal() +170 System.Web.UI.Control.PreRenderRecursiveInternal() +170 System.Web.UI.Control.PreRenderRecursiveInternal() +170 System.Web.UI.Control.PreRenderRecursiveInternal() +170 System.Web.UI.Control.PreRenderRecursiveInternal() +170 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041

Answer 1

Whats with the #'s surrounding the date?

SQL understands quoted dates when using text represented dates, ie '01/01/1901' or '05/28/1978 21:13:00.000'.

The dataset/table you are selecting from may have the postedDate column as a String and not a DateTime. Can you check the column type?

Check the regional settings on the remote machine and see if they match your regional settings.

Answer 2

This might be due to a regional datetime settings difference between the server and your dev PC. There is an implicit conversion between datetime and string in the code (remember it is inline sql (Edit: RowFilter syntax), you're not setting a parameter).

Try formatting the string

dv.RowFilter = "inspectionType='Buyer' AND postedDate >= #" & DateTime.Now.AddDays(-1).ToString("MMM dd yyyy hh.mm.ss") & "#"

Answer 3

I think ChrisE's answer is likely to solve your problem, but while you're fixing it why not actually fix it.

Protected Sub drpDateRange_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles drpRange.SelectedIndexChanged
    Dim sql As String
    sql = "DECLARE @Yesterday DateTime;Set @Yesterday= getdate()-1;" & _
      " SELECT COUNT(*) AS CNT" & _
      " FROM tInspectionRequest" & _
      " WHERE source_lookupID = 'IRS_WEB' AND inspectionType='Buyer'" & _
          " AND (@FranchiseID <= 0 OR @FranchiseID = franchiseID)" & _
          " AND postedDate >= @Yesterday;"
     lblB1.Text = New Database().selectQuery(sql.Replace("@FranchiseID", _franchiseID.ToString()))
End Sub

This should perform much better because it lets the database handle filtering the rows and only sends the necessary information across the connection. It also completely side-steps your date conversion problem by letting the database handle that for you.

Of course real query parameters would be even better, but I understand you can't do that yet because you have to use that custom database wrapper and it's not a big deal here because the only parameter is a strongly-typed integer. Also, I don't know what provision if any the database wrapper makes for returning a scalar value rather than a dataset. There ought to be something, but it means you'll need to at least change that function name for this code to work.