Data binding to a stored procedure that returns two result sets

Hi there everyone. I have a stored procedure called "PagingTable" that I use for performing searches and specifying how many results to show per 'page' and which page I want to see. This allows me to do my paging on the server-side (the database tier) and only the results that actually get shown on the webpage fly across from my database server to my web server. The code might look something like this:

strSQL = "EXECUTE PagingTable " & _

"@.ItemsPerPage = 10, " & _

"@.CurrentPage = " &CStr(intCurrentPage) &", " & _

"@.TableName = 'Products', " & _

"@.UniqueColumn = 'ItemNumber', " & _

"@.Columns = 'ItemNumber, Description, ListPrice, QtyOnHand', " & _

"@.WhereClause = '" & strSQLWhere &"'"

The problem is the stored procedure actually returns two result sets. The first result set contains information regarding the total number of results founds, the number of pages and the current page. The second result set contains the data to be shown (the columns specified). In 'classic' ASP I did this like this.

'Open the recordset

rsItems.Open strSQL, conn, 0, 1

'Get the values required for drawing the paging table

intCurrentPage = rsItems.Fields("CurrentPage").Value

intTotalPages = rsItems.Fields("TotalPages").Value

intTotalRows = rsItems.Fields("TotalRows").Value

'Advance to the next recordset

Set rsItems = rsItems.NextRecordset

I am trying to do this now in ASP.NET 2.0 using the datasource control and the repeater control. Any idea how I can accomplish two things:

A) Bind the repeater control to the second resultset

B) Build a "pager" of some sort using the values from the first resultset

Hi,

have you tried setting the DataMember property of the repeater ?

|||

Thanks for the quick reply.

I was thinking about that but I don't know what the second result set would be "called". The results come back as if you executed two SELECT statements one after another without a UNION statement in between.

So what would I set the repeater's DataMember property to? Can I use an integer like

repItems.DataMember = 1 (assuming 0 is the first result set)

|||

If you don't specify a name...:

Table1

Table2

...

TableN