The error you are getting is unlikely to be caused by the sql string you are
using. It is more likely to be a problem with the arguments used for the
Open statement.
And you have the Response.Write statement BEFORE the Open statement? You are
wasting your time and ours by failing to show us the code that's giving you
the problem.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"- Hide quoted text -
- Show quoted text -
This is the code that builds the SQL.
Response.Write(Request.ServerVariables("REQUEST_METHOD") & "<br>")
If (Request.ServerVariables("REQUEST_METHOD") = "POST") Then
If Request.Form("ctlSearchField") <> "" Then
Dim strWhere, strQuote
If (IsNumeric(ctlSearchFor) AND ctlSearchOption <> "LIKE" AND
ctlSearchOption <> "HAS") Then
strQuote = ""
Else
strQuote = "'"
End If
Response.Write("strQuote=" & strQuote & "x<br>")
If ctlSearchOption = "LIKE" Then
ctlSearchFor = ctrlSearchFor & "*"
End If
If ctlSearchOption = "HAS" Then
ctlSearchOption = "LIKE"
ctlSearchFor = "*" & ctrlSearchFor & "*"
End If
Response.Write("ctlSearchOption=" & ctlSearchOption & "+<br>")
Response.Write("ctlSearchFor=" & ctlSearchFor & "+<br>")
If ctlSearchField = "*" Then
strWhere = "ID " & ctlSearchOption & " " & strQuote & ctlSearchFor &
strQuote & " OR " &_
"[Equipment Description] " & ctlSearchOption & " " & strQuote &
ctlSearchFor & strQuote & " OR " &_
"Day " & ctlSearchOption & " " & strQuote & ctlSearchFor &
strQuote & " OR " &_
"Week " & ctlSearchOption & " " & strQuote & ctlSearchFor &
strQuote & " OR " &_
"Month " & ctlSearchOption & " " & strQuote & ctlSearchFor &
strQuote & " "
Else
strWhere = "WHERE " & ctlSearchField & " " & ctlSearchOption & " " &
strQuote & ctlSearchFor & strQuote & " "
End If
Response.Write("strWhere=" & strWhere & "+<br>")
Response.End()
sql = "SELECT * FROM [" & tables(tableID) & "] " &_
"WHERE " & strWhere &_
"ORDER BY ID ASC"
Response.Write(sql & "<br>")
Response.End()
End If
Else
sql = "SELECT * FROM [" & tables(tableID) & "] ORDER BY ID ASC"
Response.Write(sql & "<br>")
End If
Recordset1.open sql,conn,1,1
If not Recordset1.eof then
Recordset1_total = Recordset1.recordcount
Else
Recordset1_total = 0
End if
Recordset1_numRows = Recordset1_total
This is the seach form:
<form action="db.asp?ID=<%=tableID%>" method="post" name="search"
target="_self">
<TABLE width="500" align="center" border="1">
<TBODY>
<TR>
<TD vAlign="middle" align="center"><h3><B>Search for: </B></h3></TD>
</TR>
<TR>
<TD align="left">
<select id="ctlSearchField">
<option value="*">Any field</option>
<option value="ID">ID</option>
<option value='Equipment Description'>Equipment Description</option>
<option value='Day'>Day</option>
<option value='Week'>Week</option>
<option value='Month'>Month</option>
</select>
</TD>
</TR>
<TR>
<TD align="left">
<select id="ctlSearchOption">
<option value="HAS">Contains</option>
<option value="=">Equals</option>
<option value="LIKE">Starts with ...</option>
<option value=">">More than ...</option>
<option value="<">Less than ...</option>
<option value=">=">Equal or more than ...</option>
<option value="<=">Equal or less than ...</option>
</select>
</TD>
</TR>
<TR>
<TD align="left"><INPUT name="ctlSearchFor" type="text"
id="ctlSearchFor" size="40">
</TD>
</TR>
<TR>
<TD align="left"><INPUT type="submit" value="Search"> <INPUT
type="submit" value="Show all">
</TD>
</TR>
</TBODY>
</TABLE>
</form>