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
|
