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

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

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

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