[thelist] Syntax Error Message in SQL Statement for Access Database

Daniel S. O'Shea doshea at surfree.com
Mon Jun 18 21:57:07 CDT 2001


I am trying to get hyperlinks on all my column headings and am trying to use
the SQL script article from this site. I am using an access database so I
must alter the syntax a bit. I am getting an error message on my IF and THEN
line-

Error Type:
Microsoft VBScript compilation (0x800A03EA)
Syntax error
/football_Local/players1.asp, line 121
if strOrder <> "" then

I believe this may be an access syntax problem but don't know how to correct
it. Can anyone help?? Below is the entire statement that I built from the
article on this site. Thanks.


<%
dim cnn1
dim objRec
dim sql
dim strOrder
Dim varfullname

strOrder = Request.QueryString("Order")

Set cnn1 = Server.CreateObject("ADODB.Connection")
openStr = "driver={Microsoft Access Driver (*.mdb)};" & _
"dbq=" & Server.MapPath("2000/stats2000.mdb")
cnn1.Open openStr,"",""

sql = "SELECT QB.fullname, " & _
"QB.pos, " & _
"QB.team, " & _
"QB.games, " & _
"QB.passatt, " & _
"QB.passcom, " & _
"QB.passyards, " & _
"QB.passtd, " & _
"QB.avgpass, " & _
"QB.yards, " & _
"QB.rushatt, " & _
"QB.rushyards, " & _
"QB.rushtd, " & _
"QB.avgrush, " & _
"QB.fumnum, " & _
"QB.fumlost, " & _
"QB.intthrown, " & _
"QB.sack, " & _
"QB.rushconv, " & _
"QB.passconv, " & _
"QB.catchconv " & _
"FROM QB " & _

if strOrder <> "" then
sql = sql & " ORDER BY " & strOrder
end if

Set objRec = Server.CreateObject("ADODB.Recordset")
objRec.Open sql, cnn1, adOpenStatic, adLockReadOnly, adCmdText

if objRec.EOF then
'if there are no records display a message and stop
'processing the page
Response.Write "No records available"
Response.End
end if

Response.Write "<td align=center><a href=""" & _
"SortColumns.asp?Order=team"">" & _
"Team</a></td>"
Response.Write "<td align=center><a href=""" & _
"SortColumns.asp?Order=games"">" & _
"Games</a></td>"
Response.Write "<td align=center><a href=""" & _
"SortColumns.asp?Order=passatt"">" & _
"PA</a></td>"
Response.Write "<td align=center><a href=""" & _
"SortColumns.asp?Order=passcom"">" & _
"PC</a></td>"
Response.Write "<td align=center><a href=""" & _
"SortColumns.asp?Order=passyards"">" & _
"PY</a></td>"
Response.Write "<td align=center><a href=""" & _
"SortColumns.asp?Order=passtd"">" & _
"PTD</a></td>"
Response.Write "<td align=center><a href=""" & _
"SortColumns.asp?Order=intthrown"">" & _
"Int</a></td>"
Response.Write "</tr>"


while not objRec.EOF
Response.Write "<tr>"
Response.Write "<td>" & objRec("team") & "</td>"
Response.Write "<td>" & objRec("games") & "</td>"
Response.Write "<td>" & objRec("passatt") & "</td>"
Response.Write "<td>" & objRec("passcom") & "</td>"
Response.Write "<td>" & objRec("passyards") & "</td>"
Response.Write "<td>" & objRec("passtd") & "</td>"
Response.Write "<td>" & objRec("intthrown") & "</td>"
Response.Write "</tr>"
objRec.MoveNext
wend
Response.Write "</table>"

'clean up
objRec.Close
set objRec = nothing

cnn1.Close
set cnn1 = Nothing
%>
</BODY>
</HTML>





More information about the thelist mailing list