Missing Operator Error - Snitz™ Forums 2000

The Forum has been Updated

The code has been upgraded to the latest .NET core version. Please check instructions in the Community Announcements about migrating your account.

Missing Operator Error - Posted 12 May 2009 20:49 (968 Views)

Carefree

Advanced Member

Posts: 4224

4224

I'm playing with another script. I get this error:

SELECT * FROM PROGRAM P INNER JOIN SCHEDULE S ON (P.PID=S.PID AND P.DATEID=S.DATEID) WHERE S.PID<23:00 AND S.DATEID=5/12/2009

Here's the script:

Code:

<%@ Language=VBScript%>
<%Response.Buffer = true%>
<%
if Request.Form("insert")="valadd" then
	errMsg=""
	if not isdate(Request.Form("ttime")) then
		errMsg = "Time must be in 18:30 format.<br>"
	end if
	if not isdate(Request.Form("tday")) then
		errMsg = errMsg + "Date must be in 05/12/2009 format."
	end if
	if errMsg>"" then
		Response.Write	errMsg
		%>
		<meta http-equiv="refresh" content="10;url=sport.asp">
		<%
		Response.Flush
		Response.End
	end if
	set my_Conn = Server.CreateObject("ADODB.Connection")
	my_Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("database.mdb")
	errMsg=""
	strSql = "INSERT INTO SCHEDULE (PID,DATEID) VALUES (#" & Request.Form("ttime") & "#, #" & Request.Form("tday") & "#)"
	my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords
	strDescription=Replace(Request.Form("Description"),"'", "''")
	strSql = "INSERT INTO PROGRAM (DATEID, PID, SPORT, DESCRIPTION) VALUES (#" & Request.Form("tday") & "#, #" & Request.Form("ttime") & "#,'" & Request.Form("Sport") & "', '" & strDescription & "')"
	my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords
end if	
if Request.Form("action")="vallookup" then
	errMsg=""
	if not isdate(Request.Form("ttime")) then
		errMsg = "Time must be in 18:30 format.<br>"
	end if
	if not isdate(Request.Form("tday")) then
		errMsg = errMsg + "Date must be in 05/12/2009 format."
	end if
	if errMsg>"" then
		Response.Write	errMsg
		%>
		<meta http-equiv="refresh" content="10;url=sport.asp">
		<%
		Response.Flush
		Response.End
	end if
	set my_Conn = Server.CreateObject("ADODB.Connection")
	my_Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("database.mdb")
	strSql = "SELECT * FROM PROGRAM P INNER JOIN SCHEDULE S ON (P.PID=S.PID AND P.DATEID=S.DATEID) WHERE S.PID<" & Request.Form("ttime") & " AND S.DATEID=" & Request.Form("tday") & ""
	Response.Write	strSql
	set rs=my_Conn.Execute(strSql)
	if not rs.EOF then
		rs.MoveFirst
		do while not rs.EOF
			tmsg = ""
			if rs("Sport")>"" then
				Response.Write	"<tr><td bgcolor=""#EFEFEF"""&rs("pid")&"</td><td bgcolor=""#EFEFEF"">"&rs("Sport")&"</td><td bgcolor=""#EFEFEF"">"&rs("Description")&"</td></tr>"& vbNewLine
			else
				Response.Write	"<tr><td bgcolor=""#FFFFFF""></td><td bgcolor=""#FFFFFF""></td><td bgcolor=""#FFFFFF""></td></tr>" & vbNewLine
			end if	
			rs.MoveNext
		Loop
		rs.Close
	else
		Response.Write "No events."   
	end if
	set rs=Nothing
else
Response.Write	"<form action=""sport.asp"" method=""post"">" & vbNewLine & _
	"	<input name=""insert"" type=""hidden"" value=""valadd"">" & vbNewLine & _
	"	<table width=""80%"" bgcolor=""black"" border=""1"" align=""center"" cellspacing=""2"" cellpadding=""3"">" & vbNewLine & _
	"		<tr valign=""middle"">" & vbNewLine & _
	"			<td colspan=""2"" align=""center"" bgcolor=""cyan"" >" & vbNewLine & _
	"				<font size=""4"" color=""white""><b>Modify Event Date/Time</b>" & vbNewLine & _
	"				</font>" & vbNewLine & _
	"			</td>" & vbNewLine & _
	"		</tr>" & vbNewLine & _
	"		<tr valign=""middle"">" & vbNewLine & _
	"			<td width=""50%"" align=""right"" bgcolor=""skyblue"">Date: " & vbNewLine & _
	"			</td>" & vbNewLine & _
	"			<td width=""50%"" align=""left"" bgcolor=""white"">" & vbNewLine & _
	"				<input type=""date"" name=""tday"" width=""15"" maxlength=""15"">" & vbNewLine & _
	"			</td>" & vbNewline & _
	"		</tr>" & vbNewLine & _
	"		<tr valign=""middle"">" & vbNewLine & _
	"			<td width=""50%"" align=""right"" bgcolor=""skyblue"">Time: " & vbNewLine & _
	"			</td>" & vbNewLine & _
	"			<td width=""50%"" align=""left"" bgcolor=""white"">" & vbNewLine & _
	"				<input type=""time"" name=""ttime"" width=""15"" maxlength=""15"">" & vbNewLine & _
	"			</td>" & vbNewline & _
	"		</tr>" & vbNewLine & _
	"		<tr valign=""middle"">" & vbNewLine & _
	"			<td width=""50%""  align=""right"" bgcolor=""skyblue"">Sport: " & vbNewLine & _
	"			</td>" & vbNewLine & _
	"			<td width=""50%""  align=""left"" bgcolor=""white"">" & vbNewLine & _
	"				<input type=""text"" name=""sport"" width=""50"" maxlength=""50"">" & vbNewLine & _
	"			</td>" & vbNewline & _
	"		</tr>" & vbNewLine & _
	"		<tr valign=""middle"">" & vbNewLine & _
	"			<td width=""50%""  align=""right"" bgcolor=""skyblue"">Description: " & vbNewLine & _
	"			</td>" & vbNewLine & _
	"			<td width=""50%""  align=""left"" bgcolor=""white"">" & vbNewLine & _
	"				<textarea name=""description"" cols=""50"" rows=""20"" wrap=""virtual""></textarea>" & vbNewLine & _
	"			</td>" & vbNewline & _
	"		</tr>" & vbNewLine & _
	"	</table>" & vbNewLine & _
	"	<p align=""center"">" & vbNewLine & _
	"		<input type=""submit"" value=""Submit"">" & vbNewLine & _
	"	</p>" & vbNewLine & _
	"</form>" & vbNewLine
end if
Response.Write	"<form action=""sport.asp"" method=""post"">" & vbNewLine & _
	"	<input name=""action"" type=""hidden"" value=""vallookup"">" & vbNewLine & _
	"	<table width=""80%"" bgcolor=""black"" border=""1"" align=""center"" cellspacing=""2"" cellpadding=""3"">" & vbNewLine & _
	"		<tr valign=""middle"">" & vbNewLine & _
	"			<td colspan=""2"" align=""center"" bgcolor=""cyan"" >" & vbNewLine & _
	"				<font size=""4"" color=""white""><b>Lookup Event Date/Time</b>" & vbNewLine & _
	"				</font>" & vbNewLine & _
	"			</td>" & vbNewLine & _
	"		</tr>" & vbNewLine & _
	"		<tr valign=""middle"">" & vbNewLine & _
	"			<td width=""50%"" align=""right"" bgcolor=""skyblue"">Date: " & vbNewLine & _
	"			</td>" & vbNewLine & _
	"			<td width=""50%"" align=""left"" bgcolor=""white"">" & vbNewLine & _
	"				<input type=""date"" name=""tday"" width=""15"" maxlength=""15"">" & vbNewLine & _
	"			</td>" & vbNewline & _
	"		</tr>" & vbNewLine & _
	"		<tr valign=""middle"">" & vbNewLine & _
	"			<td width=""50%"" align=""right"" bgcolor=""skyblue"">Time: " & vbNewLine & _
	"			</td>" & vbNewLine & _
	"			<td width=""50%"" align=""left"" bgcolor=""white"">" & vbNewLine & _
	"				<input type=""time"" name=""ttime"" width=""15"" maxlength=""15""> " & vbNewLine & _
	"			</td>" & vbNewline & _
	"		</tr>" & vbNewLine & _
	"	</table>" & vbNewLine & _
	"	<p align=""center"">" & vbNewLine & _
	"		<input type=""submit"" value=""Submit"">" & vbNewLine & _
	"	</p>" & vbNewLine & _
	"</form>" & vbNewLine
%>

Here's a sample database with script.

Last edited by Carefree on 12 May 2009, 21:14

Posted 12 May 2009 21:21

ruirib

Snitz Forums Admin

Posts: 26364

26364

Time and date vales should be literal strings, so delimited with single quotes. In Access, dates must be delimited by the # character.
As far as I know, a time value should not be tested by the isdate function...

Snitz 3.4 Readme | Like the support? Support Snitz too

Posted 12 May 2009 21:34

Carefree

Advanced Member

Posts: 4224

4224

The time validation works perfectly, so I'm assuming I did it right LOL.

Changing that line as below gives me a different error

Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

Code:

	strSql = "SELECT * FROM PROGRAM P INNER JOIN SCHEDULE S ON (P.PID=S.PID AND P.DATEID=S.DATEID) WHERE S.PID<'" & Request.Form("ttime") & "' AND S.DATEID='" & Request.Form("tday") & "'"

Posted 13 May 2009 03:51

HuwR

Forum Admin

Posts: 20611

20611

In Access, dates must be delimited by the # character.

Posted 13 May 2009 10:40

Carefree

Advanced Member

Posts: 4224

4224

I figured it out. It wasn't the date/time selection but the time designation in a lower line which caused it's hiccup. I had to use some AS xxx statements when selecting pid/dateid.