I have a ASP Web results page working with an Access database. What I want to do now is return results but break them down into catagories such as:
>>>
TITLE 1
1
2
3
TITLE 2
4
5
6
7
TITLE 3
8
9
10
<<<
Right now I can show the results for Title 1 & Title 2 but not Title 3. I have put that into a function (getMoreStates) but it does not work properly.
Here is the code that I have for the currently. Where or how do I adjust them to return the new desired results?
>>>
...
strSearch = Request.QueryString("search")
strSearch = Replace(strSearch, "'", "''")
Get all the items in the passed category
if u_id <> "" then
strSQL = "select * from REFERRAL WHERE NCOMP_NO =" & u_id
else
strSQL = "select * from REFERRAL WHERE STATE = '" & Replace(strSearch, "'", "''") & "' " & "ORDER BY TRAINCENTR DESC, CITY;"
tempSQL = strSQL
end if
Set RS1 = conn.Execute( strSQL )
Set NCOMP_NO = RS1("NCOMP_NO")
Set STSEARCH = RS1("STATE")
scriptresponder = "DisplayTrainer.asp"
Function getTravelStates( NCOMP_NO )
Dim SQL, RSS, list
SQL = "SELECT ST.STATE FROM REFSTATE AS ST, REFERRAL AS MM WHERE ST.NCOMP_NO =" & NCOMP_NO & " AND MM.NCOMP_NO =" & NCOMP_NO & " ORDER BY ST.STATE"
Set RSS = conn.Execute( SQL )
list = RSS.GetString( , , "", "," )
RSS.Close
getTravelStates = Left( list, LEN(list)-1 )
End Function
If Replace(strSearch, "'", "''") <> tempSQL Then
listOfStates = ""
else
listOfStates = getTravelStates( NCOMP_NO )
End if
Function getMoreStates(RS)
Dim sSQL, RSS2
If not objRec.EOF Then
sSQL = "SELECT * FROM REFSTATE WHERE STATE =" & strSearch
objRec.MoveNext
End if
Set RSS2 = conn.Execute( sSQL )
RSS2.Close
End Function
...
objREC.Open strSQL, Conn, adOpenForwardOnly, adLockReadOnly, adCmdText
numpages = objRec.PageCount
numrecs = objRec.RecordCount
If mypage > numpages Then mypage = numpages
If mypage < 1 Then mypage = 1
if not objRec.EOF then
objRec.AbsolutePage = mypage
if u_id <> "" then
Response.Write "<table border='0' cellspacing='0' cellpadding='3'><tr><td valign='top'><div class='smallcopy' align='top'><b>Organization: </b>" & objRec.Fields("COMPANY") & "<br><b>Address: </b>" & objRec.Fields("ADDRESS") & "<br><b>City: </b>" & objRec.Fields("CITY") & "<br><b>State: </b>" & objRec.Fields("STATE") & "<br><b>Zip Code: </b>" & objRec.Fields("ZIP") & "</div></td><td valign='top'><div class='smallcopy' align='top'><b>Phone Number: </b>" & objRec.Fields("PHONE") & "<br><b>Email Address: </b><a href='mailto:" & objRec.Fields("EMAIL") & "'>" & objRec.Fields("EMAIL") & "</a>" & "<br><b>Programs Taught: </b><br>" & programs(objRec) & "<br><b>Will Travel To:</b><br>" & listOfStates & "</div></td></tr><tr><td> </td></tr></table>"
else
Response.Write("<div class='maincopy' align='top'>Search found " & numrecs & " total records. Displaying page " & mypage & " of " & numpages & ".</div><br>")
If objRec.Fields("TRAINCENTR") = "N" Then
Response.Write "<table border='0' cellspacing='0' cellpadding='3'><tr><td colspan='3' class='maincopy'><b>National Training Centers</b></td></tr><tr class='maincopy'><th align='left'>[u]City</u></th><th align='left'>[u]Company</u></th><th align='left'>[u]Programs Taught</u></th></tr>"
Dim iin
For iin = 1 To pagesize
If NOT objRec.EOF AND objRec.Fields("TRAINCENTR") = "N" Then
trainnumber = trainnumber + 1
my_link = scriptresponder & "?u_id=" & objRec.fields("NCOMP_NO")
Response.Write "<tr><td><div class='smallcopy' align='top'>" & "<a href='" & my_link & "'>" & objRec.Fields("CITY") & "</a>" & "</div></td>" & "<td><div class='smallcopy' align='top'>" & "<a href='" & my_link & "'>" & objRec.Fields("COMPANY") & "</a></div></td>" & "<td><div class='smallcopy' align='top'>" & programs(objRec) & "</div></td></tr>"
objRec.MoveNext
End If
Next
Response.Write "<tr><td> </td></tr></table>"
End if
Response.Write "<table border='0' cellspacing='0' cellpadding='3'><tr><td colspan='3' class='maincopy'><b>Training Organizations</b></td></tr><tr class='maincopy'><th align='left'>[u]City</u></th><th align='left'>[u]Company</u></th><th align='left'>[u]Programs Taught</u></th></tr>"
Dim i
For i = 1 To pagesize - trainnumber
mmstate = i
If NOT objRec.EOF Then
my_link = scriptresponder & "?u_id=" & objRec.fields("NCOMP_NO")
Response.Write "<tr><td><div class='smallcopy' align='top'>" & "<a href='" & my_link & "'>" & objRec.Fields("CITY") & "</a>" & "</div></td>" & "<td><div class='smallcopy' align='top'>" & "<a href='" & my_link & "'>" & objRec.Fields("COMPANY") & "</a></div></td>" & "<td><div class='smallcopy' align='top'>" & programs(objRec) & "</div></td></tr>"
objRec.MoveNext
End If
Next
Response.Write "<tr><td> </td></tr></table>"
If getMoreStates(objRec) <> "" Then
Response.Write "<table border='0' cellspacing='0' cellpadding='3'><tr><td colspan='3' class='maincopy'><b>Training Organizations</b></td></tr><tr class='maincopy'><th align='left'>[u]City</u></th><th align='left'>[u]Company</u></th><th align='left'>[u]Programs Taught</u></th></tr>"
Dim iim
For iim = 1 To pagesize - mmstate
If NOT objRec.EOF AND getMoreStates(objRec) Then
my_link = scriptresponder & "?u_id=" & objRec.fields("NCOMP_NO")
Response.Write "<tr><td><div class='smallcopy' align='top'>" & "<a href='" & my_link & "'>" & objRec.Fields("CITY") & "</a>" & "</div></td>" & "<td><div class='smallcopy' align='top'>" & "<a href='" & my_link & "'>" & objRec.Fields("COMPANY") & "</a></div></td>" & "<td><div class='smallcopy' align='top'>" & programs(objRec) & "</div></td></tr>"
objRec.MoveNext
End If
Next
Response.Write "<tr><td> </td></tr></table>"
End if
end if
>>>
Any ideas or assistance would be helpful.
Thanks.
Displaying Multiple Results
