Wrox Programmer Forums
|
ASP Pro Code Clinic As of Oct 5, 2005, this forum is now locked. No posts have been deleted. Please use "Classic ASP Professional" at: http://p2p.wrox.com/forum.asp?FORUM_ID=56 for discussions similar to the old ASP Pro Code Clinic or one of the other many remaining ASP and ASP.NET forums here.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP Pro Code Clinic section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 10th, 2004, 02:46 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default Loop problem

set cn=server.CreateObject("adodb.connection")
cn.ConnectionString="" // connection string
cn.Open

ssql="select distinct contractno from approvals"

set rs=cn.Execute(ssql)

do while rs.eof=false

Response.Write(rs("contractno"))

rs.movenext
Loop

contractno=rs("contractno") // store contractno



after that when it store all contractno in a variable ie contractno

how can retreive the records where approvals.contractno='"& contractno &"' by the loop, in following query ?


ssql2="select approvals.contractno,employees.em pno, employees.name, assignments.aposit, assignments.acontract, assignments.astrdate, assignments.aenddate
FROM (employees INNER JOIN approvals ON employees.empno=approvals.empno) INNER JOIN assignments ON employees.empno=assignments.empno
where approvals.contractno=' " & contractno &" ' " // problem is here how can use loop that it retrieve the records base on all contractno,display record one by one

set rs2=cn.Execute(ssql2)
do while rs2.eof=false
Response.Write("<td align=center>" & rs2("contractno") & "</td>")
Response.Write("<td align=center>" & rs2("empno") & "</td>")
Response.Write("<td>" & rs2("name") & "</td>")
Response.Write("<td>" & rs2("aposit") & "</td>")
Response.Write("<td align=center>" & rs2("acontract") & "</td>")
fieldData = Month(rs2("astrdate")) & "/" & Day(rs2("astrdate")) & "/" & Year(rs2("astrdate")) & ""
If fieldData = "//" Then fieldData = "-"
Response.Write("<td align=center>" & fieldData & "</td>")
fieldData = Month(rs2("aenddate")) & "/" & Day(rs2("aenddate")) & "/" & Year(rs2("aenddate")) & ""
If fieldData = "//" Then fieldData = "-"
Response.Write("<td align=center>" & fieldData & "</td>")
rs.movenext
loop


ie. display all records where contractno 221
after that again loop run and display records where contractno 2016
loop run until all contractno records will display.


simply I want that by the loop store contractno in a variable ie contractno.
and after that this value (contractno) retrieve records by the second query ie. ssql2 by loop, loop run untill all contractno
records will display

ie where approvals.contractno='221'
         approvals.contractno='2016'
     approvals.contractno='2002' and so on.........


Please help

Mateen
 
Old May 10th, 2004, 07:17 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Have a nested loop:

do while rs.eof=false

    ssql2="select approvals.contractno,employees.empno, employees.name, assignments.aposit, assignments.acontract, assignments.astrdate, assignments.aenddate
FROM (employees INNER JOIN approvals ON employees.empno=approvals.empno) INNER JOIN assignments ON employees.empno=assignments.empno
where approvals.contractno=' " & rs("contractno") &" ' "

'load second SQL string; this gets changed for every contract number
set rs2=cn.Execute(ssql2)
do while rs2.eof=false

  'do something with data

  rs2.MoveNext
next
rs.movenext
Loop

Hope this helps,

Brian
 
Old May 11th, 2004, 05:45 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks for your response.

I use your loop (just some changing)

<%
set cn=server.CreateObject("adodb.connection")
cn.ConnectionString="" // connection string
cn.Open

ssql="select distinct contractno from approvals"

set rs=cn.Execute(ssql)

do while rs.eof=false


%>

<%

ssql2="SELECT employees.empno, employees.name, approvals.contractno, assignments.aposit, assignments.acontract, assignments.astrdate, assignments.aenddate FROM (employees INNER JOIN approvals ON employees.empno=approvals.empno) INNER JOIN assignments ON employees.empno=assignments.empno where approvals.contractno='"& rs("contractno") &"'"



set rs2=cn.Execute(ssql2)

<h2>Supervision Staff Assignment</h2>

<STRONG>TRIP CONTRACT NO. <%Response.Write(rs("contractno"))%></STRONG><br>
<STRONG>FOR THE MONTH OF <%Response.Write(Month((aenddate)))%>/<%Response.Write(Year((aenddate)))%></STRONG><br>
<STRONG>(A) THE FOLLOWING STAFF FROM CONTRACT NO. <%Response.Write(rs("contractno"))%> HAS BEEN ASSIGNED AS FOLLOWS:</STRONG>


<%

Dim fielddata

do while rs2.eof=false

Response.Write("<tr>")

Response.Write("<td align=center>" & rs2("empno") & "</td>")
Response.Write("<td>" & rs2("name") & "</td>")
Response.Write("<td>" & rs2("aposit") & "</td>")
Response.Write("<td align=center>" & rs2("acontract") & "</td>")
...........
......

rs2.movenext
'next // her it give error message that unexpected next, I remark it and use loop.
loop
rs.movenext
loop

%>


above loop display records like this.

TRIP CONTRACTNO 1
............(empty) // no data show with related to contractno 1


TRIP CONTRACTNO 2
.................... // her it show contractno 1 records. her it should be show contractno 2 records


TRIP CONTRACTNO 3
.................... // her it show contractno 2 records. here it should be show contractno 3 records.

TRIP CONTRACTNO 4
.................. // here is show contractno 3 records here it should be show contractno 4 records.

and so on..............
......


TRIP CONTRACTNO (last contractno)
............... // her it show two contractno records second last and last contractno records




only just siglthly loop problem remain.

when it received contractno 1 it not show contractno records it move to next contractno 2
and in contractno 2 it display contractno 1 records why ?

Please help

Mateen
















Quote:
quote:Originally posted by bmains
 Have a nested loop:

do while rs.eof=false

    ssql2="select approvals.contractno,employees.em pno, employees.name, assignments.aposit, assignments.acontract, assignments.astrdate, assignments.aenddate
FROM (employees INNER JOIN approvals ON employees.empno=approvals.empno) INNER JOIN assignments ON employees.empno=assignments.empno
where approvals.contractno=' " & rs("contractno") &" ' "

'load second SQL string; this gets changed for every contract number
set rs2=cn.Execute(ssql2)
do while rs2.eof=false

'do something with data

rs2.MoveNext
next
rs.movenext
Loop

Hope this helps,

Brian
 
Old May 13th, 2004, 03:59 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks for response.
loop problem solve.

I was place contractno outside the table.
when I use contractno within the loop
then is dislay properly records.

regards

Mateen



Quote:
quote:Originally posted by bmains
 Have a nested loop:

do while rs.eof=false

    ssql2="select approvals.contractno,employees.em pno, employees.name, assignments.aposit, assignments.acontract, assignments.astrdate, assignments.aenddate
FROM (employees INNER JOIN approvals ON employees.empno=approvals.empno) INNER JOIN assignments ON employees.empno=assignments.empno
where approvals.contractno=' " & rs("contractno") &" ' "

'load second SQL string; this gets changed for every contract number
set rs2=cn.Execute(ssql2)
do while rs2.eof=false

'do something with data

rs2.MoveNext
next
rs.movenext
Loop

Hope this helps,

Brian





Similar Threads
Thread Thread Starter Forum Replies Last Post
Have a problem in For-each loop LeoMathew XSLT 2 July 16th, 2008 05:20 AM
loop problem smilesmita Pro PHP 1 November 2nd, 2007 02:15 AM
Loop problem! Please help me!! olud ASP.NET 1.0 and 1.1 Basics 2 March 19th, 2007 07:38 AM
problem of infinite loop sarah lee ASP.NET 1.0 and 1.1 Basics 19 December 14th, 2006 09:26 AM
while loop problem shoakat Classic ASP Databases 1 September 21st, 2004 03:44 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.