LIKE clause in Paramatized query (from ASP)

[cash0]

Damn it! I solved my own problem before I got through typing it out. I thought I'd better post it anyway in case somebody else had the same problem.

I solved it by using % instead of * in the LIKE clause.The original question is below...

------------------------------------------

Hi I've got a problem passing parameters to an Access2000 paramatized query from ASP.

I got the following code from another post and it seems to work as long as the WHERE statement uses = [ ] rather than LIKE *[ ]*. It simply returns no results. But when I run the same query from Access, it returns what it should.

The asp code:
-------------------------------

Dim rs
Dim cmd
Set cmd = Server.CreateObject("ADODB.Command&quot
Set rs = Server.CreateObject("ADODB.Recordset&quot

With cmd
.ActiveConnection = Application("dbConnectionString&quot
.CommandText = "qtest2"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("empName", adVarChar, adParamInput, 50)
.Parameters("empName&quot.value = "joe bloggs"
End With
rs.Open cmd, , adOpenStatic, adLockOptimistic

----------------------------

The Access query that works:

SELECT EmployeeNumber, EmployeeName
FROM tCV
WHERE EmployeeName=[empName];


The Access query that I want to use but doesn't return anything:

SELECT EmployeeNumber, EmployeeName
FROM tCV
WHERE EmployeeName Like "*" & [empName] & "*";

 

[cmmrfrds]

The wildcard can be confusing since it is more ANSI compliant in ADO than it is in Jet and DAO. If you are in an Access MDB and running code the wildcard will be * since it is an Access aberration as opposed to the ANSI % wildcard. Microsoft has made ADO more in line with ANSI standards like those used in SQL Server. So, if you are structuring the query in ADO use the % wildcard. If structuring a query in Access use the * wildcard. Hope this sheds some light on the confusion.