SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    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 "'".?

  2. #2
    SitePoint Wizard wdmny's Avatar
    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]

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2002
    Location
    South-West UK
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Wink

    Hi Wes,

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

    Matt


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •