npala2001
04-12-2005, 05:49 PM
I have an access database that contains many entries. I have a HTML form that allows a user to select a certain search criteria which then gets output to an ASP page that calls the access database which then grabs the data the users specified. The problem is that the ASP page is not bringing up all of the entries. For example, I did a test myself and for one of the search criteria’s there are 8 entries within the access database, but the ASP page is only bringing up 1 of the entries. Below is the HTML page code and ASP page code. Any help would be greatly appreciated.
Below is the HTML page that allows users to select a certain criteria
<html>
<head>
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Search by Facility</title>
</head>
<body>
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; dbq=" & Server.MapPath("rcqr.mdb")
sqldd = "Select distinct fa from tblQR"
set Rs = Conn.execute(sqldd)
%>
<form action="../../RespDatabase_interface/Table/Master_Quarterly_Summary_Report_Facility.asp" method="post">
<p><font face="Verdana" size="2">Search by Facility</font><br>
<select size="1" name="dd2">
<%
do until rs.eof
response.write "<option>"& rs("fa") &"</option>"
rs.movenext
loop
conn.close
set conn=nothing
%>
</select> <br>
<input type="submit" value="Submit" name="B1"><br>
<br>
<font face="Verdana" size="2"><span style="background-color: #FFFF00">Please
Note:</span> If your facility is not listed in the above drop down menu then
their <br>have been no Responsible Care Entries submitted for your facility.</font></p>
</form>
</body>
</html>
Below is the ASP Page that the HTML Form calls
<html>
<head>
<title>Master - Quarterly Summary Report - Search Facilities</title>
</head>
<body>
<%
dd2 = request.form("dd2")
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; dbq=" & Server.MapPath("rcqr.mdb")
sql = "Select qr, pr, fa, ad, ta, da from tblQR where fa='" & dd2 & "'"
set Test_Rs = Conn.execute(sql)
Counter = 0
Test_Rs.Movefirst
do until Test_Rs.Eof
if Counter = 0 then
With Response
.Write "<tr><td>"
.Write "Quarter - </td>" & Test_Rs("qr")
.Write "<br>"
.Write "Person Reporting - </td>" & Test_Rs("pr")
.Write "<br>"
.Write "Facility - </td>" & Test_Rs("fa")
.Write "<br>"
.Write "Activity Date - </td>" & Test_Rs("ad")
.Write "<br>"
.Write "Type of Activity - </td>" & Test_Rs("ta")
.Write "<br>"
.Write "Description of Activity - </td>" & Test_Rs("da")
.Write " <br>"
.Write "<td colspan=""8"">"
.Write "<hr noshade color=""#000000"" size=""1""></td>"
.Write "</tr>"
End With
end if
Counter = Counter + 1
if Counter = 10 then
Response.Write "</table>"
response.write "<br class=""page"" />"
Counter = 0
end if
Test_rs.movenext
loop
conn.close
set conn=nothing
response.end
%>
</body>
</html>
Thanks
Below is the HTML page that allows users to select a certain criteria
<html>
<head>
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Search by Facility</title>
</head>
<body>
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; dbq=" & Server.MapPath("rcqr.mdb")
sqldd = "Select distinct fa from tblQR"
set Rs = Conn.execute(sqldd)
%>
<form action="../../RespDatabase_interface/Table/Master_Quarterly_Summary_Report_Facility.asp" method="post">
<p><font face="Verdana" size="2">Search by Facility</font><br>
<select size="1" name="dd2">
<%
do until rs.eof
response.write "<option>"& rs("fa") &"</option>"
rs.movenext
loop
conn.close
set conn=nothing
%>
</select> <br>
<input type="submit" value="Submit" name="B1"><br>
<br>
<font face="Verdana" size="2"><span style="background-color: #FFFF00">Please
Note:</span> If your facility is not listed in the above drop down menu then
their <br>have been no Responsible Care Entries submitted for your facility.</font></p>
</form>
</body>
</html>
Below is the ASP Page that the HTML Form calls
<html>
<head>
<title>Master - Quarterly Summary Report - Search Facilities</title>
</head>
<body>
<%
dd2 = request.form("dd2")
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; dbq=" & Server.MapPath("rcqr.mdb")
sql = "Select qr, pr, fa, ad, ta, da from tblQR where fa='" & dd2 & "'"
set Test_Rs = Conn.execute(sql)
Counter = 0
Test_Rs.Movefirst
do until Test_Rs.Eof
if Counter = 0 then
With Response
.Write "<tr><td>"
.Write "Quarter - </td>" & Test_Rs("qr")
.Write "<br>"
.Write "Person Reporting - </td>" & Test_Rs("pr")
.Write "<br>"
.Write "Facility - </td>" & Test_Rs("fa")
.Write "<br>"
.Write "Activity Date - </td>" & Test_Rs("ad")
.Write "<br>"
.Write "Type of Activity - </td>" & Test_Rs("ta")
.Write "<br>"
.Write "Description of Activity - </td>" & Test_Rs("da")
.Write " <br>"
.Write "<td colspan=""8"">"
.Write "<hr noshade color=""#000000"" size=""1""></td>"
.Write "</tr>"
End With
end if
Counter = Counter + 1
if Counter = 10 then
Response.Write "</table>"
response.write "<br class=""page"" />"
Counter = 0
end if
Test_rs.movenext
loop
conn.close
set conn=nothing
response.end
%>
</body>
</html>
Thanks