recordset issue

[wbodger]

I have a page that does two queries, the second using a value from the first, looping thru two different tables for different reasons. My problem is that there may not be a record in the second table (which is fine), but my code doesn't want to recognize that there is no recordset. Here is the code:

sql2 = "select insert_date, pmt_amount, payor, description as descr from ycmhome.event_pmts where registration_id = " & regid & " ORDER BY registration_id "
sql2 = sql2 & "compute sum(pmt_amount) by registration_id"

set rs2 = conn.Execute(sql2)

    <% rs2.MoveFirst %>

    <% Do Until rs2.eof %>
    <tr>
        <td colspan="6">Additional Information:</td></tr>
    <tr>
        <th class="a">Date</th><th class="a">Name</th><th class="a">Amount</th><th class="a">Description</th><td colspan="2">&nbsp;</td></tr>
    <tr>
        <td><%=rs2("insert_Date") %></td><td><%=rs2("payor") %></td><td><%=formatcurrency(rs2("pmt_amount"),2) %></td><td><%=rs2("descr") %></td></tr>
    <%rs2.MoveNext
    Loop %>
    <% set rs2 = rs2.NextRecordSet() %>
    <% Dim total_amount : total_amount = rs2(0) %>
    <tr>
        <td colspan="2">Totals</td><td><%=formatcurrency(total_amount,2) %></td><td colspan="3">&nbsp;</td></tr>
    <% rs2.close
    set rs2 = Nothing %>

My code works fine when there is a record in event_pmts, but if there is no matching record, then I get the

'Item cannot be found in the collection corresponding to the requested name or ordinal.'

What am I missing or forgetting? EOF doesn't work, the recordcount always comes back as -1 and I am out of ideas. How can I skip this section of vbscript if the recordset is empty? Any help out there?

wb

 

[ChrisHirst]

Reading the FAQs here is always a good start

faq333-4615

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[wbodger]

Yup, that helped, thanks, Chris. Now, the other part of that is why would EOF not work? I know that the query returns nothing, which should put me at the end of the file, should it not?

 

[ChrisHirst]

You need to test for EOF and BOF.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[wbodger]

In other iterations I did test for both bof and eof (and jsut tried it again) and that did not work either. My thought is that it has to do with the compute clause, but I am not sure about that and was hoping somebody could shed some light on that situation.

 

[BigRed1212]

You need to test for EOF and BOF."

For a freshly opened recordset, if the recordset has a record then both EOF and BOF are false. One need not check both.

I understand the arguments for good programming practice and the possibility of working with a recordset that is not freshly opened and may have a pointer in an unknown position.

I generally write and test the old fashioned way like this:
rs.open sql, sConnString
if rs.Eof then
'whatever you want to do when there are no records
else
'whatever you want to do when there are records
end if

I don't know if a structure like that (which ould be nested) might be helpful for you.

 

[wbodger]

That is what I do, the issue comes with the compute clause, which creates another recordset even if the initial recordset is empty. For instance, I have

sql="select insert_date, pmt_amount, payor, description as descr "
sql = sql & "from ycmhome.event_pmts where registration_id = " & regid & 
sql = sql & " ORDER BY registration_id
sql = sql & " compute sum(pmt_amount) by registration_id "

set rs = conn.execute(sql)

The first recordset may be empty, there are no records in that table corresponding to that ID, but since there is a compute clause it automatically (and I don't really understand this) creates another recordset. So, now there are two empty recordsets, but rs.eof is not true. I was able to make it work how I wanted to by opening a client cursor and checking the recordcount (it is =1 even though the recordset is empty).

 

[BigRed1212]

I think you only have one recordset- rs.

The compute clause, although I have never used it, appears to "generate totals that appear as additional summary columns at the end of the result set."

To me, this suggests that your query returns nothing and then the compute clause appends a summary of that nothing.

That would seem to me to be a valid record count of 1. That would in turn suggest that when that recordset is freshly opened rs.eof would be false as there would be a record (the compute add on).

The stuff I read also says "This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature [compute] in new development work, and modify applications that currently use this feature as soon as possible. Use ROLLUP instead."

 

[wbodger]

There is one recordset and two result sets

"When COMPUTE is specified with the optional BY clause, there are two result sets for each group that qualifies for the SELECT:

The first result set for each group has the set of detail rows that contain the select list information for that group.

The second result set for each group has one row that contains the subtotals of the aggregate functions specified in the COMPUTE clause for that group."

I could not quickly get Rollup to work like Compute By, so I will have to spend more time looking into that to see if I can get it match the functionality that I get with the compute by and aggregates (like sum).

 

[BigRed1212]

Well, I can't help you there. Like I say, I haven't used Compute, much less Rollup. LOL.

A recordcount of 1 indicating a query with no returned records and a compute add on summarizing that makes sense to me so I am glad you were able to get a working solution.