|
 |
asp_databases thread: Help needed - using loops within loops to create a table
Message #1 by "donohue ian" <ianmdonohue@c...> on Wed, 23 Oct 2002 23:42:28 +0100
|
|
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
Message #2 by "Samir Chitkara , Gurgaon" <chitkaras@g...> on Thu, 31 Oct 2002 18:08:59 +0530
|
|
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
|
|
|
View Cart
