PDA

View Full Version : SQL problem


skwilliams
12-19-2005, 02:42 PM
<span style='color:red'>edited by dj - </span>

I have an ASP form with two date fields (FromDate and ToDate).

I get this error.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression 'dtDate >= #1# and dtDate <=#1#'.
/Fast2/WkTotalOrders.asp, line 27 [/color]

Here's the code for my results page

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/Fast2.asp" -->
<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.QueryString("FromDate") <> "") Then
Recordset1__MMColParam = Request.QueryString("FromDate")
End If
%>
<%
Dim Recordset1__MMColParam2
Recordset1__MMColParam2 = "1"
If (Request.QueryString("ToDate") <> "") Then
Recordset1__MMColParam2 = Request.QueryString("ToDate")
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_Fast2_STRING
Recordset1.Source = "SELECT * FROM qryDailyOrderTotals WHERE dtDate >= #" + Replace(Recordset1__MMColParam, "#", "##") + "# and dtDate <=#" + Replace(Recordset1__MMColParam2, "#", "##") + "#"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<%
Dim Recordset2__MMColParam
Recordset2__MMColParam = "1"
If (Request.QueryString("FromDate") <> "") Then
Recordset2__MMColParam = Request.QueryString("FromDate")
End If
%>
<%
Dim Recordset2__MMColParam2
Recordset2__MMColParam2 = "1"
If (Request.QueryString("ToDate") <> "") Then
Recordset2__MMColParam2 = Request.QueryString("ToDate")
End If
%>
<%
Dim Recordset2
Dim Recordset2_numRows

Set Recordset2 = Server.CreateObject("ADODB.Recordset")
Recordset2.ActiveConnection = MM_Fast2_STRING
Recordset2.Source = "SELECT * FROM qryDailyPartTotals WHERE dtDate >= #" + Replace(Recordset2__MMColParam, "#", "##") + "# and dtDate <= #" + Replace(Recordset2__MMColParam2, "#", "#") + "#"
Recordset2.CursorType = 0
Recordset2.CursorLocation = 2
Recordset2.LockType = 1
Recordset2.Open()

Recordset2_numRows = 0
%>
<%
Dim Recordset3__MMColParam
Recordset3__MMColParam = "1"
If (Request.QueryString("FromDate") <> "") Then
Recordset3__MMColParam = Request.QueryString("FromDate")
End If
%>
<%
Dim Recordset3__MMColParam2
Recordset3__MMColParam2 = "1"
If (Request.QueryString("ToDate") <> "") Then
Recordset3__MMColParam2 = Request.QueryString("ToDate")
End If
%>
<%
Dim Recordset3
Dim Recordset3_numRows

Set Recordset3 = Server.CreateObject("ADODB.Recordset")
Recordset3.ActiveConnection = MM_Fast2_STRING
Recordset3.Source = "SELECT * FROM qryDailyTimeTotals WHERE dtDate >= #" + Replace(Recordset3__MMColParam, "#", "##") + "# and dtDate<=#" + Replace(Recordset3__MMColParam2, "#", "##") + "#"
Recordset3.CursorType = 0
Recordset3.CursorLocation = 2
Recordset3.LockType = 1
Recordset3.Open()

Recordset3_numRows = 0
%>

davidj
12-19-2005, 03:05 PM
your error indicates..

Syntax error in date in query expression 'dtDate >= #1# and dtDate <=#1#'.

#1# is not a date

you have set your date vars to default to 1 if no date is given. Try setting these to 01/01/1999 and 01/01/3000

<%
Dim Recordset1__MMColParam

Recordset1__MMColParam = "1" //<<<<<<<<<<

If (Request.QueryString("FromDate") <> "") Then
Recordset1__MMColParam = Request.QueryString("FromDate")
End If
%>
<%
Dim Recordset1__MMColParam2

Recordset1__MMColParam2 = "1" //<<<<<<<<<<

If (Request.QueryString("ToDate") <> "") Then
Recordset1__MMColParam2 = Request.QueryString("ToDate")
End If
%>


i have so many apps with a To date set to 01/01/3000 as default value
I will be long dead when the support desk gets calls from client saying they are only getting results upto year 3000 when they want 3005 hehe

im sure some of these companies will still be using my application in 3000