error on basic asp

kobalt7 · May 14th, 2004, 01:57 PM

keep getting error on line 16, and i dont know why

<%@Language="VBScript"%>
<%
    Option Explicit

    Dim oRS, oConn, strSQL
    Dim i
    Dim results

    Set oConn = Server.CreateObject("ADODB.Connection")
    Set oRS = Server.CreateObject("ADODB.Recordset")

    oConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("AddressBook.mdb")

If Request.QueryString("var")="all" Then strSQL="select ContactID, Movie, Result ORDER BY Result, Movie asc"
Else
If Request.QueryString("var")="film" Then strSQL="select ContactID, Movie, Result WHERE Result = '"film"' ORDER BY Movie asc"

else
if Request.QueryString("var")="tv" then strSQL="select ContactID, Movie, Result WHERE Result = '"tv"' ORDER BY Movie asc"

else
if Request.QueryString("var")="portait" then strSQL="select ContactID, Movie, Result WHERE Result = '"portait"' ORDER BY Movie asc"

end if
end if
end if
end if

    oRS.Open strSQL, oConn, 2, 3

    If oRS.EOF Then
        Response.Write "success=False"
    Else
        Do While Not oRS.EOF
            results = results & oRS("Movie") & " " & oRS("Result") & ";"
        oRS.MoveNext
        Loop
        results = results & "q"
        Response.Write "success=True&results="

    End If

    oRS.Close
    Set oRS = Nothing
    oConn.Close
    Set oConn = Nothing
    Response.Write Server.URLEncode(results)
%>

U.N.C.L.E. · May 14th, 2004, 09:05 PM

This looks like the closest to line 16:

strSQL="select ContactID, Movie, Result ORDER BY Result, Movie asc"

You have not specified FROM where?

SELECT ContactID, Movie, Result FROM myTable ...

bmains · May 15th, 2004, 10:12 PM

In fact, none of your SQL strings have the from clause...

kobalt7 · May 18th, 2004, 03:10 AM

i put in FROM pk_database for the table and i still get the same dealWH
<%@Language="VBScript"%>
<%
    Option Explicit

    Dim oRS, oConn, strSQL
    Dim i
    Dim results

    Set oConn = Server.CreateObject("ADODB.Connection")
    Set oRS = Server.CreateObject("ADODB.Recordset")

    oConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("AddressBook.mdb")

If Request.QueryString("var")="all" Then strSQL="select * FROM pk_database ORDER BY Result, Movie asc"
Else
If Request.QueryString("var")="film" Then strSQL="select * FROM pk_database WHERE Result = '"film"' ORDER BY Movie asc"

Else
if Request.QueryString("var")="tv" then strSQL="select * FROM pk_database WHERE Result = '"tv"' ORDER BY Movie asc"

Else
if Request.QueryString("var")="portait" then strSQL="select ContactID, Result FROM pk_database WHERE Result = '"portait"' ORDER BY Movie asc"

end if
end if
end if
end if

    oRS.Open strSQL, oConn, 2, 3

    If oRS.EOF Then
        Response.Write "success=False"
    Else
        Do While Not oRS.EOF
            results = results & oRS("Movie") & " " & oRS("Result") & ";"
        oRS.MoveNext
        Loop
        results = results & "q"
        Response.Write "success=True&results="

    End If

    oRS.Close
    Set oRS = Nothing
    oConn.Close
    Set oConn = Nothing
    Response.Write Server.URLEncode(results)
%>

bmains · May 18th, 2004, 08:30 AM

Hello,

This select statement:
select * FROM pk_database WHERE Result = '"film"' ORDER BY Movie asc"

The film part is a problem, because it assumes film is a variable. If it is, it needs to be referenced as '" & film & "', but if it's not, it needs to be 'film'. I noticed this in a couple SQL strings.

Brian

kobalt7 · May 18th, 2004, 10:29 AM

that doesnt seem to get rid of the error

bmains · May 18th, 2004, 01:35 PM

Hello,

What is the exact error message that you are getting, and could you verify which line is the line that you are getting the error on?

Thanks,

Brian

Snib · May 18th, 2004, 07:40 PM

I believe you are referencing the DB instead of the table with your FROM clause.

This:

SELECT * FROM pk_database

Should be:

SELECT * FROM your_table

HTH,

Snib

<><

kobalt7 · May 18th, 2004, 09:10 PM

Microsoft VBScript compilation error '800a03ea'

Syntax error

/pk/first.asp, line 15

Else
is the error message im getting, its pointed to the table in the mdb file so i know thats not it

happygv · May 18th, 2004, 11:58 PM

kobalt7,

Code:

strSQL="select ContactID, Movie, Result WHERE Result = '"film"' ORDER BY Movie asc"

What are you trying to do there? Where is the & operator for concatenation?
As per you statement,

Code:

"select ContactID, Movie, Result WHERE Result = '"

--- is one string

Code:

"' ORDER BY Movie asc"

--- is another string

What does film in between got to do is the question there.

Either make it this way (If film is a string constant)

Code:

strSQL="select ContactID, Movie, Result WHERE Result = 'film' ORDER BY Movie asc"

Or like this (If film is a variable)

Code:

strSQL="select ContactID, Movie, Result WHERE Result = '" & film& "' ORDER BY Movie asc"

NOTE: As per your variable definitions I dont see FILM as a variable, so the first one should work.
Change it for the FILM statement alone and see if the ERROR line number changes. If so go ahead with the same changes in the lines followed.

One suggestion, dont use so many IFs and get confused. I would suggest you to go for a SELECT... CASE... construct, if you see values varying more than twice.

OR

Code:

strVar=Request.QueryString("var")

If strVar="all" Then strSQL="select ContactID, Movie, Result ORDER BY Result, Movie asc"

If strVar="film" Then strSQL="select ContactID, Movie, Result WHERE Result = 'film' ORDER BY Movie asc"

If strVar="tv" Then strSQL="select ContactID, Movie, Result WHERE Result = 'tv' ORDER BY Movie asc"

If strVar="portrait" Then strSQL="select ContactID, Movie, Result WHERE Result = 'portrait' ORDER BY Movie asc"

This should do.

When THEN is given in the same line as of IF, you dont have to close that with END IF. Also at any point of time Request.QueryString("var") is going to have any 1 of these four values, so you dont need ELSE at all.

Also keep you code aligned for easy debugging.

Hope that Helps.
Cheers!

-Vijay G