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" <DE************************@yahoo.com> wrote in message
news:QV***************@twister.socal.rr.com...

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?

[AFN]

but how would I intersperse the two recordsets into the right order in the
dataset?
"Michael Ramey" <raterus@localhost> wrote in message
news:u3**************@TK2MSFTNGP10.phx.gbl...

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

--Michael

"AFN" <DE************************@yahoo.com> wrote in message
news:QV***************@twister.socal.rr.com...

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]

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

"AFN" <DE************************@yahoo.com> wrote in message
news:nn***************@twister.socal.rr.com...

but how would I intersperse the two recordsets into the right order in the
dataset?
"Michael Ramey" <raterus@localhost> wrote in message
news:u3**************@TK2MSFTNGP10.phx.gbl...

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

--Michael

"AFN" <DE************************@yahoo.com> wrote in message
news:QV***************@twister.socal.rr.com...

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?

[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.
"Michael Ramey" <raterus@localhost> wrote in message
news:u2**************@TK2MSFTNGP11.phx.gbl...

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

"AFN" <DE************************@yahoo.com> wrote in message
news:nn***************@twister.socal.rr.com...

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

dataset?
"Michael Ramey" <raterus@localhost> wrote in message
news:u3**************@TK2MSFTNGP10.phx.gbl...

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

--Michael

"AFN" <DE************************@yahoo.com> wrote in message
news:QV***************@twister.socal.rr.com...
> 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?
>
>

[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

AFN wrote:

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.
"Michael Ramey" <raterus@localhost> wrote in message
news:u2**************@TK2MSFTNGP11.phx.gbl...

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

"AFN" <DE************************@yahoo.com> wrote in message
news:nn***************@twister.socal.rr.com...

but how would I intersperse the two recordsets into the right order in
the
dataset?
"Michael Ramey" <raterus@localhost> wrote in message
news:u3**************@TK2MSFTNGP10.phx.gbl...

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

--Michael

"AFN" <DE************************@yahoo.com> wrote in message
news:QV***************@twister.socal.rr.com. ..

>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?
>
>

--
Regards,
Dilip Krishnan
MCAD, MCSD.net
dilipdotnet at apdiya dot com