LIKE operator in SQL for my Access Database returns no values in ASP CLASSIC while it does if the query gets copied directly in Access

Question

So as the title says, I'm trying to run a query in my ASP Classic page but for some reason it doesn't return a record set while it does returns a record set it if the query is copied directly in Access.

There is one thing where it probably goes wrong, namely:

• The query uses an LIKE operator on a NUMERIC value.

Obviously the like operator can only be used on STRING values so I tried to cast the numeric value to a string using CStr, this had no effect. Then I tried to just hard code a value in my query and in Access this does seem to work (even though I am using the LIKE operator on a string to find numeric values - it works in Access).

My code is as follows:

Set keywords_cmd = Server.CreateObject ("ADODB.Command")

Set keywords_cmd.ActiveConnection = con
sql = "SELECT Description, MyNumber FROM Orders where MyNumber LIKE '*23*' "

keywords_cmd.CommandText = sql

Set keywords = keywords_cmd.Execute(sql)

if keywords.EOF then
response.write("EOF???")
end if


Do While Not keywords.EOF
    response.write("A record")
%><br> <%       
keywords.movenext
Loop

When pasting the SQL command directly into Access it generates 6 records containing the 23 number. Though when doing the exact same in the ASP file it generates 0 records (EOF = true).

I also checked my connection to the database by adjusting the SQL command to:

sql = "SELECT Description, MyNumber FROM Orders where MyNumber = 1506 "

This generates records in the ASP file so the connection works.

So the question is: why are no records generated while they are using the exact same query in Access directly?

Just a reminder: the column MyNumber is of type NUMERIC in the database.

Some additional information: The Access database is a mdb file (older Access) perhaps this also has something to do with it?

Answer 1

Figured it out myself. Seems that I only had to replace the * wildcards with the % wildcard. This did the trick..

Answer 2

use % in "LIKE" comparisons for wildcards:

"SELECT Description, MyNumber FROM Orders where MyNumber LIKE '%23%'"

Answer 3

use string function Str ( number )

sql = "SELECT Description, MyNumber FROM Orders where Str(MyNumber) LIKE '*23*' "