|
Subject:
|
SQL problem in ASP
|
|
Posted By:
|
skwilliams
|
Post Date:
|
12/19/2005 10:53:16 AM
|
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
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
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<%
Dim Repeat2__numRows
Dim Repeat2__index
Repeat2__numRows = -1
Repeat2__index = 0
Recordset2_numRows = Recordset2_numRows + Repeat2__numRows
%>
<%
Dim Repeat3__numRows
Dim Repeat3__index
Repeat3__numRows = -1
Repeat3__index = 0
Recordset3_numRows = Recordset3_numRows + Repeat3__numRows
%>
<SCRIPT RUNAT=SERVER LANGUAGE=VBSCRIPT>
function DoDateTime(str, nNamedFormat, nLCID)
dim strRet
dim nOldLCID
strRet = str
If (nLCID > -1) Then
oldLCID = Session.LCID
End If
On Error Resume Next
If (nLCID > -1) Then
Session.LCID = nLCID
End If
If ((nLCID < 0) Or (Session.LCID = nLCID)) Then
strRet = FormatDateTime(str, nNamedFormat)
End If
If (nLCID > -1) Then
Session.LCID = oldLCID
End If
DoDateTime = strRet
End Function
</SCRIPT>
<html>
<head>
<title>Weekly Totals</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF">
<p align="center"><font size="4"><strong>Fast Incoming Order Totals</strong></font></p>
<p align="center"><%=(Recordset1.Fields.Item("dtDate").Value)%></p>
<table width="700" border="0" align="center" bgcolor="#FFFFFF">
<tr bgcolor="#FFCC00" >
<td width="14" height="21" bgcolor="#FFFFFF">
<div align="center"><font color="#FFCC00"><font color="#0000FF"></font></font></div></td>
<td width="286" bgcolor="#FFFFFF">
<div align="center"><font color="#FFCC00"><font color="#0000FF"></font></font></div></td>
<td width="174" bgcolor="#FFFFFF">
<div align="center"><font color="#0000FF"><strong>Orders</strong></font></div></td>
<td width="176" bgcolor="#FFFFFF">
<div align="center"></div>
<div align="center"></div>
<div align="center"><font color="#0000FF"><strong>Parts </strong></font></div></td>
<td width="6" bgcolor="#FFFFFF"> </td>
<td width="165" bgcolor="#FFFFFF">
<div align="center"></div>
<div align="center"></div>
<div align="center"><font color="#0000FF"><strong>Time </strong></font></div></td>
<td width="47" bgcolor="#FFFFFF"> </td>
</tr>
<tr >
<td height="20" colspan="3" bgcolor="#FFFFFF">
<table width="370" border="1">
<% While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) %>
<tr bgcolor="#FFFFFF" >
<td width="160" height="20">
<font color="#000000"><strong><%=(Recordset1.Fields.Item("CatType").Value)%></strong></font><div align="center">
<font color="%></font></strong></div></td>
<td width="100">
<div align="center"><strong><font size="2" face="Arial, Helvetica, s").Value)%></font></strong></div>
</font></td>
<td width="160" height="20"><font color="#000000" size="2" face="Arial, Helvetica, sans-serif"><strong><%=(Recordset1.Fields.Item("EmpName").Value)%></strong></font></td>
<td width="50" height="20">
<div align="center"><font color="#000000" size="2" face="Arial, Helvetica, s").Value)%><%=(Recordset1.Fields.Item("CountOfPart").Value)%></font></div></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%>
</table></td>
<td height="25" colspan="2" bgcolor="#FFFFFF">
<table width="105" border="1" cellpadding="1" cellspacing="2" bgcolor="#FFFFFF">
<% While ((Repeat2__numRows <> 0) AND (NOT Recordset2.EOF)) %>
<tr>
<td height="20" bgcolor="#FFFFFF">
<div align="center"></div>
<div align="center"></div>
<div align="center"></div>
<div align="center"><font color="#000000" size="3.5" face="Arial, Helvetica, sans-serif"><%=(Recordset2.Fields.Item("SumOfPart").Value)%></font></div></td>
</tr>
<%
Repeat2__index=Repeat2__index+1
Repeat2__numRows=Repeat2__numRows-1
Recordset2.MoveNext()
Wend
%>
</table></td>
<td height="20" colspan="2" bgcolor="#FFFFFF">
<table width="105" border="1">
<% While ((Repeat3__numRows <> 0) AND (NOT Recordset3.EOF)) %>
<tr>
<td height="20" bgcolor="#FFFFFF">
<div align="center"></div>
<div align="center"></div>
<div align="center"></div>
<div align="center"><font color="#000000" size="3.5" face="Arial, Helvetica, sans-serif"><%= DoDateTime((Recordset3.Fields.Item("SumOfTimeDiff").Value), 3, 2057) %></font></div>
<div align="center"></div></td>
</tr>
<%
Repeat3__index=Repeat3__index+1
Repeat3__numRows=Repeat3__numRows-1
Recordset3.MoveNext()
Wend
%>
</table></td>
</tr>
</table>
<p align="center"><strong><font size="4"></font></strong></p>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
<%
Recordset2.Close()
Set Recordset2 = Nothing
%>
<%
Recordset3.Close()
Set Recordset3 = Nothing
%>
|
|
