exception in do while record=rs("record")

[amrigo]

Hi
I want to list below each org. each project from this org.and below each project
the subprojects from that project

my script is giving exception error at the line:
Do While project = rs("name_project")

What can be wrong ?

thank´s in advance


dim total_proj, total, lines_sp, lines_proj, lines_total, name_org, project, cod_project, cod_sp, name_sp, city, valor

total = ""
total_proj = 0
lines_sp = ""
lines_proj = ""
lines_total = ""
lines_proj_org = ""
project = ""
cod_project = ""
cod_sp = ""
name_sp = ""
name_org = ""
city = ""
valor = ""
response.write("<table>")
Set rs = conn.Execute( strSQL )

Do While NOT rs.EOF
name_org=rs("name_org")
total_proj=0
lines_proj=""

'org
Do While name_org = rs("name_org")

project = rs("name_project")
cod_project = rs("cod_project")

'subprojects
Do While project = rs("name_project")

cod_sp = rs("cod_subproject")
name_sp = rs("name_subproject")
city = rs("municipio")
valor = rs("valor")
total = total_proj+valor
Response.Write (""&lines_sp& "<tr><td>"& cod_sp&"</td><td>"&name_sp&"</td><td>"&city&"</td><td>"&valor&"</td></tr>")
'lines_sp = lines_sp& "<tr><td>"& cod_sp &"</td><td>"&name_sp&"</td><td>"&city&"</td><td>"&valor&"</td><td>"
rs.MoveNext

Loop
Response.Write ("<tr><td>"&cod_project&"</tr><td>"&name_project&"</td></tr>")
'lines_proj = "<tr><td>"&cod_project&"</tr><td>"&name_project&"</td></tr>"
'lines_proj_org = lines_proj_org&lines_proj&lines_sp&total
total_proj = total_proj+total
rs.MoveNext

Loop

lines_total = lines_total&name_org & "<br>" &lines_proj_org& total_proj
rs.MoveNext
Loop

'response.write("<table>")
response.write lines_total
response.write("</table>")

[CardboardHammer]

On the last "name_org", you hit the end of the recordset and then try to do a read.

Just before the end of the inner loop, check for rs.EOF and break out of the inner loop if it is true.

[lmf232s]

you could try to catch it like this


Do while not rs.EOF
If project = rs("name_project") then
cod_sp = rs("cod_subproject")
name_sp = rs("name_subproject")
city = rs("municipio")
valor = rs("valor")
total = total_proj+valor
Response.Write (""&lines_sp& "<tr><td>"& cod_sp&"</td><td>"&name_sp&"</td><td>"&city&"</td><td>"&valor&"</td></tr>")
'lines_sp = lines_sp& "<tr><td>"& cod_sp &"</td><td>"&name_sp&"</td><td>"&city&"</td><td>"&valor&"</td><td>"
end if
rs.MoveNext
Loop

[amrigo]

i]Originally posted by CardboardHammer [/i]
On the last "name_org", you hit the end of the recordset and then try to do a read.

[Just before the end of the inner loop, check for rs.EOF and break out of the inner loop if it is true.

how is it done?
Thank´´s in advance

[CardboardHammer]

If rs.EOF Then Exit Do

[amrigo]

in wich line of the script you suggest to use If rs.EOF Then
Exit Do?

I attempt to build a new version but this version does not disply the sub projects :

Set rs = conn.Execute( strSQL )

'Existem registros?
If NOT rs.EOF Then
%>
<table border="0" align="center" cellpadding="4" cellspacing="2">
<%

Do While NOT rs.EOF
'Novo orgão?
If orgao<>rs("orgao") Then

Response.Write( "<tr><td class='texto' colspan='3' bgcolor='EFEFEF'><b>" & rs("orgao") & "</b></td><tr>" )

'Novo projeto?
If projeto<>rs("projeto") Then

Response.Write( "<tr><td class='texto' colspan='3'><b>"& rs("cod_projeto") &"&nbsp;&nbsp;&nbsp;&nbsp;" & rs("projeto") & "</b></td><tr>" )

Do While cod_projeto=rs("cod_projeto")
Response.Write( "<tr><td class='texto'>" & rs("subprojeto") & "</td><td class='texto' align='right' >" & rs("municipio") & "</td><td class='texto'>" & rs("valor") & "</td><tr>" )
If rs.EOF Then
Exit Do
Else
rs.MoveNext
End if

Loop
End If
End If

'Atualiza registros
orgao = rs("orgao")
projeto = rs("projeto")
cod_projeto = rs("cod_projeto")

rs.MoveNext
Loop
Response.Write( "</table>" )
Else
Response.Write( "<b>Não existem registro a listar</b>" )
End If

%>

[lmf232s]

Hey just took your code and put in a page to look at it
color coded.

Im not sure you have the best logic to do what you want.

You are looking at 3 do while loops where you say
do while not objRS.EOF.

It will drop into the first loop but then when it hits the
second do loop it will stay in their until it reaches EOF.
then when you come out of it its going to try to move to the
next record and create an error and even if it makes it past
that one it will hit the last movenext and create an error.

Im not sure and i may be overlooking something but you logic
does not seem like the best approach.

Here is your code indented.

<%
Set rs = conn.Execute( strSQL )
Do While NOT rs.EOF
name_org=rs("name_org")
total_proj=0
lines_proj=""
'org
Do While name_org = rs("name_org")
project = rs("name_project")
cod_project = rs("cod_project")
'subprojects
Do While project = rs("name_project")
cod_sp = rs("cod_subproject")
name_sp = rs("name_subproject")
city = rs("municipio")
valor = rs("valor")
total = total_proj+valor
Response.Write (""&lines_sp& "<tr><td>"& cod_sp&"</td><td>"&name_sp&"</td><td>"&city&"</td><td>"&valor&"</td></tr>")
rs.MoveNext
Loop
Response.Write ("<tr><td>"&cod_project&"</tr><td>"&name_project&"</td></tr>")
total_proj = total_proj+total
rs.MoveNext
Loop
lines_total = lines_total&name_org & "<br>" &lines_proj_org& total_proj
rs.MoveNext
Loop
response.write lines_total
response.write("</table>")
%>


You may want to take another apporach.

[lmf232s]

Ok im not sure because i just wipped this together,
but i think it would be easier to execute another qury
with in the your first do loop to get the subprojects.
Also i may be looking at this wrong so you may actually
be right but
take a look at this and tell me what you think.


<%
Set rs = conn.Execute( strSQL )
If NOT rs.EOF
name_org=rs("name_org")
total_proj=0
lines_proj=""
'org
Do While name_org = rs("name_org") or rs.EOF
project = rs("name_project")
cod_project = rs("cod_project")
'subprojects

'execute your subprojects query hear for this org
SQL = "SELECT * FROM ? WHERE Org = '" & rs("name_org") & "'"
set cs = conn.Execute(SQL)
Do While not objRS.EOF
cod_sp = rs("cod_subproject")
name_sp = rs("name_subproject")
city = rs("municipio")
valor = rs("valor")
total = total_proj+valor
Response.Write (""&amp;lines_sp&amp; "&lt;tr&gt;&lt;td&gt;"&amp; cod_sp&amp;"&lt;/td&gt;&lt;td&gt;"&amp;name_sp&amp;"&lt;/td&gt;&lt;td&gt;"&amp;city&amp;"&lt;/td&gt;&lt;td&gt;"&amp;valor&amp;"&lt;/td&gt;&lt;/tr&gt;")
rs.MoveNext
Loop
cs.Close

Response.Write ("<tr><td>"&cod_project&"</tr><td>"&name_project&"</td></tr>")
total_proj = total_proj+total
rs.MoveNext
Loop
lines_total = lines_total&name_org & "<br>" &lines_proj_org& total_proj
else
'No records were returned
end if
response.write lines_total
response.write("</table>")
%>

[CardboardHammer]

Making queries while looping sucks for performance and should not be done when reasonably avoidable. It never should be used as a substitute for a join.

[CardboardHammer]

Do While NOT rs.EOF
name_org=rs("name_org")
total_proj=0
lines_proj=""

'org
Do While name_org = rs("name_org")

project = rs("name_project")
cod_project = rs("cod_project")

'subprojects
Do While project = rs("name_project")

cod_sp = rs("cod_subproject")
name_sp = rs("name_subproject")
city = rs("municipio")
valor = rs("valor")
total = total_proj+valor
Response.Write (""&lines_sp& "<tr><td>"& cod_sp&"</td><td>"&name_sp&"</td><td>"&city&"</td><td>"&valor&"</td></tr>")
'lines_sp = lines_sp& "<tr><td>"& cod_sp &"</td><td>"&name_sp&"</td><td>"&city&"</td><td>"&valor&"</td><td>"
rs.MoveNext
If rs.EOF Then Exit Do
Loop
Response.Write ("<tr><td>"&cod_project&"</tr><td>"&name_project&"</td></tr>")
'lines_proj = "<tr><td>"&cod_project&"</tr><td>"&name_project&"</td></tr>"
'lines_proj_org = lines_proj_org&lines_proj&lines_sp&total
total_proj = total_proj+total
'rs.MoveNext THIS LINE MUST BE REMOVED (you're already on the next record when you fell out of the previous loop)
If rs.EOF Then Exit Do
Loop

lines_total = lines_total&name_org & "<br>" &lines_proj_org& total_proj
'rs.MoveNext THIS LINE MUST BE REMOVED (you're already on the next record when you fell out of the previous loop)
Loop

[amrigo]

i am using CardboardHammer version and it´s working thank´s a lot

i agree that one query is more fast than two.

My only doubt is about wich aproach is faster is printing the results while they are being processed in every loop or concatenate and print just one time at the final ?

[CardboardHammer]

Actually, the other way wouldn't be 2 queries, it would be 1 + n queries, where n is the number of rows returned by the first query.

You should just Response.write every record as you hit it. Building it up in a string as you loop through is a very inefficient way to do it, due to the mechanism used for concatonating to a string. You can set Response.buffer to false to turn off output buffering, or you can use Response.flush to periodically (after each project is processed, for instance) flush pending output to the client when Response.buffer is set to true.

[amrigo]

Originally posted by CardboardHammer
Actually, the other way wouldn't be 2 queries, it would be 1 + n queries, where n is the number of rows returned by the first query.

You should just Response.write every record as you hit it. Building it up in a string as you loop through is a very inefficient way to do it, due to the mechanism used for concatonating to a string. You can set Response.buffer to false to turn off output buffering, or you can use Response.flush to periodically (after each project is processed, for instance) flush pending output to the client when Response.buffer is set to true.

What´s the idea here? This will bring the results more faster than just using response write? that´s it?

[CardboardHammer]

You use Response.write either way.

Turning buffering on improves performance, at the expense that output is only sent to the client after the page is completely processed or when Response.flush is called, at which point, the content of the buffer is sent.

If the page processes quickly, there's no need to flush. However, if the page takes a long time to completely process, it's a good idea to flush periodically, so the client sees that progress is being made and doesn't just assume that nothing is happening and give up or retry.

[amrigo]

Thank´s a lot for all the answers.


Best regards