I am working on an ASP/database page with Access.
I am trying to bring up multiple results but the page is only displaying 2 of the desired 3 result sections. The function 'getMoreStates' is bringing in the proper data as is shown with the response.write (which is only in the function to show the data is there but nothing will be displayed if those are commented out).
Here is the full code:
>>
<%@ Language=VBScript %>
<%
Response.Buffer=True
'Option Explicit
%>
<html>
<body>
<div id="Layer1" style="position:absolute; left:11px; top:80px; width:610px; height:244px; z-index:20">
<table width="600" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>
<%
Dim strSQL
Dim Conn
Dim cmd
Dim objRec
Dim requestedState
Dim requestedType
u_id = request.querystring("u_id")
Dim strURL
Dim strPrograms
Dim strSpanish
Dim strBLev
Dim strAED
Dim strInst
Dim strTrain
Dim strSearch
set Conn = Server.CreateObject("ADODB.Connection")
set cmd = Server.CreateObject("ADODB.Command")
set objRec = Server.CreateObject("ADODB.Recordset")
Conn.Open strTrainReferConnect
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adUseClient = 3
Const adCmdText = 1
strURL = Request.ServerVariables("URL")
Dim mypage, numpages
Dim numrecs, pagesize
mypage=CInt(Request("page"))
If mypage = 0 Then mypage = 1
pagesize = 10
objRec.PageSize = pagesize
objRec.CacheSize = pagesize
objRec.CursorLocation = adUseClient
objRec.CursorType = 3
strSearch = Request.QueryString("search")
strSearch = Replace(strSearch, "'", "''")
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, list2
sSQL = "SELECT REFERRAL.COMP_NO, REFERRAL.COMPANY, REFERRAL.ADDRESS, REFERRAL.CITY, REFERRAL.STATE, [REFERRAL].[ZIP] AS ZIP, REFERRAL.PHONE, REFERRAL.MASTERS, REFERRAL.TRAINERS, REFERRAL.INSTRUCTOR, REFERRAL.REGISTRY, REFERRAL.EMAIL, REFERRAL.V5, REFERRAL.PMV5, REFERRAL.SMV2, REFERRAL.BBPVID, REFERRAL.OXYGEN, REFERRAL.ICV5, REFERRAL.CPCPR, REFERRAL.AEDSUP, REFERRAL.BLSAED, REFERRAL.ECARE, REFERRAL.V5SP, REFERRAL.CONTACT, REFERRAL.TRAINCENTR, REFERRAL.NCOMP_NO FROM REFERRAL INNER JOIN REFSTATE ON REFERRAL.NCOMP_NO = REFSTATE.NCOMP_NO WHERE (((REFERRAL.STATE)<>[REFSTATE].[STATE]) AND ((REFSTATE.STATE)='" & strSearch & "')) ORDER BY REFERRAL.CITY, REFSTATE.COMP_NO"
' Response.Write sSQL
Set RSS2 = conn.Execute( sSQL )
list2 = RSS2.GetString( , , ", ", "<br>" )
RSS2.Close
getTravelStates2 = Left( list2, LEN(list2)-1 )
Response.Write getTravelStates2 + "<br>"
End Function
Function programs(RS)
dim strAED, strBLev, strBLS, strBBLev, strCare, strECare, strIC, strOXLev, strPLev, strSpanish, strSLev, strTrain, strInst
If objRec.Fields("V5") = "Y" Then strBLev = "Basic "
If objRec.Fields("PMV5") = "Y" Then strPLev = "Peds "
If objRec.Fields("SMV2") = "Y" Then strSLev = "Sports "
If objRec.Fields("BBPVID") = "Y" Then strBBLev = "BBP "
If objRec.Fields("OXYGEN") = "Y" Then strOXLev = "Oxy "
If objRec.Fields("ICV5") = "Y" Then strIC = "IC "
If objRec.Fields("CPCPR") = "Y" Then strCare = "CarePlus "
If objRec.Fields("AEDSUP") = "Y" Then strAED = "AED "
If objRec.Fields("BLSAED") = "Y" Then strBLS = "BLSPro "
If objRec.Fields("ECARE") = "Y" Then strECare = "ECare "
If objRec.Fields("V5SP") = "Y" Then strSpanish = "SPB "
If objRec.Fields("MASTERS") <> "0" Then strTrain = "Train "
If objRec.Fields("MASTERS") <> "0" Then strInst = "Inst "
If objRec.Fields("TRAINERS") <> "0" Then strInst = "Inst "
strProgram = strAED & strBLev & strBLS & strBBLev & strCare & strECare & strIC & strOXLev & strPLev & strSpanish & strSLev & strInst & strTrain
strProgram = RTrim(strProgram)
programs = Replace (strProgram," ",", ")
End Function
%>
<table width="600" border="0" cellspacing="0" cellpadding="0">
<tr valign="top">
<td><span class="sectionhead">Results - Instructor Locator - <%= strSearch %>
</span></td>
</tr>
<tr valign="top">
<td>
</td>
</tr>
</table>
</td>
</tr>
<tr valign="top">
<td>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td valign="top">
<%
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' width='80'>[u]City</u></th><th align='left' width='210'>[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
'If objRec.Fields("TRAINCENTR") <> "N" 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' width='80'>[u]City</u></th><th align='left' width='210'>[u]Company</u></th><th align='left'>[u]Programs Taught</u></th></tr>"
Dim i
For i = 1 To pagesize - trainnumber
If NOT objRec.EOF Then
mmstate = i + 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
If getMoreStates(objRec) <> "" Then
Response.Write "<table border='0' cellspacing='0' cellpadding='3'><tr><td colspan='3' class='maincopy'><b>More Travel Training Organizations</b></td></tr><tr class='maincopy'><th align='left' width='80'>[u]City</u></th><th align='left' width='210'>[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 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
If mypage > 1 Then
%>
<a href="<%= strURL %>?search=<%= Server.URLEncode(strSearch) %>&page=<%= mypage - 1 %>">[<< Prev]</a>
<%
End If
For I = 1 To numpages
If I = mypage Then
%>
<%= I %>
<%
Else
%>
<a href="<%= strURL %>?search=<%= Server.URLEncode(strSearch) %>&page=<%= I %>"><%= I %></a>
<%
End If
Next 'I
If mypage < numpages Then
%>
<a href="<%= strURL %>?search=<%= Server.URLEncode(strSearch) %>&page=<%= mypage + 1 %>">[Next >>]</a>
<%
End If
else
Response.Write "<div class='pageheadfont'>Instructor Search:<br><img src='../../images/misc/transparent.gif' width='1' height='5'><br></div><div class='maincopy'>Sorry, there are no Instructors for this location.</div>"
Response.Write "<br> <br>"
end if
objRec.Close
set objRec = nothing
%>
<div class='smallcopy'><br><b><a href="SearchTrainer.asp">Return to Instructor Search Page</a></b> <br>
<br>
</div>
</td>
</tr>
<tr>
<td align="center">
<div class='smallcopy'>This Instructor List may not include every Instructor in an area or region.</div><br>
</td>
</tr>
</table>
</td>
</tr>
</table>
</div>
</body>
</html>
<<
As mentioned above, in the function being called, there are a few lines I have in just to see that the proper data is being returned from the function. I know that those should be taken out once everything is working properly:
>>
list2 = RSS2.GetString( , , ", ", "<br>" )
getTravelStates2 = Left( list2, LEN(list2)-1 )
Response.Write getTravelStates2 + "<br>"
<<
Here is what the displayed results looks like this, again, it is only returning 2 of the desired 3 results:
>>
Results - Instructor Locator - CT
------------------------------------------------------------------
Search found 7 total records. Displaying page 1 of 1.
National Training Centers
City Company Programs Taught
Cromwell Respect AED, Basic, BBP
Newington Citrus AED, Basic, BBP, Inst
Training Organizations
City Company Programs Taught
Meriden Smith, Edward AED, Basic, BBP, ECare, Oxy, Peds, Inst, Train
Riverside Greenwich AED, Basic, BBP, Peds, SPB, Inst
Stamford Stamford Center AED, Basic, BBP, Peds, Inst
Waterbury SALAD AED, Basic, Inst
West Haven Plus PLUS AED, Basic, BBP, IC, Peds, Inst
10999-1, TRAINING - Training, 4417 Daspit Rd, Los Angeles, CA, 90028, (213) 555-1212, 0, 0, 1, 26852,
[email protected], Y, , , Y, , , , , , , , Jimmy Stewart, , 109991
19999-1, MORE TRAINING - Test, 4400 Desperate Pl, Los Angeles, CA, 90028, (213) 555-1212, 0, 0, 1, 89999,
[email protected], Y, , Y, Y, , Y, Y, Y, , , Y, Smith Smyth, , 1999911
Return to Instructor Search Page
This Instructor List may not include every Instructor in an area or region.
<<
In this example, the two items at the bottom (data showing 10999-1, 19999-1) should return results and have a header reading "More Travel Training Organizations" and show results as the other two do. Also, the display: "Search found 7 total records." should be showing 9 and NOT 7.
What in the function or in the code that calls the function that is causing the display to not show the proper results?
Thank you in advance for your assistance.
Function Display Problems???
