Oracle22
12-05-2005, 02:16 AM
Can someone help me out with this program. I would like to display total sum insured per insured through looping.
<html>
<Head>
</Head>
<Body bgcolor="#FFFFFF">
<P ALIGN=Center>
<H1><font color="blue"><Center>Multi-Purpose Insurans Bhd<Center></font></H1>
<H2><Center>Total Transaction (Weekly/Monthly)</Center></h2>
<P ALIGN=RIGHT> <A HREF="Menu Selection.html">Main Menu</A>
</P>
'<!-- C01 Retreive the input of this page -->
<% Dim frDate
Dim toDate
frDate = Request.QueryString("frDate")
toDate = Request.QueryString("toDate")
%>
<form action="Weekly_Report.asp" method="get">
<table>
<tr>
<td><b>Date of Offer:</b></td>
</tr>
<tr>
<td>From Date:
<td><input name="frDate" size="8" value="<%= frDate %>" /></td>
<td>(mm/dd/yyyy)
</tr>
<tr>
<td>To Date: </td>
<td><input name="toDate" size="8" value="<%= toDate %>" /></td>
<td>(mm/dd/yyyy) </td>
</tr>
<tr>
<td><b>Business Type:</b> </td>
<td><input type="radio" name="BUSS_TYPE" value="NEW"> NEW </td>
<td><input type="radio" name="BUSS_TYPE" value="RENEWAL"> RENEWAL</td>
</tr>
<td>
</table>
</form>
<FORM method="GET" action="weekly_report.asp">
<table><tr><td><input type="submit" value="Submit"></td></tr></table>
</form>
<TABLE BORDER="1" align="center">
<br>
<br>
<tr>
<th bgCOLOR=#CCCCCC>No</th>
<th bgCOLOR=#CCCCCC>Acceptance No</th>
<th bgCOLOR=#CCCCCC>Date of Offer</th>
<th bgCOLOR=#CCCCCC>New Biz/Renewal</th>
<th bgCOLOR=#CCCCCC>Cedant</th>
<th bgCOLOR=#CCCCCC>Insured Name</th>
<th bgCOLOR=#CCCCCC>Inception Date</th>
<th bgCOLOR=#CCCCCC>Expiry Date</th>
<th bgCOLOR=#CCCCCC>MPIB Share</th>
<th bgCOLOR=#CCCCCC>Fire</th>
<th bgCOLOR=#CCCCCC>Engr</th>
<th bgCOLOR=#CCCCCC>Motor</th>
<th bgCOLOR=#CCCCCC>MISC</th>
<th bgCOLOR=#CCCCCC>Bond</th>
<th bgCOLOR=#CCCCCC>PAI</th>
<th bgCOLOR=#CCCCCC>Libiality</th>
<th bgCOLOR=#CCCCCC>Oil&Gas</th>
<th bgCOLOR=#CCCCCC>Aviation</th>
<th bgCOLOR=#CCCCCC>Marine Hull</th>
<th bgCOLOR=#CCCCCC>Marine Cargo</th>
<th bgCOLOR=#CCCCCC>Medical</th>
<th bgCOLOR=#CCCCCC>Total Gross Premium</th>
</tr>
<%
Dim Conn, rs, strsql
'Create object. In this case Connection to a database
Set Conn = Server.CreateObject("ADODB.Connection")
'Select provider
'Open the connection
conn.open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &Server.MapPath("Risk_Entry.mdb")&""
'Create recordset
Set Rs = Server.CreateObject("ADODB.Recordset")
strsql = "SELECT Acceptance_No, Date_of_Offer,Buss_Type, Ceding_Co, Insured, inception_date, "_
& "Expiry_date, mpib_share " _
& "from risk_acceptance "_
& "where buss_type ='New' and Risk_Status='Not Taken Up' "_
& "GROUP BY Acceptance_No, Date_of_Offer, Buss_Type, Ceding_Co, Insured, inception_date, "_
& "Expiry_date, mpib_share " _
& "ORDER BY Date_of_Offer"
Rs.Open strSQL,Conn, 1,3
Dim a
Dim Check, CurInsured, PrevInsured, Insured(100)
Dim Fire(100), Engr(100), Motor(100), MISC(100), Bond(100)
Dim PAI(100), LIB(100), OIL(100), Aviation(100), Hull(100), Cargo(100), Med(100)
' Move to the first record
rs.movefirst
a=1
do while not rs.EOF
CurInsured = rs("Insured")
For i=1 to a
If CurInsured = Insured(a) then
Check =1
If rs("Major_Class") = "FIR" then
Fire(a)=Fire(a) + rs("sum_insured")
Else If rs("Major_Class") ="Eng" Then
Engr(a)=Engr(a) + rs("sum_insured")
Else If rs("Major_Class") ="Mot" Then
Motor(a)=Motor(a) + rs("sum_insured")
Else If rs("Major_Class") ="MISC" Then
MISC(a)=MISC(a) + rs("sum_insured")
Else If rs("Major_Class") ="BON" Then
Bond(a)=Bond(a) + rs("sum_insured")
Else If rs("Major_Class") ="PAI" Then
PAI(a)=PAI(a) + rs("sum_insured")
Else If rs("Major_Class") ="LIB" Then
LIB(a)=LIB(a)+ rs("sum_insured")
Else If rs("Major_Class") ="OIL" Then
OIL(a)=OIL(a)+ rs("sum_insured")
Else If rs("Major_Class") ="AVI" Then
AVI(a)=AVI(a) + rs("sum_insured")
Else If rs("Major_Class") ="HUL" Then
HULL(a)=HULL(a) + rs("sum_insured")
Else If rs("Major_Class") ="CGO" Then
CARGO(a)=CARGO(a)+ rs("sum_insured")
Else If rs("Major_Class") ="MED" Then
MED(a)=MED(a)+ rs("sum_insured")
End If
End if
If check =0 then
Insured(a)= rs("Insured")
If rs("Major_Class") = "FIR" then
Fire(a)=rs("sum_insured")
Else If rs("Major_Class") ="Eng" Then
Engr(a)=rs("sum_insured")
Else If rs("Major_Class") ="Mot" Then
Motor(a)=rs("sum_insured")
Else If rs("Major_Class") ="MISC" Then
MISC(a)=rs("sum_insured")
Else If rs("Major_Class") ="BON" Then
Bond(a)=rs("sum_insured")
Else If rs("Major_Class") ="PAI" Then
PAI(a)=rs("sum_insured")
Else If rs("Major_Class") ="LIB" Then
LIB(a)=rs("sum_insured")
Else If rs("Major_Class") ="OIL" Then
OIL(a)=rs("sum_insured")
Else If rs("Major_Class") ="AVI" Then
AVI(a)=rs("sum_insured")
Else If rs("Major_Class") ="HUL" Then
HULL(a)=rs("sum_insured")
Else If rs("Major_Class") ="CGO" Then
CARGO(a)=rs("sum_insured")
Else If rs("Major_Class") ="MED" Then
MED(a)=rs("sum_insured")
End If
End if
Next
a = a + 1
rs.MoveNext ' Movenext
loop
rs.movefirst
do while not rs.EOF
%>
<tr>
<td><%= rs("acceptance_no") %></td>
<td><%= rs("date_of_offer") %></td>
<td><%= rs("buss_type") %></td>
<td><%= rs("ceding_co") %></td>
<td><%= rs("insured") %></td>
<td><%= rs("inception_date") %></td>
<td><%= rs("expiry_date") %></td>
<td><%= rs("mpib_share") %></td>
<td><%= rs("Fire") %></td>
<td><%= rs("Engr") %></td>
<td><%= rs("Motor") %></td>
<td><%= rs("MISC") %></td>
<td><%= rs("Bond") %></td>
<td><%= rs("PAI") %></td>
<td><%= rs("LIB") %></td>
<td><%= rs("OIL") %></td>
<td><%= rs("AVI") %></td>
<td><%= rs("HULL") %></td>
<td><%= rs("CARGO") %></td>
<td><%= rs("Medical") %></td>
<td><%= rs("TotalGrossPremium") %></td>
</tr>
<%
rs.MoveNext ' Movenext
loop
%>
</table>
<%
'Deinitialize the Connection and Recordset
conn.close
set Rs = nothing
Set conn = Nothing
%>
</body>
</html>
ERROR MSG:
Error Type:
Microsoft VBScript compilation (0x800A041F)
Unexpected 'Next'
/RI_Project/Weekly_Report_i.asp, line 231
Next
Please help me as I need a solution ASAP as my dateline is nearby.
PS: Also need help in the SQL query when comes to date range using MS ACCESS!! Really Lost:
strsql = "SELECT Acceptance_No, Date_of_Offer,Buss_Type, Ceding_Co, Insured, inception_date, "_
& "Expiry_date, mpib_share " _
& "from risk_acceptance "_
& "where date_of_offer between #" & frDate & "# and #" & toDate & "# "_
& "GROUP BY Acceptance_No, Date_of_Offer, Buss_Type, Ceding_Co, Insured, inception_date, "_
& "Expiry_date, mpib_share " _
& "ORDER BY Date_of_Offer ASC"
HELP!!! HELP!!!
<html>
<Head>
</Head>
<Body bgcolor="#FFFFFF">
<P ALIGN=Center>
<H1><font color="blue"><Center>Multi-Purpose Insurans Bhd<Center></font></H1>
<H2><Center>Total Transaction (Weekly/Monthly)</Center></h2>
<P ALIGN=RIGHT> <A HREF="Menu Selection.html">Main Menu</A>
</P>
'<!-- C01 Retreive the input of this page -->
<% Dim frDate
Dim toDate
frDate = Request.QueryString("frDate")
toDate = Request.QueryString("toDate")
%>
<form action="Weekly_Report.asp" method="get">
<table>
<tr>
<td><b>Date of Offer:</b></td>
</tr>
<tr>
<td>From Date:
<td><input name="frDate" size="8" value="<%= frDate %>" /></td>
<td>(mm/dd/yyyy)
</tr>
<tr>
<td>To Date: </td>
<td><input name="toDate" size="8" value="<%= toDate %>" /></td>
<td>(mm/dd/yyyy) </td>
</tr>
<tr>
<td><b>Business Type:</b> </td>
<td><input type="radio" name="BUSS_TYPE" value="NEW"> NEW </td>
<td><input type="radio" name="BUSS_TYPE" value="RENEWAL"> RENEWAL</td>
</tr>
<td>
</table>
</form>
<FORM method="GET" action="weekly_report.asp">
<table><tr><td><input type="submit" value="Submit"></td></tr></table>
</form>
<TABLE BORDER="1" align="center">
<br>
<br>
<tr>
<th bgCOLOR=#CCCCCC>No</th>
<th bgCOLOR=#CCCCCC>Acceptance No</th>
<th bgCOLOR=#CCCCCC>Date of Offer</th>
<th bgCOLOR=#CCCCCC>New Biz/Renewal</th>
<th bgCOLOR=#CCCCCC>Cedant</th>
<th bgCOLOR=#CCCCCC>Insured Name</th>
<th bgCOLOR=#CCCCCC>Inception Date</th>
<th bgCOLOR=#CCCCCC>Expiry Date</th>
<th bgCOLOR=#CCCCCC>MPIB Share</th>
<th bgCOLOR=#CCCCCC>Fire</th>
<th bgCOLOR=#CCCCCC>Engr</th>
<th bgCOLOR=#CCCCCC>Motor</th>
<th bgCOLOR=#CCCCCC>MISC</th>
<th bgCOLOR=#CCCCCC>Bond</th>
<th bgCOLOR=#CCCCCC>PAI</th>
<th bgCOLOR=#CCCCCC>Libiality</th>
<th bgCOLOR=#CCCCCC>Oil&Gas</th>
<th bgCOLOR=#CCCCCC>Aviation</th>
<th bgCOLOR=#CCCCCC>Marine Hull</th>
<th bgCOLOR=#CCCCCC>Marine Cargo</th>
<th bgCOLOR=#CCCCCC>Medical</th>
<th bgCOLOR=#CCCCCC>Total Gross Premium</th>
</tr>
<%
Dim Conn, rs, strsql
'Create object. In this case Connection to a database
Set Conn = Server.CreateObject("ADODB.Connection")
'Select provider
'Open the connection
conn.open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &Server.MapPath("Risk_Entry.mdb")&""
'Create recordset
Set Rs = Server.CreateObject("ADODB.Recordset")
strsql = "SELECT Acceptance_No, Date_of_Offer,Buss_Type, Ceding_Co, Insured, inception_date, "_
& "Expiry_date, mpib_share " _
& "from risk_acceptance "_
& "where buss_type ='New' and Risk_Status='Not Taken Up' "_
& "GROUP BY Acceptance_No, Date_of_Offer, Buss_Type, Ceding_Co, Insured, inception_date, "_
& "Expiry_date, mpib_share " _
& "ORDER BY Date_of_Offer"
Rs.Open strSQL,Conn, 1,3
Dim a
Dim Check, CurInsured, PrevInsured, Insured(100)
Dim Fire(100), Engr(100), Motor(100), MISC(100), Bond(100)
Dim PAI(100), LIB(100), OIL(100), Aviation(100), Hull(100), Cargo(100), Med(100)
' Move to the first record
rs.movefirst
a=1
do while not rs.EOF
CurInsured = rs("Insured")
For i=1 to a
If CurInsured = Insured(a) then
Check =1
If rs("Major_Class") = "FIR" then
Fire(a)=Fire(a) + rs("sum_insured")
Else If rs("Major_Class") ="Eng" Then
Engr(a)=Engr(a) + rs("sum_insured")
Else If rs("Major_Class") ="Mot" Then
Motor(a)=Motor(a) + rs("sum_insured")
Else If rs("Major_Class") ="MISC" Then
MISC(a)=MISC(a) + rs("sum_insured")
Else If rs("Major_Class") ="BON" Then
Bond(a)=Bond(a) + rs("sum_insured")
Else If rs("Major_Class") ="PAI" Then
PAI(a)=PAI(a) + rs("sum_insured")
Else If rs("Major_Class") ="LIB" Then
LIB(a)=LIB(a)+ rs("sum_insured")
Else If rs("Major_Class") ="OIL" Then
OIL(a)=OIL(a)+ rs("sum_insured")
Else If rs("Major_Class") ="AVI" Then
AVI(a)=AVI(a) + rs("sum_insured")
Else If rs("Major_Class") ="HUL" Then
HULL(a)=HULL(a) + rs("sum_insured")
Else If rs("Major_Class") ="CGO" Then
CARGO(a)=CARGO(a)+ rs("sum_insured")
Else If rs("Major_Class") ="MED" Then
MED(a)=MED(a)+ rs("sum_insured")
End If
End if
If check =0 then
Insured(a)= rs("Insured")
If rs("Major_Class") = "FIR" then
Fire(a)=rs("sum_insured")
Else If rs("Major_Class") ="Eng" Then
Engr(a)=rs("sum_insured")
Else If rs("Major_Class") ="Mot" Then
Motor(a)=rs("sum_insured")
Else If rs("Major_Class") ="MISC" Then
MISC(a)=rs("sum_insured")
Else If rs("Major_Class") ="BON" Then
Bond(a)=rs("sum_insured")
Else If rs("Major_Class") ="PAI" Then
PAI(a)=rs("sum_insured")
Else If rs("Major_Class") ="LIB" Then
LIB(a)=rs("sum_insured")
Else If rs("Major_Class") ="OIL" Then
OIL(a)=rs("sum_insured")
Else If rs("Major_Class") ="AVI" Then
AVI(a)=rs("sum_insured")
Else If rs("Major_Class") ="HUL" Then
HULL(a)=rs("sum_insured")
Else If rs("Major_Class") ="CGO" Then
CARGO(a)=rs("sum_insured")
Else If rs("Major_Class") ="MED" Then
MED(a)=rs("sum_insured")
End If
End if
Next
a = a + 1
rs.MoveNext ' Movenext
loop
rs.movefirst
do while not rs.EOF
%>
<tr>
<td><%= rs("acceptance_no") %></td>
<td><%= rs("date_of_offer") %></td>
<td><%= rs("buss_type") %></td>
<td><%= rs("ceding_co") %></td>
<td><%= rs("insured") %></td>
<td><%= rs("inception_date") %></td>
<td><%= rs("expiry_date") %></td>
<td><%= rs("mpib_share") %></td>
<td><%= rs("Fire") %></td>
<td><%= rs("Engr") %></td>
<td><%= rs("Motor") %></td>
<td><%= rs("MISC") %></td>
<td><%= rs("Bond") %></td>
<td><%= rs("PAI") %></td>
<td><%= rs("LIB") %></td>
<td><%= rs("OIL") %></td>
<td><%= rs("AVI") %></td>
<td><%= rs("HULL") %></td>
<td><%= rs("CARGO") %></td>
<td><%= rs("Medical") %></td>
<td><%= rs("TotalGrossPremium") %></td>
</tr>
<%
rs.MoveNext ' Movenext
loop
%>
</table>
<%
'Deinitialize the Connection and Recordset
conn.close
set Rs = nothing
Set conn = Nothing
%>
</body>
</html>
ERROR MSG:
Error Type:
Microsoft VBScript compilation (0x800A041F)
Unexpected 'Next'
/RI_Project/Weekly_Report_i.asp, line 231
Next
Please help me as I need a solution ASAP as my dateline is nearby.
PS: Also need help in the SQL query when comes to date range using MS ACCESS!! Really Lost:
strsql = "SELECT Acceptance_No, Date_of_Offer,Buss_Type, Ceding_Co, Insured, inception_date, "_
& "Expiry_date, mpib_share " _
& "from risk_acceptance "_
& "where date_of_offer between #" & frDate & "# and #" & toDate & "# "_
& "GROUP BY Acceptance_No, Date_of_Offer, Buss_Type, Ceding_Co, Insured, inception_date, "_
& "Expiry_date, mpib_share " _
& "ORDER BY Date_of_Offer ASC"
HELP!!! HELP!!!