How to display sorted recordset?

  • Thread starter Thread starter Bill Patel
  • Start date Start date
B

Bill Patel

I want to sort the recordset and receive following error:
ADODB.Properties error '800a0cc1'

ADO could not find the object in the collection
corresponding to the name or ordinal reference requested
by the application.

/reported/ASP3.asp, line 30


Code as follows:

<%@ Language=VBScript %>
<%
Function GenerateList
myDSN="DSN=cn_reported;uid=sa;pwd="
mySQL = "SELECT Insured.policy, Insured.effective,
Insured.expiration, " & _
"Insured.status, Insured.audit, Insured.name1,
Insured.policy_prm, " & _
"Insured.total_bill, Insured.total_paid,
Insured.net_due, " & _
"SUM(Rpt_dtl.premium_written) AS
premium_written, " & _
"SUM(Rpt_dtl.commission_amount) AS
commission_amount, " & _
"SUM(Rpt_dtl.inforce_amount) AS
inforce_amount, " & _
"SUM(Rpt_dtl.annual_premium) AS
annual_premium, " & _
"SUM(Rpt_dtl.premium_due) AS premium_due, " & _
"SUM(Rpt_dtl.policy_fee_deducted) AS
policy_fee_deducted " & _
"FROM Insured, Rpt_dtl " & _
"WHERE Insured.policy = Rpt_dtl.policy_number
AND " & _
"Insured.status <> 'IF' AND Insured.audit
= 'N' " & _
"GROUP BY Insured.effective,
Insured.expiration,Insured.policy, " & _
"Insured.status, Insured.audit, Insured.name1,
Insured.policy_prm, " & _
"Insured.total_bill, Insured.total_paid,
Insured.net_due "

SET contemp= server.CreateObject("adodb.connection")
contemp.Open myDSN
SET rstemp=contemp.Execute(mySQL)

IF rstemp.eof THEN
Response.Write "No records found."
Response.Write "<br>Cannot create report ..."
Response.End
ELSE
rstemp.Properties("Optimize") = True
rstemp.sort = "name1"
rstemp.MoveFirst
DIM p_written
DIM c_amount
DIM i_amount
DIM a_premium
DIM p_due
DIM p_fee
DIM t_policy_prm
DIM t_total_bill
DIM t_total_paid
DIM t_net_due
DIM t_p_written
DIM t_c_amount
DIM t_i_amount
DIM t_a_premium
DIM t_p_due
DIM t_p_fee
%>
<TABLE WIDTH=1875 BORDER=1 CELLSPACING=0
CELLPADDING=0>
<TR>
<TH ALIGN="CENTER" WIDTH="150"
bgcolor="silver">Policy Number</b></TH>
<TH ALIGN="CENTER" WIDTH="75"
bgcolor="silver">Effe. <BR> Date</b></TH>
<TH ALIGN="CENTER" WIDTH="75"
bgcolor="silver">Expi. <BR> Date</b></TH>
<TH ALIGN="CENTER" WIDTH="60"
bgcolor="silver">Policy <BR> Status</b></TH>
<TH ALIGN="CENTER" WIDTH="80"
bgcolor="silver">Audit <BR> Completed</b></TH>
<TH ALIGN="CENTER" WIDTH="100"
bgcolor="silver">Policy <BR> Premium</b></TH>
<TH ALIGN="CENTER" WIDTH="100"
bgcolor="silver">Total <BR> Billed</b></TH>
<TH ALIGN="CENTER" WIDTH="100"
bgcolor="silver">Total <BR> Paid</b></TH>
<TH ALIGN="CENTER" WIDTH="100"
bgcolor="silver">Net <BR> Due</b></TH>
<TH ALIGN="CENTER" WIDTH="115"
bgcolor="silver">Premium Written</b></TH>
<TH ALIGN="CENTER" WIDTH="110"
bgcolor="silver">Commission <BR> Amount</b></TH>
<TH ALIGN="CENTER" WIDTH="110"
bgcolor="silver">Inforce <BR> Amount</b></TH>
<TH ALIGN="CENTER" WIDTH="110"
bgcolor="silver">Annual <BR> Premium</b></TH>
<TH ALIGN="CENTER" WIDTH="115"
bgcolor="silver">Premium Due</b></TH>
<TH ALIGN="CENTER" WIDTH="100"
bgcolor="silver">Policy Fee <BR> Deducted</b></TH>
<TH ALIGN="CENTER" WIDTH="375"
bgcolor="silver">Name of Insured</b></TH>
</TR>
<%
DO WHILE NOT rstemp.eof
IF rstemp("total_bill") <> 0 OR rstemp
("total_paid") <> 0 OR rstemp("premium_written") <> 0 OR
rstemp("premium_due") <> 0 THEN
p_written = formatcurrency(rstemp
("premium_written"), -1, -1, 0)
c_amount = formatcurrency(rstemp
("commission_amount"), -1, -1, 0)
i_amount = formatcurrency(rstemp
("inforce_amount"), -1, -1, 0)
a_premium = formatcurrency(rstemp
("annual_premium"), -1, -1, 0)
p_due = formatcurrency(rstemp
("premium_due"), -1, -1, 0)
p_fee = formatcurrency(rstemp
("policy_fee_deducted"), -1, -1, 0)
t_policy_prm = t_policy_prm + rstemp
("policy_prm")
t_total_bill = t_total_bill + rstemp
("total_bill")
t_total_paid = t_total_paid + rstemp
("total_paid")
t_net_due = t_net_due + rstemp("net_due")
t_p_written = t_p_written + rstemp
("premium_written")
t_c_amount = t_c_amount + rstemp
("commission_amount")
t_i_amount = t_i_amount + rstemp
("inforce_amount")
t_a_premium = t_a_premium + rstemp
("annual_premium")
t_p_due = t_p_due + rstemp
("premium_due")
t_p_fee = t_p_fee + rstemp
("policy_fee_deducted")
Response.Write "<tr>"
Response.Write "<td ALIGN=CENTER>" & rstemp
("policy") & "</td>"
Response.Write "<td ALIGN=CENTER>" & rstemp
("effective") & "</td>"
Response.Write "<td ALIGN=CENTER>" & rstemp
("expiration") & "</td>"
Response.Write "<td ALIGN=CENTER>" & rstemp
("status") & "</td>"
Response.Write "<td ALIGN=CENTER>" & rstemp
("audit") & "</td>"
Response.Write "<td ALIGN=RIGHT>" &
formatcurrency(rstemp("policy_prm"), -1, -1, 0) & "</td>"
Response.Write "<td ALIGN=RIGHT>" &
formatcurrency(rstemp("total_bill"), -1, -1, 0) & "</td>"
Response.Write "<td ALIGN=RIGHT>" &
formatcurrency(rstemp("total_paid"), -1, -1, 0) & "</td>"
Response.Write "<td ALIGN=RIGHT>" &
formatcurrency(rstemp("net_due"), -1, -1, 0) & "</td>"
Response.Write "<td ALIGN=RIGHT>" &
p_written & "</td>"
Response.Write "<td ALIGN=RIGHT>" &
c_amount & "</td>"
Response.Write "<td ALIGN=RIGHT>" &
i_amount & "</td>"
Response.Write "<td ALIGN=RIGHT>" &
a_premium & "</td>"
Response.Write "<td ALIGN=RIGHT>" & p_due
& "</td>"
Response.Write "<td ALIGN=RIGHT>" & p_fee
& "</td>"
Response.Write "<td ALIGN=LEFT>" & rstemp
("name1") & "</td>"
Response.Write "</tr>"
END IF
rstemp.movenext
LOOP
rstemp.close
t_policy_prm = formatcurrency(t_policy_prm, -1, -
1, 0)
t_total_bill = formatcurrency(t_total_bill, -1, -
1, 0)
t_total_paid = formatcurrency(t_total_paid, -1, -
1, 0)
t_net_due = formatcurrency(t_net_due, -1, -1,
0)
t_p_written = formatcurrency(t_p_written, -1, -
1, 0)
t_c_amount = formatcurrency(t_c_amount, -1, -1,
0)
t_i_amount = formatcurrency(t_i_amount, -1, -1,
0)
t_a_premium = formatcurrency(t_a_premium, -1, -
1, 0)
t_p_due = formatcurrency(t_p_due, -1, -1, 0)
t_p_fee = formatcurrency(t_p_fee, -1, -1, 0)
%>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td ALIGN=CENTER> Totals </td>
<td ALIGN=RIGHT><% =t_policy_prm %> </td>
<td ALIGN=RIGHT><% =t_total_bill %> </td>
<td ALIGN=RIGHT><% =t_total_paid %> </td>
<td ALIGN=RIGHT><% =t_net_due %> </td>
<td ALIGN=RIGHT><% =t_p_written %> </td>
<td ALIGN=RIGHT><% =t_c_amount %> </td>
<td ALIGN=RIGHT><% =t_i_amount %> </td>
<td ALIGN=RIGHT><% =t_a_premium %> </td>
<td ALIGN=RIGHT><% =t_p_due %> </td>
<td ALIGN=RIGHT><% =t_p_fee %> </td>
</TABLE>
<%
END IF
contemp.Close
SET contemp=NOTHING
End Function
%>

<HTML>
<HEAD>
<META NAME="GENERATOR" CONTENT="Microsoft Visual
Studio 6.0">
<TITLE>Golden Isles Underwriting, Inc.</TITLE>
</HEAD>
<BODY>
<P ALIGN=CENTER><IMG SRC="logo.gif" WIDTH="343"
HEIGHT="93" ></P>
&nbsp;&nbsp;&nbsp;
<FONT SIZE="6"><B>
<P ALIGN=CENTER>Premium Reported to Canal</P>
<% generatelist %>
</BODY>
</HTML>
 
1. You post it in the wrong group. This is the ADO.NET,
not the old ADO.
2. The returned Recordset object is always a read-only,
forward-only cursor. If you need a Recordset object with
more functionality, first create a Recordset object with
the desired property settings, then use the Recordset
object's Open method to execute the query and return the
desired cursor type.
3. Because you don't need more functionality except
sorting, Adding the " Order By Insured.name1 " in your SQL
statements should be enough.


Bin Song, MCP
 
hi Bill,

I do not see any sorting in your code. If you need to sort data, then just
add ORDER BY ... clause to your SQL statement and it will work.
 
Back
Top