asp error

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>

<%
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
%>

 

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):

 

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
%>

 

Problem solved since last post.
thank you for your help.

 

you're quite welcome

let me guess.... uncomment your execute line?

 

Yes indeed!