Invalid Character error (ADO/ASP)

Question

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)
%>

You are mixing up ASP/VBScript (which goes between <% and %> in a .asp file) and Microsoft SQL Server Transact/SQL (which you put into a stored procedure on the SQL server). — webaware

sgeddes sgeddes · Accepted Answer · 2013-01-09T04:55:27

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
%>

Invalid Character error (ADO/ASP)

1 Answers