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 25th, 2005, 08:40 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default Loop Error Handling

Thanks for your response.
I am sorry about this, you feel Inconvenience

Error is

Error Type:
ADODB.Field (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/tabulation/bmhours/FIWMHR2.asp, line 160



How can handle the loop at the following coding.

User want to view the record to insert dated and project no.

following query will use to check the record in table.

1. when any one dept name not found it give error, why ?
2. if any one dept name (record) not found it should display
        other dept records how ?
ie.
dated=Request.Form("dated")
projno=Request.Form("projno")

ssql="select e.dept
,a.projno
,b.dated


from (
select empno
,projno
,mh1 = case when b.weekno = 1 then a.mh1 else 0 end
,MH2 = case when b.weekno = 2 then a.mh1 else 0 end
,MH3 = case when b.weekno = 3 then a.mh1 else 0 end
,MH4 = case when b.weekno = 4 then a.mh1 else 0 end
,mh5 = case when b.weekno = 5 then a.mh1 else 0 end
,total = a.mh1
from mh1 a
JOIN weeks b
ON a.dated = b.dated
) a

JOIN bh b
ON a.empno = b.empno
AND a.projno = b.projno
JOIN Emp e
ON a.empno = e.empno
where b.dated='"& dated &"'
and a.projno='"& projno &"'
group by
e.dept
,a.projno
,b.dated"
set rs=cn.Execute(ssql)
if rs.eof=false then

select the records from the above query and then can dispaly the records by the following queries ?
my problem in loop.
1. how can move the rs("dept") values to following queries ?
2.. if any one rs("dept") (dept name) not found by bove query
(first query) it should not give error
    it should display other dept records how ?


set cn=server.CreateObject("adodb.connection")
set rs=server.CreateObject("adodb.recordset")

cn.ConnectionString="....." // connection string
cn.Open


ssql1="select a.projno, e.Name, e.dept,a.empno,b.dated,b.bh,sum(mh1) mh1 ,sum(MH2) MH2,sum(MH3) MH3,sum(MH4) MH4 ,sum(MH5)

MH5 ,sum(total) total from (select empno ,projno ,mh1 = case when b.weekno = 1 then a.mh1 else 0 end ,MH2 = case when

b.weekno = 2 then a.mh1 else 0 end ,MH3 = case when b.weekno = 3 then a.mh1 else 0 end ,MH4 = case when b.weekno = 4 then

a.mh1 else 0 end ,mh5 = case when b.weekno = 5 then a.mh1 else 0 end ,total = a.mh1 from mh1 a JOIN weeks b ON a.dated =

b.dated) a JOIN bh b ON a.empno = b.empno AND a.projno = b.projno JOIN Emp e ON a.empno = e.empno
where b.dated='"& DATED &"'
and a.projno='"& projno &"'
and e.dept=rs("dept") // here is problem, how dept name came here from the ssql (first query) ?

group by a.projno ,e.dept,e.name,a.empno,b.bh,b.dated"



ssql2="select a.projno, e.Name, e.dept,a.empno,b.dated,b.bh,sum(mh1) mh1 ,sum(MH2) MH2,sum(MH3) MH3,sum(MH4) MH4 ,sum(MH5)

MH5 ,sum(total) total from (select empno ,projno ,mh1 = case when b.weekno = 1 then a.mh1 else 0 end ,MH2 = case when

b.weekno = 2 then a.mh1 else 0 end ,MH3 = case when b.weekno = 3 then a.mh1 else 0 end ,MH4 = case when b.weekno = 4 then

a.mh1 else 0 end ,mh5 = case when b.weekno = 5 then a.mh1 else 0 end ,total = a.mh1 from mh1 a JOIN weeks b ON a.dated =

b.dated) a JOIN bh b ON a.empno = b.empno AND a.projno = b.projno JOIN Emp e ON a.empno = e.empno
where b.dated='"& DATED &"'
and a.projno='"& projno &"'
and e.dept=rs("dept") // here is problem, how dept name came here from the ssql (first query) ?

group by a.projno ,e.dept,e.name,a.empno,b.bh,b.dated"


and same like othere queries. dept name came from the first query ie. rs("dept")

ssql3=" .... "


ssql4="..... "

....
....

ssql10="......"



set rs1=cn.Execute(ssql1)
set rs2=cn.Execute(ssql2)
set rs3=cn.Execute(ssql3)
set rs4=cn.Execute(ssql4)
set rs5=cn.Execute(ssql5)
set rs6=cn.Execute(ssql6)
set rs7=cn.Execute(ssql7)
set rs8=cn.Execute(ssql8)
set rs9=cn.Execute(ssql9)
set rs10=cn.Execute(ssql10)

dim totalBH1, total1, per1
dim totalBH2, total2, per2
dim totalBH3, total3, per3
dim totalBH4, total4, per4
dim totalBH5, total5, per5
dim totalBH6, total6, per6
dim totalBH7, total7, per7
dim totalBH8, total8, per8
dim totalBH9, total9, per9
dim totalBH10, total10, per10

dim GBH, GMH1, GPER


v = 1

Response.Write "<tr>"
Response.Write("<td colspan='6'>" & "<strong>" & "" & rs("dept") & "" & "</strong>" & "</td>") //
Line 160 - here is problem, how dept name came here from the ssql (first query) ?
Response.Write "</tr>"

IF NOT RS1.EOF THEN
do while rs1.eof=false
if v mod 2 = 0 then
Response.Write("<tr bgcolor=#ffffff>")
else
Response.Write("<tr bgcolor=#f8f8ff>")

end if

Response.Write("<td align=center>" & rs1("empno") & "</td>")
Response.Write("<td>" & rs1("Name") & "</td>")

totalBH1 = (totalBH1 + cint(rs1("BH")))

Response.Write("<td align=center>" & rs1("BH") & "</td>")

Response.Write("<td align=center>" & rs1("mh4") & "</td>")

total1 = (total1 + cint(rs1("total")))

Response.Write("<td align=center>" & rs1("total") & "</td>")
Response.Write("<td> </td>")

v = v + 1

rs.movenext

loop

on error resume next

per1 = (total1/totalBH1)*100

per1=round(per1,2)

Response.Write "<tr bgcolor=#fffff0>" & _
"<td colspan='1'>" & "<strong>" &"" & " SUB-TOTAL " & "" &

"</strong>" & _
"<td> </td>" & _
"<td align=center>" & totalBH1 & "</td>" & _
"<td> </td>" & _
"<td align=center>" & total1 & "</td>" & _
"<td align=center>" & per1 & "%" & "</td>" & _
"</tr>"

end if



Response.Write "<tr>"
Response.Write("<td colspan='6'>" & "" & "<strong>" & rs("dept") & "" & "</strong>" &"</td>") // here is problem, how dept name came here from the ssql (first query) ?
Response.Write "</tr>"


IF NOT RS2.EOF THEN
do while rs2.eof=false
if v2 mod 2 = 0 then
Response.Write("<tr bgcolor=#ffffff>")
else
Response.Write("<tr bgcolor=#f8f8ff>")

end if


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

totalBH2 = (totalBH2 + cint(rs2("BH")))

Response.Write("<td align=center>" & rs2("BH") & "</td>")
Response.Write("<td align=center>" & rs2("mh4") & "</td>")

total2 = (total2 + cint(rs2("total")))

Response.Write("<td align=center>" & rs2("total") & "</td>")
Response.Write("<td> </td>")

v2 = v2 + 1

rs2.movenext

loop

per2 = (total2/totalBH2)*100

per2=round(per2,2)

Response.Write "<tr bgcolor=#fffff0>" & _
"<td>" & "<strong>" & "" & " SUB-TOTAL " & "" & "</td>" &

"</strong>" &_
"<td> </td>" & _
"<td colspan='1' align=center>" & totalBH2 & "</td>" & _
"<td> </td>" & _
"<td align=center>" & total2 & "</td>" & _
"<td align=center>" & per2 & "%" & "</td>" & _
"</tr>"

end if


else

Response.Redirect("http://dcilweb/tabulation/Bmhours/FOWMHR.asp?msg=Not+Found")

end if
end if


same like above I have to dislay all records set values
ie.
rs1,rs2,rs3.....to rs10.


Please hlep to solve the loop problem.

regards

Mateen
 
Old May 25th, 2005, 06:03 PM
Friend of Wrox
 
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

Wow are we suppposed to find the error for you? which loop? what error? A resonable ask do you think?

IMO you should post the problematic code only, indicate where and what the problem is (your error). help others help you, sorry if that seems unreasonable.

Wind is your friend
Matt
 
Old May 27th, 2005, 11:21 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I am sorry about this, you feel Inconvenience.

Error is

Error Type:
ADODB.Field (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/tabulation/bmhours/FIWMHR2.asp, line 160


regards.

Mateen


 
Old May 29th, 2005, 07:42 PM
Friend of Wrox
 
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

I see you have edited your original post and place the error/line number there now. There is no need to be sorry, just learn form it. Including the error and line number with your problem will Help others help you.

Your error is telling you the recordSet (RS) you are attempting to use is at the EndOfFile. If you loop thru a RS then use it again further down the page you need to place the cursor at the beginning again - use RS.MoveFirst - now it is available again.

Wind is your friend
Matt
 
Old July 6th, 2005, 09:31 AM
Registered User
 
Join Date: Jun 2005
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

When u have a query to get a record set satisfying a given condition, you need to test if you have some records or not,

If no records have been returned then
the recordset bof and eof are both true










Similar Threads
Thread Thread Starter Forum Replies Last Post
error handling Abhinav_jain_mca General .NET 5 December 21st, 2005 01:12 AM
Error Handling evad Excel VBA 2 August 18th, 2005 03:54 AM
Error handling Hudson40 Access VBA 2 February 11th, 2005 05:32 AM
Error Handling zaeem Classic ASP Basics 2 November 14th, 2003 10:42 AM
Error handling ! ittorget Classic ASP Professional 2 November 14th, 2003 09:44 AM





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