I have a database with the fields REGION, LOCATION,SECTION, NAME, CONTACT. I am trying to set up a search facility whereby people can do a search on REGION, LOCATION or NAME
This is, very simply, what I have started with.
<%
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"_
& "Data Source="
objConn.ConnectionString=objConn.ConnectionString & Server.MapPath(".") & "\contactsdb.mdb"
objConn.Open
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "contactsdb",objConn, , , adCmdTable
Do While NOt objRS.EOF
Response.Write objRS("Name") & " "
Response.Write objRS ("Locations") & " "
Response.Write objRS ("Number") & "<br>"
objRS.MOveNExt
Loop
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>
I am just not sure how to get started with setting up the ASP page to accomodate the various search options?well that's a really wide open question becuase of the myriad of answers to it.
Very simply, you could have a form with a field for every column of your table and then have your ASP build a SQL Query to hit the database. Something like.....
<%
sub sForm
%>
<form action="search.asp?mode=go" method="post">
Search Contacts : <input type="text" name="fldContacts">
<input type="submit">
</form>
<%
end sub
sub search
dim strQuery
strQuery = "SELECT * FROM TABLE1 "
if request("fldContacts") <> "" then
strQUery = strQuery & " WHERE CONTACT = '" & replace(request("fldContacts"),"'","''") & "'"
end if
dim recSet,arrRS
set recSet = db.execute(strQuery)
if not(recSet.eof) then
arrRS = recSet.getRows
%><table><%
for i=0 to ubound(arrRS,2)
%><tr><%
for j=0 to ubound(arrRS,1)
%><td><%=arrRS(j,i)%></td><%
next
%></tr><%
next
%></table><%
else
response.write("Search returned no results")
end if
%><a href=http://www.htmlforums.com/archive/index.php/"search.asp">Return to Search Form</a>
end sub
select case request("mode")
case ""
sForm
case "go"
search
end select
%>
As you add more fields, the form will obviously grow and so will your code to search it, but that's a fairly basic search concept.Thanks Putts, this will help me get started. Just a question, what does this mean: ubound(arrRS) ?
ThanksIt returns the upper boundary (last index) of an array. So if you have an array five five elements, it will return the number 4 (since array indices start at 0, it would have 0, 1, 2, 3, and 4 in it).
RysOriginally posted by putts
<%
sub sForm
%>
<form action="search.asp?mode=go" method="post">
Search Contacts : <input type="text" name="fldContacts">
<input type="submit">
</form>
<%
end sub
sub search
dim strQuery
strQuery = "SELECT * FROM TABLE1 "
if request("fldContacts") <> "" then
strQUery = strQuery & " WHERE CONTACT = '" & replace(request("fldContacts"),"'","''") & "'"
end if
dim recSet,arrRS
set recSet = db.execute(strQuery)
if not(recSet.eof) then
arrRS = recSet.getRows
%><table><%
for i=0 to ubound(arrRS,2)
%><tr><%
for j=0 to ubound(arrRS,1)
%><td><%=arrRS(j,i)%></td><%
next
%></tr><%
next
%></table><%
else
response.write("Search returned no results")
end if
%><a href=http://www.htmlforums.com/archive/index.php/"search.asp">Return to Search Form</a>
end sub
select case request("mode")
case ""
sForm
case "go"
search
end select
%>
[/B]
While I know I run the risk of sounding completely stupid, I have to ask: what doe sit all mean???? Can you at the very least explain this part
if not(recSet.eof) then
arrRS = recSet.getRows
%><table><%
for i=0 to ubound(arrRS,2)
%><tr><%
for j=0 to ubound(arrRS,1)
%><td><%=arrRS(j,i)%></td><%
next
%></tr><%
Thanks putts.........or anyone else with superior intelligence
sorry for the delay....
getRows is a method I love to use in ClASP that is a method of a recordset that returns an array that is basically the same as the recordset.
Obviously, this array has to be multi-dimensional to contain all the data that could be in any given recordset.
So, now that's what arrRS.
So, if my recordset consisted of 3 records and 2 columns that will return an array 2 X 3 so I could use a for loop to say...
for i=0 to 2 'representing the # of rows
for j=0 to 1 'representing the # of cols
next
next
....but I love to make my code as dynamic as possible so I just employ the ubound() functions
ubound(arrRs,2) = number of records in the array
ubound(arrRs,1) = number of columns in the array
Does that clear up that part?Thanks Putts, it certainly does. I obviously have a long way to go.........
This is, very simply, what I have started with.
<%
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"_
& "Data Source="
objConn.ConnectionString=objConn.ConnectionString & Server.MapPath(".") & "\contactsdb.mdb"
objConn.Open
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "contactsdb",objConn, , , adCmdTable
Do While NOt objRS.EOF
Response.Write objRS("Name") & " "
Response.Write objRS ("Locations") & " "
Response.Write objRS ("Number") & "<br>"
objRS.MOveNExt
Loop
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>
I am just not sure how to get started with setting up the ASP page to accomodate the various search options?well that's a really wide open question becuase of the myriad of answers to it.
Very simply, you could have a form with a field for every column of your table and then have your ASP build a SQL Query to hit the database. Something like.....
<%
sub sForm
%>
<form action="search.asp?mode=go" method="post">
Search Contacts : <input type="text" name="fldContacts">
<input type="submit">
</form>
<%
end sub
sub search
dim strQuery
strQuery = "SELECT * FROM TABLE1 "
if request("fldContacts") <> "" then
strQUery = strQuery & " WHERE CONTACT = '" & replace(request("fldContacts"),"'","''") & "'"
end if
dim recSet,arrRS
set recSet = db.execute(strQuery)
if not(recSet.eof) then
arrRS = recSet.getRows
%><table><%
for i=0 to ubound(arrRS,2)
%><tr><%
for j=0 to ubound(arrRS,1)
%><td><%=arrRS(j,i)%></td><%
next
%></tr><%
next
%></table><%
else
response.write("Search returned no results")
end if
%><a href=http://www.htmlforums.com/archive/index.php/"search.asp">Return to Search Form</a>
end sub
select case request("mode")
case ""
sForm
case "go"
search
end select
%>
As you add more fields, the form will obviously grow and so will your code to search it, but that's a fairly basic search concept.Thanks Putts, this will help me get started. Just a question, what does this mean: ubound(arrRS) ?
ThanksIt returns the upper boundary (last index) of an array. So if you have an array five five elements, it will return the number 4 (since array indices start at 0, it would have 0, 1, 2, 3, and 4 in it).
RysOriginally posted by putts
<%
sub sForm
%>
<form action="search.asp?mode=go" method="post">
Search Contacts : <input type="text" name="fldContacts">
<input type="submit">
</form>
<%
end sub
sub search
dim strQuery
strQuery = "SELECT * FROM TABLE1 "
if request("fldContacts") <> "" then
strQUery = strQuery & " WHERE CONTACT = '" & replace(request("fldContacts"),"'","''") & "'"
end if
dim recSet,arrRS
set recSet = db.execute(strQuery)
if not(recSet.eof) then
arrRS = recSet.getRows
%><table><%
for i=0 to ubound(arrRS,2)
%><tr><%
for j=0 to ubound(arrRS,1)
%><td><%=arrRS(j,i)%></td><%
next
%></tr><%
next
%></table><%
else
response.write("Search returned no results")
end if
%><a href=http://www.htmlforums.com/archive/index.php/"search.asp">Return to Search Form</a>
end sub
select case request("mode")
case ""
sForm
case "go"
search
end select
%>
[/B]
While I know I run the risk of sounding completely stupid, I have to ask: what doe sit all mean???? Can you at the very least explain this part
if not(recSet.eof) then
arrRS = recSet.getRows
%><table><%
for i=0 to ubound(arrRS,2)
%><tr><%
for j=0 to ubound(arrRS,1)
%><td><%=arrRS(j,i)%></td><%
next
%></tr><%
Thanks putts.........or anyone else with superior intelligence
sorry for the delay....getRows is a method I love to use in ClASP that is a method of a recordset that returns an array that is basically the same as the recordset.
Obviously, this array has to be multi-dimensional to contain all the data that could be in any given recordset.
So, now that's what arrRS.
So, if my recordset consisted of 3 records and 2 columns that will return an array 2 X 3 so I could use a for loop to say...
for i=0 to 2 'representing the # of rows
for j=0 to 1 'representing the # of cols
next
next
....but I love to make my code as dynamic as possible so I just employ the ubound() functions
ubound(arrRs,2) = number of records in the array
ubound(arrRs,1) = number of columns in the array
Does that clear up that part?Thanks Putts, it certainly does. I obviously have a long way to go.........