ADODB.Recordset error '800a0bb9' Arguments

Question

I've got an old website that is using ASP Classic and I have recently been asked remove the SQL injection attack threat. I'm trying to use parameterized queries, but it's all a little above my head.

here is my code:

<% whatSector = request.querystring("whatSector")%>

    <%  adoCon.Open cString
        dim rs_client
        if whatSector="" then
    strSQL="SELECT * FROM clients ORDER BY alphabet"
    else

    Set objCommand = Server.CreateObject("ADODB.COMMAND")

    strCmd1 = "SELECT * FROM clients Where industrySector=? ORDER BY alphabet"

    Set objCommand.ActiveConnection = adoCon
        objCommand.CommandText = strCmd1
        objCommand.CommandType = adCmdText

    Set param1 = objCommand.CreateParameter ("whatSector",adVarChar, adParamInput, 50)
    param1.value = whatSector
    objCommand.Parameters.Append(param1)
    Set rs_client = objCommand.Execute()

    end if 
    set rs_client = server.CreateObject("ADODB.Recordset")
    rs_client.open strSQL,adoCon

%>

This seemed to work for me on another page (except for some reason I had to remove a recordCount thing I was using for paging), but I'm getting the following error on this page:

ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/clients/clientspotlight_list.asp, line 50

Line 50 - is the rs_client.open at the end of the above code snippet.

I have used

    <!-- METADATA TYPE="TypeLib" NAME="Microsoft ADO Type Library" UUID="{00000205-0000-0010-8000-00AA006D2EA4}" -->

for adovbs.inc.

Answer 1

OK.. problem solved

I moved the last two lines after the end if

set rs_client = server.CreateObject("ADODB.Recordset")
rs_client.open strSQL,adoCon

to above before the ELSE

yes, it was that simple.. a logic mis-flow, pointed out to me by my friend - who read my problem here, and pointed me in the right direction elsewhere ..

Thanks dmarietta :-)

Answer 2

Looks like your parameter names are malformed. Try changing your assignment of strCmd1 to:

strCmd1 = "SELECT * FROM clients Where industrySector=@whatSector ORDER BY alphabet"

Then change the assignment of param1 to:

Set param1 = objCommand.CreateParameter ("@whatSector",adVarChar, adParamInput, 50)