Searching problem now!

[mattyd22]

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 "'".?

[wdmny]

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]

[mattyd22]

Hi Wes,

Thanks for that,....it worked a treat first time.

Matt