LIKE: works in Access, but not ASP/SQL

[ahmun]

Has anybody tried using the LIKE statement in an ASP file running in IIS 5?

I cannot seem to get it to work. Any Ideas?

Here is the lo-down:
--*Conditions*--
1. I am running windows 2000, with IIS5.
2. Using an Access 2000 mdb as the backend database
3. Accessing the mdb in ASP using SQL query

--*Observations*--
1. The query always returns an empty set.
2. I've changed the WHERE clause to say "NOT LIKE", and the asp file will lock up (as if in an infinite loop?)

the following code is a snippet of what I had in my ASP file (the SQL query in the example below is identical to what I used in Access 2000 where it runs fine.

<%@ LANGUAGE=&quot;VBSCRIPT&quot; %>

<%
  Set myConnection = Server.CreateObject(&quot;ADODB.Connection&quot;)
  set rsRecordSet = server.createObject(&quot;ADODB.Recordset&quot;)
  dbFilePath = Server.MapPath(&quot;..\Database\JHA.mdb&quot;)
  myConnection.Open &quot;Driver={Microsoft Access Driver (*.mdb)}; DBQ=&quot; & dbFilePath & &quot;;&quot;

  sSQL = &quot;SELECT tblCategory.intCategoryID as CatID, tblCategory.strCategoryName as CatName &quot;
  sSQL = sSQL & &quot;FROM tblCategory &quot;
  sSQL = sSQL & &quot;WHERE (((tblCategory.strCategoryName) Like '*sc*'));&quot;
  rsRecordSet.open sSQL, myConnection, 2, 3

I use this if statement to test for an empty recordset

  if rsRecordSet.EOF then
    response.write "empty recordset"
  end if

  do while not rsRecordSet.EOF
    response.write &quot;<p>&quot; & rsRecordSet(&quot;CatID&quot;) & &quot;: &quot; & rsRecordSet(&quot;CatName&quot;) & &quot;</p>&quot;
  loop
%>

Earnie Eng - Newbie Access Programmer/DBA
If you are born once, you will die twice.
If you are born twice, you will die once

 

[JuanitaC]

SQL Server uses % as a wild card, not * as Access does. Change your SQL statement to look like this:

sSQL = "SELECT TC.intCategoryID as CatID, TC.strCategoryName as CatName "
sSQL = sSQL & "FROM tblCategory TC "
sSQL = sSQL & "WHERE TC.strCategoryName Like '%sc%'"
rsRecordSet.open sSQL, myConnection, 2, 3

 

[ahmun]

MAN that hit the spot!

I've been frustrated with this problem for the past two days.. and it turns out to be a simple syntax issue!!!

Thanks a lot, Juanita! Earnie Eng - Newbie Access Programmer/DBA
If you are born once, you will die twice.
If you are born twice, you will die once