SitePoint Sponsor |
|
User Tag List
Results 1 to 3 of 3
Thread: Searching problem now!
-
Sep 20, 2002, 14:25 #1
- Join Date
- Jul 2002
- Location
- South-West UK
- Posts
- 100
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Searching problem now!
I'm getting the following error when I perform a search on a database:
[VBS]Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'aside like '%%it's not over%%''.
/[/VBS] it seems that the search code doesn't like it if I enter something with a "'" in the words (as seen by the error above) and if I leave out the "'", everything is fine.
Here's the code that performs the search:[VBS]u_input = trim(request.form("u_input")) 'u_input is the varible for user input
' the trim function removes any blanks around the the use input
u_field=request.form("u_field") 'the dropdown varible for user inputted search field
if u_input <> "" then ' If the user entered a value query the db
accessdb="db/stock" ' name of the access db
table_name="items" ' name of the table within the access db
cn="driver={microsoft access driver (*.mdb)};"
cn=cn & "dbq=" & server.mappath(accessdb)
set rs = server.createobject("ADODB.Recordset")
sql = "select * from "& table_name &" where " & u_field & " like '%%" & u_input & "%%' " [/VBS]
Has anyone got any idea as to what I need to add / alter so that it will accept the "'".?
-
Sep 20, 2002, 16:40 #2
- Join Date
- Jul 2000
- Location
- Here
- Posts
- 1,010
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
You need to escape characters like the single quote. The best way is to make a simple function and to include it with all your database scripts.
[vbs]
Public Function FixDBString(ByVal strIn)
If Len(strIn) < 1 Then
Exit Function
End If
FixDBString = Replace(strIn, "'", "''")
End Function
[/vbs]
I also think you only need one % sign, not two in a row like you have in your code.
[vbs]
' ... your code
sql = "SELECT * FROM "& table_name & " WHERE " & u_field & " LIKE '%" & FixDBString(u_input) & "%' "
' more code ...
[/vbs]
-
Sep 21, 2002, 02:44 #3
- Join Date
- Jul 2002
- Location
- South-West UK
- Posts
- 100
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Hi Wes,
Thanks for that,....it worked a treat first time.
Matt
Bookmarks