...

View Full Version : Two do while loops



avenger_gr
03-01-2007, 07:32 PM
Hello everyone ive been trying to fix the following problem for too much time :P
I am making an evoting site so im trying to have a page with the all elections of a particular organisation and the candidates under each equivalent election.

The code for the problem is here :
----- this is where the connections are done, one for the elections and one for the candidates


<% Dim organisation, election_name
Dim DBConn, rs
Dim strSQL, rsDetails, adoconn

organisation=(Session("theOrganisation"))

Set DBConn2=server.CreateObject("ADODB.Connection")
DBConn2.Provider="Microsoft.Jet.OLEDB.4.0"
DBConn2.Open "C:\Inetpub\wwwroot\database\db.mdb"
set rs2=Server.CreateObject("ADODB.Recordset")
rs2.Open "SELECT * FROM Elections WHERE (organisation='" & organisation&"') ;", DBConn2

Set adoconn=server.CreateObject("ADODB.Connection")
adoconn.Provider="Microsoft.Jet.OLEDB.4.0"
adoconn.Open "C:\Inetpub\wwwroot\database\db.mdb"
set rsDetails=Server.CreateObject("ADODB.Recordset")
strSQL="SELECT * FROM admin_view_cand WHERE ((organisation='" & organisation&"') AND (name='" & election_name&"'));"
rsDetails.Open strSQL, adoconn
%>

<table cellpadding="5" width="50%">

<% DO WHILE NOT rs2.EOF ------first do while that shows the first election
election_name=rs2("name") %>
<TR><TH align="center">Election</TH><TH align="center">Start Date</TH><TH align="center">End Date</TH></TR>
<tr> -----election information
<td align="center"><a href="viewAElection.asp?ename=<% response.write rs2("name")%> "><% Response.Write rs2("name") %></a></td>
<td align="center"><% Response.Write rs2("date_start") %></td> <td align="center"><% Response.Write rs2("date_end") %></td>
</tr>
<TR><TH align="left">Name</TH><TH align="left">Election Name</TH><TH align="left">Position</TH></TR>
<%DO WHILE NOT rsDetails.EOF%> ------ second do while to show the candidates of each election
<tr> ------ candidate information
<td><a href="viewACandidate.asp?fname=<% Response.Write (rsDetails("fname")) %>&lname=<% Response.Write (rsDetails("lname")) %>&ename=<% response.write (rsDetails("name"))%>&username=<% response.write (rsDetails("username"))%> "><% Response.Write rsDetails("fname") %>&nbsp;<% Response.Write rsDetails("lname") %></a></td><td><a href="viewAElection.asp?ename=<% response.write rsDetails("name")%> "><% response.write rsDetails("name")%></a></td><td><% Response.Write rsDetails("ca_position") %></td>
</tr>
<% rsDetails.Movenext
Loop
rs2.MoveNext
Loop
%>
</table>

BarrMan
03-01-2007, 07:51 PM
First of all, you posted this in the wrong forum.
Second, you didn't write the actual problem.
And third, you might want to write RsDetail.MoveFirst after the second loop so you could get the results after the first time it's being looped.

avenger_gr
03-01-2007, 08:53 PM
Sorry, ive been trying to edit my post but it hasnt edited anything twice... :s

The problem is that when i open it it outputs the election titles 4 times, the 4 different elections, and the candidate titles 4 times, but it doesnt show any candidates. Even though election_name keeps the equivelant election name to be used to search for the candidates, it seems that the sql query does not use it. If i use election_name as a specific election name and not a variable, it outputs the candidates of the specific election but it outputs them only for the first election and not in the equivalent election.

I tried the rsDetails.MoveFirst and i get:
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record. line 41
line 41 is the line where rsDetails.Movefirst is. - i placed it above the first do while. and i got the same result if i placed it after the second loop :s

Thanks for your time

BarrMan
03-01-2007, 08:59 PM
Ok then, so to evoid this error just do this:

If Not RsDetail.EOF Then Rs.MoveFirst
Since you get this error, it means you get no results returned from the query RsDetail has therefor even if you do add this piece of code, it would only help you avoid the error, but you wouldn't still see any results returned.

Btw, I just noticed your SQL statement. Try using this instead:

strSQL="SELECT * FROM admin_view_cand WHERE organisation='" & organisation&"' AND name='" & election_name&"'"

avenger_gr
03-01-2007, 09:07 PM
I tried this but i wanted to see whats going on with strSQL and discovered that election_name=rs2("name") can be outputted correctly with Response.Write, but it does not show in strSQL:
SELECT * FROM admin_view_cand WHERE organisation='Sony' AND name='' SELECT * FROM admin_view_cand WHERE organisation='Sony' AND name='' SELECT * FROM admin_view_cand WHERE organisation='Sony' AND name='' SELECT * FROM admin_view_cand WHERE organisation='Sony' AND name=''

That came from Response.Write(strSQL) just after the first do while
:S

BarrMan
03-01-2007, 10:46 PM
So your problem must be with the value of the variable election name.
Try changing the name in the query to something that does exist in your database and see if it returns results, if it does, the problem is only with your sql statement.
Check your election_name variable value too.

avenger_gr
03-02-2007, 12:31 AM
If i change the query to something that exists in the database it outputs the candidates of the specific election but it only outputs it under the candidates of the first election and not the equivalent one.
Any suggestions? :confused:

avenger_gr
03-02-2007, 03:12 AM
To make things clearer :
When i use the election_name=rs2("name") it outputs:

Election Start Date End Date
Exec 24/01/2007 24/01/2007
Name Election Name Position
-----------if i use election_name as a specific one such as the "Exec", the specific candidates appear. if i use it as "CIO" the candidates of CIO appear here and not below.
Election Start Date End Date
CIO 24/01/2007 24/01/2007
Name Election Name Position

Election Start Date End Date
Football Team 24/01/2007 24/01/2007
Name Election Name Position

Election Start Date End Date
CEO 13/02/2007 23/02/2007
Name Election Name Position

BarrMan
03-02-2007, 10:31 AM
But from what I understood your query had queries in it. That's what you said when you wrote the strSql output.

SSJ
03-02-2007, 10:59 AM
what is going on over here?

avenger_gr
03-02-2007, 05:17 PM
When i wrote the strSQL output it was like:
SELECT * FROM admin_view_cand WHERE organisation='Sony' AND name=''
the problem is that it gets the value of the organisation but not for the name. The name is provided by the variable election_name which keeps the election names (if i response.write it, it outputs them correctly. why doesnt the query get the values?)
the next problem is that if i put election_name="CIO" then it outputs the candidates of election "CIO" but in the first position where election "Exec" is.

BarrMan
03-02-2007, 06:01 PM
Ok, so first of all, I'd advice you to arrange code. ie:

<table>
<tr>
<td>
</td>
</tr>
</table>


Second, write the arranged code in the CODE tags in the editor.
This would help me find the problem.

avenger_gr
03-02-2007, 07:06 PM
Tried to edit my first post again but seems to not work completely :s
I made a change in strSQL and tried another way which worked. Instead of doing it through strSQL i added an if statement just after the second do while which checks the election name of rs2 with the election name of rsDetails so the latest output was:

Sony Current Elections:

Election Start Date End Date
Exec 24/01/2007 24/01/2007
Name Position
Dim Gr Vice-President
Takis Pakis Vice-President
Gerar Vialo Vice-President

Election Start Date End Date
CIO 24/01/2007 24/01/2007
Name Position

Election Start Date End Date
Football Team 24/01/2007 24/01/2007
Name Position

Election Start Date End Date
CEO 13/02/2007 23/02/2007
Name Position

The problem now is that the second do while only works the first time and does not continue on :s



<%
Dim organisation, election_name
Dim DBConn, rs, foundit
Dim strSQL, rsDetails, adoconn

organisation=(Session("theOrganisation"))

Set DBConn2=server.CreateObject("ADODB.Connection")
DBConn2.Provider="Microsoft.Jet.OLEDB.4.0"
DBConn2.Open "C:\Inetpub\wwwroot\database\db.mdb"
set rs2=Server.CreateObject("ADODB.Recordset")
rs2.Open "SELECT * FROM Elections WHERE (organisation='" & organisation&"') ;", DBConn2

Set adoconn=server.CreateObject("ADODB.Connection")
adoconn.Provider="Microsoft.Jet.OLEDB.4.0"
adoconn.Open "C:\Inetpub\wwwroot\database\db.mdb"
set rsDetails=Server.CreateObject("ADODB.Recordset")
strSQL="SELECT * FROM admin_view_cand WHERE (organisation='" & organisation&"');"
rsDetails.Open strSQL, adoconn
%>

<div class="welcome"><% Response.Write(Session("theOrganisation"))%>&nbsp;Current Elections:</div> <br />
<div class="main_elections">
<table cellpadding="5" width="50%">
<%
DO WHILE NOT rs2.EOF
election_name=rs2("name")
%>
<TR> ----election titles
<TH align="center">Election</TH>
<TH align="center">Start Date</TH>
<TH align="center">End Date</TH>
</TR>

<tr> ----elections
<td align="center"><a href="viewAElection.asp?ename=<% response.write rs2("name")%> "><% Response.Write rs2("name") %></a></td>
<td align="center"><% Response.Write rs2("date_start") %></td> <td align="center"><% Response.Write rs2("date_end") %></td>
</tr>

<TR> -----candidate titles
<TH align="left">Name</TH>
<TH align="left">Position</TH>
</TR>
<%
DO WHILE NOT rsDetails.EOF
if (StrComp(rsDetails("name"), election_name, vbTextCompare)=0) then
%>
<tr> -----candidates
<td><a href="viewACandidate.asp?fname=<% Response.Write (rsDetails("fname")) %>&lname=<% Response.Write (rsDetails("lname")) %>&ename=<% response.write (rsDetails("name"))%>&username=<% response.write (rsDetails("username"))%> "><% Response.Write rsDetails("fname") %>&nbsp;<% Response.Write rsDetails("lname") %></a></td><td><% Response.Write rsDetails("ca_position") %></td>
</tr>
<%
end if
rsDetails.Movenext
Loop
rs2.MoveNext
Loop
%>
</table>
</div>

<%
rs2.Close
Set rs2=Nothing
DBConn2.Close
Set DBConn2 = Nothing
rsDetails.Close
Set rsDetails=Nothing
adoconn.Close
Set adoconn = Nothing
%>

BarrMan
03-03-2007, 11:11 AM
When you run through your records with RsDetails the first time, it stops when it's on the end of the file and then when you try to run through it again it's already on the end of the file so it can't move anymore. That's why I said at the beggining to add Rs.MoveFirst after the do while loop of RsDetails.

<%
Dim organisation, election_name
Dim DBConn, rs, foundit
Dim strSQL, rsDetails, adoconn

organisation=(Session("theOrganisation"))

Set DBConn2=server.CreateObject("ADODB.Connection")
DBConn2.Provider="Microsoft.Jet.OLEDB.4.0"
DBConn2.Open "C:\Inetpub\wwwroot\database\db.mdb"
set rs2=Server.CreateObject("ADODB.Recordset")
rs2.Open "SELECT * FROM Elections WHERE (organisation='" & organisation&"') ;", DBConn2

Set adoconn=server.CreateObject("ADODB.Connection")
adoconn.Provider="Microsoft.Jet.OLEDB.4.0"
adoconn.Open "C:\Inetpub\wwwroot\database\db.mdb"
set rsDetails=Server.CreateObject("ADODB.Recordset")
strSQL="SELECT * FROM admin_view_cand WHERE (organisation='" & organisation&"');"
rsDetails.Open strSQL, adoconn
%>

<div class="welcome"><% Response.Write(Session("theOrganisation"))%>&nbsp;Current Elections:</div> <br />
<div class="main_elections">
<table cellpadding="5" width="50%">
<%
DO WHILE NOT rs2.EOF
election_name=rs2("name")
%>
<TR> ----election titles
<TH align="center">Election</TH>
<TH align="center">Start Date</TH>
<TH align="center">End Date</TH>
</TR>

<tr> ----elections
<td align="center"><a href="viewAElection.asp?ename=<% response.write rs2("name")%> "><% Response.Write rs2("name") %></a></td>
<td align="center"><% Response.Write rs2("date_start") %></td> <td align="center"><% Response.Write rs2("date_end") %></td>
</tr>

<TR> -----candidate titles
<TH align="left">Name</TH>
<TH align="left">Position</TH>
</TR>
<%
DO WHILE NOT rsDetails.EOF
if (StrComp(rsDetails("name"), election_name, vbTextCompare)=0) then
%>
<tr> -----candidates
<td><a href="viewACandidate.asp?fname=<% Response.Write (rsDetails("fname")) %>&lname=<% Response.Write (rsDetails("lname")) %>&ename=<% response.write (rsDetails("name"))%>&username=<% response.write (rsDetails("username"))%> "><% Response.Write rsDetails("fname") %>&nbsp;<% Response.Write rsDetails("lname") %></a></td><td><% Response.Write rsDetails("ca_position") %></td>
</tr>
<%
end if
rsDetails.Movenext
Loop
Rs.MoveFirst
rs2.MoveNext
Loop
%>
</table>
</div>

<%
rs2.Close
Set rs2=Nothing
DBConn2.Close
Set DBConn2 = Nothing
rsDetails.Close
Set rsDetails=Nothing
adoconn.Close
Set adoconn = Nothing
%>

avenger_gr
03-03-2007, 06:02 PM
Thanx a lot !! :) i was spending too many hours looking at it but not changing anything :P I had removed the movefirst after u said it cause i had the problem with the election_name and it gave me the error with the deleted records etc..
Anyway, thanx for your time and help :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum