SQL parameters not returning any results

Question

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

Because you use a parameterised query the type is inferred when you create the parameter so there is no need for the single quotes *(`'`)* around the `search` variable in essence at the moment when the `ADODB.Command` is executed it is doing `WHERE itemname LIKE ''%youritemname%''` when you actually want it to be `WHERE itemname LIKE '%youritemname%'`. Change the `search` variable to `search = "%" & Request.QueryString("itemname") & "%"` to fix it. — user692942, Apr 25 '17 at 22:25
Are you sure you using MySQL as this looks an awful lot like MS SQL Server T-SQL? — user692942, Apr 25 '17 at 22:28

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

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

A: How to use ASP variables in SQL statement (useful tips when working with the ADODB.Command object)

SQL parameters not returning any results

1 Answers1

Useful Links