ASP SQL Statement Problem using wildcards (*) 2

[nvwildfire]

Hey list, I've got a problem that is just killing me. I am using ASP 3.0 and connecting with an access 2000 database with ADO. The problem is in my SQL statement. I know this because I am not getting any records returned (i.e. objRS.EOF and objRS.BOF). Here is the SQL statement:

strSQLS = "SELECT tblCache.NFES, tblCache.Description FROM tblCache "
strSQLS = strSQLS & "WHERE ((Description) like '*' & 'jean' & '*')"

Here is the ADO Request:
objRsSearch strSQLS, objConn

If I copy this statement straight into the sql view of a query, 23 records are returned. Just to check I changed the WHERE clause to NFES = 141 and it works fine via ASP (the NFES field is a number field). I am sure it has something to do with the single quotes (') or maybe its the wildcard (*) I don't know. I have also used response.write to print the strSQLS statement to the browser to see if anything weird is coming up and it looks fine (also have pasted the results of the response.write into the access sql view and it works fine).

Anyway any help would be greatly appreciated.

Thank you in advance

 

[onpnt]

the like wild is %
try replacing the * with that and see how it goes

_____________________________________________________________________

[sub]
Hakuna matata!![/sub]

 

[Funka]

in addition to wanting to use the '%' symbol for the SQL wildcard as was suggested, you'll also want to look into how your sql string is being built...

this line:
strSQLS = "WHERE ((Description) like '*' & 'jean' & '*')"

is interesting in that i think it does not do what you want, and looking at this string after it is built, you will most likely see this output:

WHERE ((Description) like '*' & 'jean' & '*')

which is not valid SQL. you probably want to see this instead:
WHERE [Description] LIKE '%jean%'

(note that removing the parentheses and putting brackets around the tablename is not what i wanted to highlight, but rather the "like" clause syntax i wanted to point out that might be causing you grief...)

The ampersand (&) is the VBScript concatenation operator, and is not understood in SQL. To concatenate several smaller strings together into one larger string, you will want to do this concatenation in VBScript, not SQL, which means you use the regular quote mark instead of the tick mark. For example,

strSQLS = "SELECT tblCache.NFES, tblCache.Description FROM tblCache "
strSQLS = strSQLS & "WHERE ((Description) like '%" & myVariable & "%')"
Response.Write "Here is the SQL statement: "
Response.Write strSQLS

Note that a good way to debug recordset and SQL problems in general is to add in a line such as Response.Write &quot;<p>SQL: &quot; & strSQLS & &quot;</p>&quot; anytime you are creating a dynamic SQL string, so you can see the exact SQL command you are trying to pass to the database. just leave the line in there and comment it out when you're ready for productoin. this will catch many unexpected errors in various situations throughout your programming career!

best of luck!
-f!

 

[shaddow]

The diference here is the way of creating your connection string.
If you use the Jet OLEDB (Jet.OLEDB.4.0 or 3.5) driver then you would need to use &quot;*&quot;
If you use the ODBC Driver (Driver=Microsoft Access Driver(*.mdb)) then you have to use SQL like sintax with &quot;%&quot;.

So there i presume you are using an ODBC connection to that database since it requires &quot;%&quot;

________
George, M

 

[nvwildfire]

Thanks for the input everyone. I'll try using brakets around my field name in the WHERE clause and I'll also add the % instead of *.

Shaddow,
I am connecting to access with the Jet OLEDB provider.

I won't be able to try this until monday when I am back at work.

kgk

 

[nvwildfire]

Thank you onpnt and funka!!!! That was my problem, needed to use a '%' instead of '*'.

kgk

 

[nvwildfire]

Just a note to others. When passing wildcards in an sql statement while connecting to MS access with ADO use &quot;%&quot; as your wildcard character. When using wildcards in MS access use &quot;*&quot; as your wildcard character. It took the help of others and many frustrated hours to figure this out.

Thank you for everyones help.

kgk