Thanks, I should check my spelling before asking for help!

Ian Donohue
21 Chaucer Close 
Fareham
Portsmouth


-----Original Message-----
From: Samir Chitkara , Gurgaon [mailto:chitkaras@g...] 
Sent: Thursday, October 31, 2002 12:39 PM
To: ASP Databases
Subject: [asp_databases] RE: Help needed - using loops within loops to
cre ate a table

hi!

why ru using not twice?
it should be

Do While Not objRS.eof 


loop

Samir


-----Original Message-----
From: donohue ian [mailto:ianmdonohue@c...]
Sent: Thursday, October 24, 2002 4:12 AM
To: ASP Databases
Subject: [asp_databases] Help needed - using loops within loops to
create a table


I am trying to create a list of records from an Oracle database that
sorts
onto a web page.  The result should provide a list of records that have
been
entered in a week sorted by a particular area 12 in all though in some
weeks
there will not be records for some areas.
 
I have linked to the Oracle database and set up the query that produces
the
right records though in one long list.  I can display the first record
of
the first area but can not get it to loop back for further records and
then
continue onto the next area.
 
This is done on a system at work and unfortunately it does not display
an
error message just the standard 'page not available'
 
Dim adOpenForwardOnly, adLockReadOnly, adCmdTable, Varfirstdate,
Varsecondate , VarWard
            adOpenForwardOnly = 0
            adLockReadOnly = 1
            adCmdTable = 2
                        
            Varfirstdate = Request.Form ("FirstDate")
            Varsecondate = Request.Form ("SecondDate")
            
            Dim objConn, objRS, sqltext
            Set objConn = Server.CreateObject("ADODB.Connection")
            Set objRS = Server.CreateObject("ADODB.Recordset")
            
            Dim strDatabaseType
            strDatabaseType = "ORACLE"
            
            ObjConn.Open "Provider=OraOLEDB.ORACLE;" & _ 
                                                 "Data Source 
OCELLA8;"  &
_
                                                 "User id=***********;"
&_
                                                 "Password=*******;"
 
            ' Dates have been hard coded for testing
            
sqltext = " SELECT REFERENCE, OFFICER, LOCATION_LINE1, LOCATION_LINE2,
LOCATION_LINE3, LOCATION_LINE4, DEV_DESC_LINE1, DEV_DESC_LINE2,
DEV_DESC_LINE3, DEV_DESC_LINE4, APPLICANT_NAME, RECEIVED,
WARD_DISTRICT_PARISH, TYPE_B_OR_P FROM APPLICATION WHERE RECEIVED
BETWEEN
'2002-10-10' AND '2002-10-17' AND TYPE_B_OR_P='P' AND REFERENCE Like
'P%'
And REFERENCE Not Like '%/MA/A' And REFERENCE Not Like '%/DP/%' ORDER BY
WARD_DISTRICT_PARISH "                            
                        
            Response.Write sqltext
 
            
            objRS.Open sqltext, objConn, adOpenForwardOnly,
adLockReadOnly
 
            VarWard = objRS("WARD_DISTRICT_PARISH")
 
            response.write "<table border=0 width=""100%"">"
            response.write "  <tr><td colspan=2 align=center ><b>Weekly
List
For 10 October 2002</b></td></tr>"
            Response.write "  <tr><td><br></td></tr>"
            
            objRS.Movefirst
            
            response.write "  <tr><td colspan=2 align=center ><b>North
Town</b></td></tr>"
            'Do While Not Not objRS.eof '   With this line in I get an
error
however without the loop it only displays the first record
            If VarWard = "FR" Then          
            Response.write "  <tr><td><br></td></tr>"
Response.write "  <tr><td><b>Reference: </b> " & objRS("Reference") & "
<b>
Applicant: </b>" & objRS("Applicant_Name") & " <b> Ward: </b>" &
objRS("WARD_DISTRICT_PARISH") & " <b> Received: </b>" &
objRS("RECEIVED") &
"</td></tr>"
            response.write "  <tr><td><b>Location: </b>" &
objRS("LOCATION_LINE1") & "  " & objRS("LOCATION_LINE2") & "  " &
objRS("LOCATION_LINE3") & "  " & objRS("LOCATION_LINE4") & "</td></tr>"
            response.write "  <tr><td><b>Description: </b>" &
objRS("DEV_DESC_LINE1") & "  " & objRS("DEV_DESC_LINE2") & "  " &
objRS("DEV_DESC_LINE3") & "  " & objRS("DEV_DESC_LINE4") & "</td></tr>"
            Response.write "  <tr><td><hr></td></tr>"
            objRS.movenext
            ' Loop With this line in I get an error however without the
loop
it only displays the first record
            else
            Response.Write" <tr><td colspan=2 align=center ><b>There are
no
applications for this Ward</b></td></tr>"
            end if 
            response.write "</table>"
            
            I then need to move onto the second area and check for any
entries and so on for 12 areas           
 
response.write "  <tr><td colspan=2 align=center ><b>South
Town</b></td></tr>"
            'Do While Not Not objRS.eof '   
            If VarWard = "FS" Then          
            Response.write "  <tr><td><br></td></tr>"
Response.write "  <tr><td><b>Reference: </b> " & objRS("Reference") & "
<b>
Applicant: </b>" & objRS("Applicant_Name") & " <b> Ward: </b>" &
objRS("WARD_DISTRICT_PARISH") & " <b> Received: </b>" &
objRS("RECEIVED") &
"</td></tr>"
            response.write "  <tr><td><b>Location: </b>" &
objRS("LOCATION_LINE1") & "  " & objRS("LOCATION_LINE2") & "  " &
objRS("LOCATION_LINE3") & "  " & objRS("LOCATION_LINE4") & "</td></tr>"
            response.write "  <tr><td><b>Description: </b>" &
objRS("DEV_DESC_LINE1") & "  " & objRS("DEV_DESC_LINE2") & "  " &
objRS("DEV_DESC_LINE3") & "  " & objRS("DEV_DESC_LINE4") & "</td></tr>"
            Response.write "  <tr><td><hr></td></tr>"
            objRS.movenext
            ' Loop 
            else
            Response.Write" <tr><td colspan=2 align=center ><b>There are
no
applications for this Ward</b></td></tr>"
            end if 
            response.write "</table>"
 
            ObjRS.Close
            objConn.Close
            Set objRS = Nothing
            Set objConn = Nothing