Syntax error (Missing Operator) in query ...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Syntax error (Missing Operator) in query ...

Author

Topic

yabud
Starting Member

6 Posts

Posted - 2009-01-02 : 12:21:20

Hey people,

OK, this has been working perfectly until one day it just stopped and starting giving me this error:javascript:showcode();

Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression 'teacherid= AND timeslotid='.

bookdate.asp, line 59

I have not changed a single thing from when it worked, here is the code below... any help is greatly appreciated!

<HTML>
<HEAD>
<TITLE>Appointment Booking</TITLE>
<LINK REL=StyleSheet HREF="/sjkstylesheet.css" TYPE="text/css">
<STYLE>
.borderimage{
border:1px solid white;
}
</STYLE>
<SCRIPT LANGUAGE="JavaScript1.2">
function borderit(which,color){
//if IE 4+ or NS 6+
if (document.all||document.getElementById){
which.style.borderColor=color
}
}
</SCRIPT>
<META NAME="Description" CONTENT="<%=pagetitle%>">
</HEAD>
<BODY>
<CENTER>
<span class="titlebar">Appointment Booking</span><br>
<br><%
Function formatSQLDate(dateToFormat)
	Dim aDay
	Dim aMonth
	Dim aYear

	aDay = Day(dateToFormat)
	aMonth = Monthname(Month(dateToFormat),True)
	aYear = Year(dateToFormat)

	formatSQLDate= aDay & "-" & aMonth & "-" & aYear
End function

dateid=request.form("dateid")
teacherid=request.form("teacherid")
teachername=request.form("teachername")%>
<b>Schedule for <%=teachername%> on <%=formatdatetime(dateid,1)%></b>
<br>
<table border="2" width="80%">
<tr><th>Status</th><th>Start Time</th><th>End Time</th><th>Student Name</th><th>Email</th><th>IP Address</th><th>When Added</th></tr>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.provider="Microsoft.Jet.OLEDB.4.0"
conn.open("c:\inetpub\databases\appointment.mdb")
set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM timeslots WHERE timeslots.date=#" & formatSQLDate(dateid) & "# ORDER BY timeslots.starttime ASC"
rs.open sql,conn,1

do until rs.EOF
for each x in rs.Fields
	if x.name="id" then id=x.value
	if x.name="starttime" then starttime=x.value
	if x.name="endtime" then
		endtime=x.value
		set rs2 = Server.CreateObject("ADODB.Recordset")
		sql2 = "SELECT * FROM appointments WHERE teacherid=" & teachersid & " AND timeslotid=" & id
		rs2.open sql2,conn,1
		if rs2.recordcount <> 0 then
			for each y in rs2.Fields
				if y.name="student" then%>
					<form method="post" action="delete.asp">
					<input type="hidden" name="teacherid" value="<%=teacherid%>">
					<input type="hidden" name="timeslotid" value="<%=id%>">
					<input type="hidden" name="dateid" value="<%=dateid%>">
					<input type="hidden" name="teachername" value="<%=teachername%>">
					<tr>
					<td><input type="submit" value="Delete"></td>
					</form>
					<td><%=formatdatetime(starttime,3)%></td>
					<td><%=formatdatetime(endtime,3)%></td>
					<td><%=y.value%></td><%	
				end if
				if y.name="emailaddr" then%>
					<td><%=y.value%></td><%
				end if
				if y.name="ip" then%>
					<td><%=y.value%></td><%
				end if
				if y.name="whenadded" then%>
					<td><%=y.value%></td></tr><%
				end if
			next
		else%>
			<form method="post" action="booktime.asp">
			<input type="hidden" name="teacherid" value="<%=teacherid%>">
			<input type="hidden" name="timeslotid" value="<%=id%>">
			<input type="hidden" name="dateid" value="<%=dateid%>">
			<input type="hidden" name="teachername" value="<%=teachername%>">
			<tr>
			<td><input type="submit" value="Available"></td>
			</form>
			<td><%=formatdatetime(starttime,3)%></td>
			<td><%=formatdatetime(endtime,3)%></td>
			<td> </td>
			<td> </td>
			<td> </td>
			<td> </td>
			</tr><%	
		end if
		rs2.close
		set rs2=nothing
		set sql2=nothing		
	end if
next
rs.MoveNext
loop
rs.close
set rs=nothing
conn.close
set conn=nothing%>
</table><br>
Please click an "Available" button above or you can: <br><br>
<form method="post" action="bookteacher.asp">
<input type="hidden" name="teacherid" value="<%=request.form("teacherid")%>">
<input type="hidden" name="teachername" value="<%=teachername%>">
<input type="submit" value="Choose a different Date">
</form>
</CENTER>
</BODY>
</HTML>

Andrew Dusz.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-02 : 12:23:36

Is a NULL being passed to one of them?

Also, your code is vulnerable to sql injection as you aren't using parameterized queries. You shouldn't concatenate your query string together like that. There is plenty of information on the Internet about this, including in my blog and others here.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-02 : 12:25:11

'teachersid' and 'timeslotid' are probably not getting assigned a value in your form.

yabud
Starting Member

6 Posts

Posted - 2009-01-02 : 12:55:42

Yeah thats what it seems like with the whole '

teacherd= AND timeslotid='

I just cant figure out what would have changed from when it worked, hmm...

Andrew Dusz.

yabud
Starting Member

6 Posts

Posted - 2009-01-02 : 13:17:20

I tried hardcoding the values for teachersid AND timeslotid in the statement:

sql2 = "SELECT * FROM appointments WHERE teacherid=" & teachersid & " AND timeslotid=" & id

and it worked fine, clearly I need to be able to grab those numbers from the table though...

Any other ideas?

Andrew Dusz.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-01-02 : 13:26:20

try printing the timeslot id and teacher id before you create sql2 query

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-02 : 13:34:25

And don't forget to switch to parameterized queries!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

yabud
Starting Member

6 Posts

Posted - 2009-01-02 : 13:35:48

I can print the teacherid BUT NOT THE timeslotid before the query...

Andrew Dusz.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-01-02 : 14:04:54

before SQL2?

surely one of the parameters (timeslotid and teachersid) is missing, you have to figure out which one.
teacherid is user entered and timeslot id comes from table [timeslots].

yabud
Starting Member

6 Posts

Posted - 2009-01-02 : 14:16:48

Im pretty sure its the "timeslotid" and it should be grabbed from the table here:

sql = "SELECT * FROM timeslots WHERE timeslots.date=#" & formatSQLDate(dateid) & "# ORDER BY timeslots.starttime ASC"
rs.open sql,conn,1

do until rs.EOF
for each x in rs.Fields
if x.name="id" then id=x.value
if x.name="starttime" then starttime=x.value
if x.name="endtime" then
endtime=x.value
set rs2 = Server.CreateObject("ADODB.Recordset")
sql2 = "SELECT * FROM appointments WHERE teacherid=" & teacherid & " AND timeslotid=" & id
rs2.open sql2,conn,1

But it doesnt seem to be able to see it? Or something? The timeslots table definetely has an "id" column, which is a list of a numbers from 1 to whatever.

Andrew Dusz.

yabud
Starting Member

6 Posts

Posted - 2009-01-02 : 14:21:57

OK, dont know what I did but it seems to be working once again.

Thanks everyone.

Andrew Dusz.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources