VBScript error 0x800A03EA Syntax error

edbkegan3 · March 25th, 2004, 09:41 AM

Trying to do a simple search page that connects to one .mdb file (so far -- plan on looking up two), but am getting stuck at one part:

The info provided by IE:
Error Type:
Microsoft VBScript compilation (0x800A03EA)
Syntax error
/Spec_Apps/apps5.asp, line 45
Else

Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; xxx)

Page:
POST 11 bytes to /Spec_Apps/apps5.asp

POST Data:
search=test

IIS version 5.1

Here's the code to my apps5.asp page:
<html>
<body>

<form action="apps5.asp" method="post">

Search for: <input type="text" name="search" size="125"> <br>
<input type="submit" value="Get it now">

<%
'Option Explicit
'declare your variables
dim connection, recordset
dim SQL, sConnString
dim busq

set recordset = 0

'declare SQL statement that will query the database
busq = Request.Form("search")
SQL = "SELECT Name,Acronym,Field3,Contact1,Contact2 FROM App WHERE Name LIKE '%" & busq & "%' OR Acronym LIKE '%" & busq & "%';"

Response.Write("<br> <br>Test: <b>" & busq & "</b> is in SQL statement: <b>" & SQL & "</b><br>")

'define the connection string, specify database
'driver and the location of database
sConnString="DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & Server.MapPath("apps.mdb") & ";"

'create an ADO connection and recordset
Set connection = Server.CreateObject("ADODB.Connection")

'Open the connection to the database
connection.Open sConnString

Set recordset = Server.CreateObject("ADODB.RecordSet")
recordset.Open SQL,connection,adOpenDynamic, adLockOptimistic

If recordset=0 Then Response.Write("<tr><td colspan=5>Nothing</td></tr>")
Else
  'create table
  Response.Write("<table width = 95%>")
  Response.Write("<tr>")
  Response.Write("<td>Name</td>")
  Response.Write("<td>Other names</td>")
  Response.Write("<td>IC's that use it</td>")
  Response.Write("<td>Tier 1</td>")
  Response.Write("<td>Tier 2</td>")
  Response.Write("</tr>")
   'test if there are any matches
   If recordset.EOF Then Response.Write("<tr><td colspan=5>No apps found</td></tr>")
   Else
     Do While Not recordset.EOF
       Response.Write("<tr>")
       Response.Write("<td>" & recordset("Name") & "</td>)
       Response.Write("<td>" & recordset("Acronym") & "</td>)
       Response.Write("<td>" & recordset("Field3") & "</td>)
       Response.Write("<td>" & recordset("Contact1") & "</td>)
       Response.Write("<td>" & recordset("Contact2") & "</td>)
       Response.Write("</tr>")
       recordset.MoveNext
     Loop
     Response.Write("</table>")
   End If
End If

'close the connection and recordset objects and free up resources
Recordset.Close
Connection.Close
Set Recordset = Nothing
Set Connection = Nothing
%>

</body>
</html>

Please help
-Ed

U.N.C.L.E. · March 25th, 2004, 12:29 PM

You can try putting Response.Write(...) on a separate line.

I think some constants are undefined... They are usually defined in a file called adovbs.inc.

If you don't have this file then you can replace the keywords with their numeric equivalents.

adCmdText = 1
adOpenDynamic = 2
adOpenForwardOnly = 0 ' More efficient reading successive records
adLockOptimistic = 3

e.g.
recordset.Open SQL,connection, 0, 3, 1

edbkegan3 · March 29th, 2004, 01:57 PM

Thanks for the help ... it looks like it was the RecordSet options "adOpenDynamic" and "adLockOptimistic" causing the problems, so I just took them out.

Also noted several other problems with my code -- I need to be a little bit more meticulous:
I had a percent sign in the "Response.Write("<table width = 95%>") which caused the end of the VB Script.
Moved the Response.Writes down to the next line
Missing end quote marks at the end of each Response.Write ("<td> " & ... & "</td>)
Updated MS Jet 4.0 SP8, just in case ;)

Now my code is working -- just gotta pretty up the HTML code.

Again, thanks for the help, UNCLE ... I was pulling out what little hair I have left.