1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

asp error

Discussion in 'C#' started by dirkoo, Apr 13, 2006.

  1. #1
    Hello, i am trying to make a database search on an access file. I am getting the following error :
    Microsoft VBScript compilation error '800a0401'

    Expected end of statement

    /data/zoeken.asp, line 23

    strSQL = "SELECT * FROM ledenlijst & "WHERE NAAM LIKE '%" & Replace(strSearch, "'", "''") & "%' " & "OR VOORNAAM LIKE '%" & Replace(strSearch, "'", "''") & "%' " & "ORDER BY NAAM;"
    --------------------------------------^

    Can anyone help me please?


    <%
    Dim cnnSearch
    Dim rstSearch
    Dim strDBPath
    Dim strSQL
    Dim strSearch
    Dim strnaam, strvoornaam, strtelnummer, strgsmnummer, stremail
    strSearch = Request.QueryString("search")

    %>
    <p>Search database </p>
    <form action="<zoeken.asp>" method="get">
    <input name="search" value="<%= strSearch %>" />
    <input type="submit" />
    </form>
    SEMrush
    <%
    If strSearch <> "" Then

    strDBPath = Server.MapPath("\DATA\ledenlijst.mdb")
    Set cnnSearch = Server.CreateObject("ADODB.Connection")
    cnnSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"
    strSQL = "SELECT * FROM ledenlijst & "WHERE NAAM LIKE '%" & Replace(strSearch, "'", "''") & "%' " & "OR VOORNAAM LIKE '%" & Replace(strSearch, "'", "''") & "%' " & "ORDER BY NAAM;"

    Set rstSearch = cnnSearch.Execute(strSQL)

    %>
    <table border="1">
    <tr>
    <th>NAAM</th>
    <th>VOORNAAM</th>
    <th>TEL</th>
    <th>GSM</th>
    <th>EMAIL</th>
    </tr>
    <%
    Do While Not rstSearch.EOF
    %>
    <tr>
    <td><%= rstSearch.Fields("NAAM").Value %> <%= rstSearch.Fields("VOORNAAM").Value %></td>
    <td><%= rstSearch.Fields("TEL").Value %></td>
    <td><%= rstSearch.Fields("GSM").Value %></td>
    <td><%= rstSearch.Fields("EMAIL").Value %></td>
    </tr>
    <%

    rstSearch.MoveNext
    Loop
    %>
    </table>
    <%
    rstSearch.Close
    Set rstSearch = Nothing
    cnnSearch.Close
    Set cnnSearch = Nothing
    End If
    %>
     
    dirkoo, Apr 13, 2006 IP
    SEMrush
  2. vectorgraphx

    vectorgraphx Guest

    Messages:
    545
    Likes Received:
    16
    Best Answers:
    0
    Trophy Points:
    0
    #2
    your sql statement is messed up.

    try this

    
    strSQL = "SELECT * FROM ledenlijst WHERE NAAM LIKE '%" & Replace(strSearch, "'", "''") & "%' OR VOORNAAM LIKE '%" & Replace(strSearch, "'", "''") & "%' ORDER BY NAAM;"
    
    Code (markup):
    but honestly, it would make it easier if you would cleanse your strsearch variable before your SQL statement rather than cleansing it on the fly in-line, like this:

    
    dim newstrsearch 
    newstrsearch = Replace(strSearch, "'", "''")
    
    strSQL = "SELECT * FROM ledenlijst WHERE NAAM LIKE '%" & newstrsearch & "%' OR VOORNAAM LIKE '%" & newstrsearch & "%' ORDER BY NAAM;"
    
    Code (markup):
     
    vectorgraphx, Apr 13, 2006 IP
  3. dirkoo

    dirkoo Peon

    Messages:
    5
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    thank you very much for your answer. but the problem now is that i am not getting any respons from the database. Here is the page :
    http://www.pauldesmedt.be/data/zoeken.asp

    this is the code : Is there still an error in this code? My provider supports ASP, and the database has been uploaded.

    <%
    Dim cnnSearch
    Dim rstSearch
    Dim strDBPath
    Dim strSQL
    Dim strSearch
    Dim strnaam, strvoornaam, strtelnummer, strgsmnummer, stremail
    strSearch = Request.QueryString("search")

    %>
    <p>Search database </p>
    <form action="file:///C|/Documents and Settings/dwcm11/Desktop/thor/&lt;zoeken.asp&gt;" method="get">
    <input name="search" value="<%= strSearch %>" />
    <input type="submit" />
    </form>

    <%
    If strSearch <> "" Then

    strDBPath = Server.MapPath("\DATA\ledenlijst.mdb")
    Set cnnSearch = Server.CreateObject("ADODB.Connection")
    cnnSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"

    'strSQL = "SELECT * FROM ledenlijst & "WHERE NAAM LIKE '%" & Replace(strSearch, "'", "''") & "%' " & "OR VOORNAAM LIKE '%" & Replace(strSearch, "'", "''") & "%' " & "ORDER BY NAAM;"

    dim newstrsearch
    newstrsearch = Replace(strSearch, "'", "''")

    strSQL = "SELECT * FROM ledenlijst WHERE NAAM LIKE '%" & newstrsearch & "%' OR VOORNAAM LIKE '%" & newstrsearch & "%' ORDER BY NAAM;"
    'Set rstSearch = cnnSearch.Execute(strSQL)

    %>
    <table border="1">
    <tr>
    <th>NAAM</th>
    <th>VOORNAAM</th>
    <th>TEL</th>
    <th>GSM</th>
    <th>EMAIL</th>
    </tr>
    <%
    Do While Not rstSearch.EOF
    %>
    <tr>
    <td><%= rstSearch.Fields("NAAM").Value %> <%= rstSearch.Fields("VOORNAAM").Value %></td>
    <td><%= rstSearch.Fields("TEL").Value %></td>
    <td><%= rstSearch.Fields("GSM").Value %></td>
    <td><%= rstSearch.Fields("EMAIL").Value %></td>
    </tr>
    <%

    rstSearch.MoveNext
    Loop
    %>
    </table>
    <%
    rstSearch.Close
    Set rstSearch = Nothing
    cnnSearch.Close
    Set cnnSearch = Nothing
    End If
    %>
     
    dirkoo, Apr 18, 2006 IP
  4. dirkoo

    dirkoo Peon

    Messages:
    5
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Problem solved since last post.
    thank you for your help.;)
     
    dirkoo, Apr 18, 2006 IP
  5. vectorgraphx

    vectorgraphx Guest

    Messages:
    545
    Likes Received:
    16
    Best Answers:
    0
    Trophy Points:
    0
    #5
    you're quite welcome :D

    let me guess.... uncomment your execute line?
     
    vectorgraphx, Apr 18, 2006 IP
  6. dirkoo

    dirkoo Peon

    Messages:
    5
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Yes indeed! :eek:
     
    dirkoo, Apr 18, 2006 IP