l00ny_tn1
asked on
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 .Connectio n")
curDir = Server.MapPath("db\mymdb.m db")
Conn.Open "DBQ="& curDir &";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=M S 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
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
Set conn = Session("db/mymdb.mdb_conn
Else
Set conn = Server.CreateObject("ADODB
curDir = Server.MapPath("db\mymdb.m
Conn.Open "DBQ="& curDir &";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=M
Set Session("db/mymdb.mdb_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
rs.Open sql, conn, 3, 3
%>
Using the above rs is empty. What do I do wrong?
Can anybody help? Thanks!
Regards
Gero
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 :)