CODEWITHSUNDEEP
sql-servervbscriptparametersasp-classicadodb
rem
rem

Reputation: 85

SQL parameters not returning any results

Following code is not returning any results:

search = "'%" & Request.QueryString("itemname") & "%'"

Set cmd = Server.CreateObject("ADODB.COMMAND")  
Set cmd.ActiveConnection = conn

sql = ""
sql = sql & "DECLARE @search varchar;"
sql = sql & "SET @search = ?;"

sql = sql & "SELECT ID, itemname, itemtype FROM vw_items WHERE itemname LIKE @search"

    cmd.CommandText = sql
    cmd.CommandType = adCmdText

response.write("<strong>You searched for:</strong> " & search & "<br /><br 
/>")    

cmd.Parameters.Append cmd.CreateParameter("@search", adVarchar, adParamInput, 50, search)

set rs = cmd.Execute
else

    search = null
    strSQL2 =   "SELECT * FROM vw_items"
    set rs = server.CreateObject("ADODB.Recordset")
    rs.open strSQL2,conn

end if

I've seen this answer: ADO parameterised query not returning any result and tried fixing mine but no luck

Any help would be much appreciated

Upvotes: 2

Views: 165

Answers (1)

user692942
user692942

Reputation: 16671

When using ADODB.Command the provider infers the data type based off the Parameters collection that is setup before calling the Execute() method.

As you are passing in as

search = "'%" & Request.QueryString("itemname") & "%'"

in effect when the SQL is executed by the provider it will look like (because it already knows the Parameter is a VARCHAR data type already)

WHERE itemname LIKE ''%youritemname%''

When you actually want it to be

WHERE itemname LIKE '%youritemname%'

This means the current query is doing a LIKE for the physical string '%youritemname%' rather than doing an actually pattern matching query.

The fix is simple, remove the single quotes from the search variable, like so;

search = "%" & Request.QueryString("itemname") & "%"

Useful Links

Upvotes: 2

Related Questions