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 operatoron a
like operator can only be used on
STRING values so I tried to cast the
numeric value to a
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?