Partial keyword search using sql in ASP

Hello,

I have a problem with ASP and a MSACCESS database. I want to execute a query that selects all products that _begin_ with lets say 'COLA'.
Now when I execute that query under ACCESS with the following sql-statement

SELECT PRODUCTS.PRODUCTNAME
FROM PRODUCTS
WHERE (((PRODUCTS.PRODUCTNAME) Like 'COLA*'));

the output is

COLA-PEPSI
COLA-COKE

But when I try that with ASP the dataset is empty:

<%
If IsObject(Session("db/mymdb.mdb_conn")) Then
    Set conn = Session("db/mymdb.mdb_conn")
Else
    Set conn = Server.CreateObject("ADODB.Connection")
    curDir = Server.MapPath("db\mymdb.mdb")
    Conn.Open "DBQ="& curDir &";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;password=xyz"
    Set Session("db/mymdb.mdb_conn") = conn
End If
%>
<%
    sql = "SELECT PRODUCTS.PRODUCTNAME  FROM PRODUCTS  WHERE (((PRODUCTS.PRODUCTNAME) Like 'COLA*'))   "
    If cstr(Param) <> "" And cstr(Data) <> "" Then
        sql = sql & " And [" & cstr(Param) & "] = " & cstr(Data)
    End If
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open sql, conn, 3, 3
%>

Using the above rs is empty. What do I do wrong?
Can anybody help? Thanks!

Regards
Gero
l00ny_tn1Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
clockwatcherConnect With a Mentor Commented:
Try the '%' symbol:

 ([PRODUCTS].[PRODUCTNAME] Like 'COLA%')  
0
 
l00ny_tn1Author Commented:
Thanks!
Hm, interesting that the asterisk does not work...
MS says "The asterisk (*) character matches zero or more characters" ... but perhaps sql in ASP is ANSI-SQL and in ACCESS MS-SQL :)

0
All Courses

From novice to tech pro — start learning today.