Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Enterprise
Expertise: Intermediate
Jul 17, 2000



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Dealing with Multi-Recordsets Returned by Compound SQL Statements

Everyone can deal with one recordset easily. However, if a SQL statement is like this: "SELECT a, b FROM Table1 COMPUTER SUM(a), SUM(b)," looping though the recordset will not return the aggregate value.

Actually, this SQL will return three recordsets—one by one. Either "EXECUTE of Command object" or "OPEN of Recordset object" will get only the first recordset. In our case, it is the "SELECT a, b FROM table1."

We'll use an important recordset method to retrieve the aggregate value. It is "NEXTRECORDSET." Here is the syntax:

 Set NewRecordset = CurrentRecordset.NextRecordSet
This will populate the SUM(a) result to NewRecordset and clear the "SELECT" result, and so on. You can get the SUM(b) the same way.

After that, you still can use the NextRecordSet method, but no new recordset can be retrieved. You won't get error message, however. Test for this case by verifying that the BOF and EOF properties are both True. Based on the documentation, this method is N/A to the client-side recordset object.

Ning Hao
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date