Hi All,

hopefully someone can give me a hand. I am trying to make an advanced search tool for my DB. Using html and ASP. I want to have one form with the select fields and one submit button. I have done what I think would work but I get:

Microsoft JET Database Engine error '80040e10'
No value given for one or more required parameters.
asearch.asp, line 34

Line 34. Of course Line 34 is not the problem but somewhere in my query:

<%
Dim rsresults__MMColParam
rsresults__MMColParam = "%"
If (Request.QueryString("sPostal" <> "" Then
rsresults__MMColParam = Request.QueryString("sPostal"
End If
%>
<%
Dim rsresults__MMColParam2
rsresults__MMColParam2 = "%"
If (Request.QueryString("sCity" <> "" Then
rsresults__MMColParam2 = Request.QueryString("sCity"
End If
%>
<%
Dim rsresults__MMColParam3
rsresults__MMColParam3 = "%"
If (Request.QueryString("sRegion" <> "" Then
rsresults__MMColParam3 = Request.QueryString("sRegion"
End If
%>
<%
Dim rsresults
Dim rsresults_numRows

Set rsresults = Server.CreateObject("ADODB.Recordset"
rsresults.ActiveConnection = MM_conndbcomplete_STRING
rsresults.Source = "SELECT intUserID, txtPostal, txtPhone, intCompanyID, intChapter, intCity, intSegment, txtCompanyName, cityName, ChapterName FROM tblUsers, tblCompanies, tblCity, tblChapters WHERE txtPostal LIKE '" + Replace(rsresults__MMColParam, "'", "''" + "%' AND intCompanyID = intCompID AND intChapter = ChapterID AND intCity = cityID AND cityID LIKE '" + Replace(rsresults__MMColParam2, "'", "''" + "' AND intChapter = ChapterID AND ChapterID LIKE '" + Replace(rsresults__MMColParam3, "'", "''" + "'"
rsresults.CursorType = 0
rsresults.CursorLocation = 2
rsresults.LockType = 1
rsresults.Open() <------------------------------------------------- this is line 34

rsresults_numRows = 0
%>
<%
Dim rscity
Dim rscity_numRows

Set rscity = Server.CreateObject("ADODB.Recordset"
rscity.ActiveConnection = MM_conndbcomplete_STRING
rscity.Source = "SELECT * FROM tblCity ORDER BY cityName ASC"
rscity.CursorType = 0
rscity.CursorLocation = 2
rscity.LockType = 1
rscity.Open()

rscity_numRows = 0
%>
<%
Dim rsChapters
Dim rsChapters_numRows

Set rsChapters = Server.CreateObject("ADODB.Recordset"
rsChapters.ActiveConnection = MM_conndbcomplete_STRING
rsChapters.Source = "SELECT * FROM tblChapters ORDER BY ChapterName ASC"
rsChapters.CursorType = 0
rsChapters.CursorLocation = 2
rsChapters.LockType = 1
rsChapters.Open()

rsChapters_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
rsresults_numRows = rsresults_numRows + Repeat1__numRows
%>

Should I be trying in a different way? please, if someone has done something like this can you share your experience please.

Thanks,

Javier

you could try this one too:

<%

Dim rsSearch,strSearch

strSearch = Request.Querystring("search"

Set rsSearch = ObjConn.Execute("SELECT * FROM TABLE WHERE title LIKE '%" & strSearch & "%' OR TABLE LIKE '%" & strSearch & "%' ORDER BY id DESC"

If rsSearch.EOF Then

response.write "Query not found!"

Else

While NOT rsSearch.EOF

response.write rsSearch("TABLE"

rsSearch.MoveNext
Wend
rsSearch.Close
Set rsSearch = Nothing

End If

%>

Thanks Micah for your reply.

What I'd like to know is and it seems that you have answered in your code, that I should possibly use OR instead of AND when I do my query.

I'll play with that and see where it takes me.