how to mix 2 recordsets into one result table?

[AFN]

Hi. I'm pretty comfortable binding a single recordset result to a
datagrid. But now I have a more unique problem. I have 2 really long
stored procedures that cannot be combined at the SQL Server level (please
don't ask why, but it can't, and even if I could, it would take 10x longer
because they are very involved queries). But I need to mix the recordset
results into one HTML table, and need advice about the best way to do that.
Example:

Table 1
PrimaryID BookName BookPrice
1 Alpha Book $14.00
2 Beta Book $5.00
3 Delta Book $7.00

Table2
ForeignKey BookName BookPrice
1 Animal Book $10.00
2 Mary's Cooking $12.99
2 Mary's Crafts $15.00
3 Fred's Biography $18.00

Desired HTML Table Results after merging the 2 tables, inserting each
matching record from table 2 into table 1, under the record from table 1:

Books For Sale:
Alpha Book $14.00
Animal Book $10.00
Beta Book $5.00
Mary's Cooking $12.99
Mary's Crafts $15.00
Delta Book $7.00
Fred's Biography $18.00


My thoughts? I'm thinking about different ways, and I have no idea what is
fastest, and I also don't want to try them all. For instance, I could get
the 2 recordsets, and then, with code, make my own array, and then bind that
array to a datagrid.

Your suggestions?

 

[Michael Ramey]

How about using DataAdapter to fill a DataSet twice, with each Command.
Then bind the Datagrid to the dataset.

--Michael

 

[AFN]

but how would I intersperse the two recordsets into the right order in the
dataset?

 

[Michael Ramey]

Sort it?, or create a dataview from the dataset that is sorted the way you
like it.

 

[AFN]

if you look at my example, there is no way to sort it. the records with the
same IDs from recordset 1 must show before the records from recordset 2 with
those same IDs.

 

[dilipdotnet at apdiya.com]

How about making the sps return an additional column indicating the
source in the order you wanted it...

so for example you could return

ForeignKey BookName BookPrice TableNo.

Then you could sort on Foreignkey, TableNo.

Let me know if that works