ADO Recordset problem

Hi,
I am using the COMPUTE function in my SQL statement... How do I retrieve the value from the recordset set.. ie. RS.Fields("sum") doesnt work

Can someone help me please?

Thanks.

:sick:

Could you post your code? What database are yiou using?

Either name the column - Select col1,col2,sum(3) "SumCol" From ...

Then use rs.fields("SumCol")

Or simply use rs.fields(2) - ordinal position in the recordset will work as well...

Quote:

---

Originally Posted by dee-u

Could you post your code? What database are yiou using?

---

Sure thing.

Code:

---

select QuestionNo,test=Count(Answer), Answer from SurveyA where surveyno = 7 and questionno=2 group by Answer,QuestionNo compute sum(count(answer))

---

Using SQL Server database with ASP on server side.

I just dont know how to bring in the "Compute Sum(Count(answer))" value (ABOVE) into my ADO recordset.

Thanks.

Quote:

---

Originally Posted by szlamany

Either name the column - Select col1,col2,sum(3) "SumCol" From ...

Then use rs.fields("SumCol")

Or simply use rs.fields(2) - ordinal position in the recordset will work as well...

---

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

I need to use the COMPUTE function in SQL.

I assume that COMPUTE returns the results as a separate recordset. Have you tried NextRecordset?
Something like this:

VB Code:

Dim rst As Recordset
    Set rst = New Recordset
    rst.Open ("....")
    'do stuff, then get the next recordset
    Set rst = rst.NextRecordset

Quote:

---

Originally Posted by PilgrimPete

I assume that COMPUTE returns the results as a separate recordset. Have you tried NextRecordset?
Something like this:

VB Code:

Dim rst As Recordset
    Set rst = New Recordset
    rst.Open ("....")
    'do stuff, then get the next recordset
    Set rst = rst.NextRecordset

---

Thanks for replying,
however, I get the following error 'Operation is not allowed when the object is closed'

also I dont think I can change use the nextrecord set as I need records from both record sets in a while loop.

e.g.

Code:

---

set rs  =  oconn.execute(strSQL,blah,blah)
set rs2 = rs.nextrecordset

while not eof rs      ' Loop through data on both recordsets.

      rs.fields("Answer")
      rs2.fields(0)
loop

---

If you have query analyzer, you should test the query in QA until it's working.

That will show you how many recordsets you get and what the columns are called and how they are configured...

Quote:

---

Originally Posted by szlamany

If you have query analyzer, you should test the query in QA until it's working.

That will show you how many recordsets you get and what the columns are called and how they are configured...

---

Hi,
I have tested in QA and it appears to work correctly.. I just cant seem to retreive the COMPUTED field on the Web page.. the QA returns the first set of data at the top of the results page and the Comuted field at the bottom.. does this indicate that it is infact 2 seperate recordsets?

Craig.

If there are two sets of headings in the RESULTS PANEL - then they are two recordsets.

You can further verify this by going to the MESSAGE PANEL - you will have two lines that say "xxx rows affected".

Something like this will process the separate recordsets...

VB Code:

Do While rsInquire.State <> 0
    
        lngRecCnt = 0
        
        Do While rsInquire.EOF = False
.
.
.
            rsInquire.MoveNext
        Loop
        
        Set rsInquire = rsInquire.NextRecordset
        
    Loop

Quote:

---

Originally Posted by szlamany

If there are two sets of headings in the RESULTS PANEL - then they are two recordsets.

You can further verify this by going to the MESSAGE PANEL - you will have two lines that say "xxx rows affected".

Something like this will process the separate recordsets...

VB Code:

Do While rsInquire.State <> 0
    
        lngRecCnt = 0
        
        Do While rsInquire.EOF = False
.
.
.
            rsInquire.MoveNext
        Loop
        
        Set rsInquire = rsInquire.NextRecordset
        
    Loop

---

]

unfortunately that wont work with my sql table results.

I am using a test=count of each Answer, ie. how many people said Yes or No.

My results page looks like this.

Question: Do you like apples?

Answer % Responses.
Yes 50 10
No 50 10
Total Responses : 20

so I use the query :

'select test=count(Answer) from SurveyA where surveyno = 7 and questionno=2 group by Answer,QuestionNo compute(Answer)'


basically, I need the value from the Compute answer First for each question, so I can work out the percent of answers selected for each question.

You cannot access both recordsets at the same time. Once you call NextRecordset, the previous recordset is closed.
Move the data from the first recordset into an array (see Recordset.GetRows method). Then get the value from the second recordset and finally process through the array.

Or use With Rollup instead of Computed. You will only get one recordset with the following statement

Code:

---

select CustomerId, test=Count(OrderId)
From Orders
group by CustomerId With Rollup

---

With Rollup creates a total for each group and a grand total. Also check out the Grouping aggregate function.