CODEWITHSUNDEEP
sqlasp-classicado
sephiith
sephiith

Reputation: 1197

Invalid Character error (ADO/ASP)

I am trying to use a variable as the column field in the SQL. However when I run the code I get the following error: 

Microsoft VBScript compilation error '800a0408'

Invalid character

/junk/dbresults.htm, line 25

DECLARE @cat char(20)
--------^

The code is below:

<%
    Dim connectString, connect, conDB, con
    connectString = "Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=" & Server.MapPath("data")
          src_abn = Request.QueryString("abn")
          src_cat = Request.QueryString("cat")
    set connect = Server.CreateObject("ADODB.connection")
    connect.open connectString

    DECLARE @cat char(20)
    DECLARE @dynamicsql char(1000)

    SET @cat = src_cat;
    SET @dynamicsql = "SELECT * FROM cont.csv WHERE @cat='Yes'"

    if src_abn = "all" then
    conDB = EXEC (@DynamicSQL)  
    else
    conDB = "SELECT * FROM cont.csv WHERE ucase(abn) LIKE ucase('%"+src_abn+"%')"   
    end if

    set con = connect.execute(conDB)
%>

Upvotes: 1

Views: 826

Answers (1)

sgeddes
sgeddes

Reputation: 62831

I'm not positive what you're trying to do, but you can't run SQL Syntax (i.e. DECLARE @cat...) with classic ASP. Are you trying to do something like this?

<%
    //Request your variables
    src_abn = Request.QueryString("abn")
    src_cat = Request.QueryString("cat")

    Dim connectionString, dbConn, rsResults, strSQL

    //Setup your connection
    connectionString = "Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=" & Server.MapPath("data")
    set dbConn= Server.CreateObject("ADODB.Connection")
    dbConn.open connectionString

    //Create your sql statement
    if src_abn = "all" then
        strSQL = "SELECT * FROM cont.csv WHERE " & src_cat & "='Yes'"
    else
        strSQL = "SELECT * FROM cont.csv WHERE ucase(abn) LIKE ucase('%" & src_abn & "%')"   
    end if

    //Create your results
    set rsResults = Server.CreateObject("ADODB.Recordset")
    rsResults.open strSQL, dbConn

    //Loop through your results
    While Not rsResults.EOF

        rsResults.MoveNext
    Wend

    //Closes results and connection
    rsResults.Close
    dbConn.Close
%>

Upvotes: 2

Related Questions