how to mix 2 recordsets into one result table?

Discussion in 'ASP .Net' started by AFN, Feb 11, 2004.

  1. AFN

    AFN Guest

    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, Feb 11, 2004
    #1
    1. Advertising

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

    --Michael

    "AFN" <> wrote in message
    news:QVtWb.885$...
    > 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, Feb 11, 2004
    #2
    1. Advertising

  3. AFN

    AFN Guest

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


    "Michael Ramey" <raterus@localhost> wrote in message
    news:...
    > How about using DataAdapter to fill a DataSet twice, with each Command.
    > Then bind the Datagrid to the dataset.
    >
    > --Michael
    >
    > "AFN" <> wrote in message
    > news:QVtWb.885$...
    > > 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, Feb 11, 2004
    #3
  4. Sort it?, or create a dataview from the dataset that is sorted the way you
    like it.

    "AFN" <> wrote in message
    news:nnwWb.915$...
    > but how would I intersperse the two recordsets into the right order in the
    > dataset?
    >
    >
    > "Michael Ramey" <raterus@localhost> wrote in message
    > news:...
    > > How about using DataAdapter to fill a DataSet twice, with each Command.
    > > Then bind the Datagrid to the dataset.
    > >
    > > --Michael
    > >
    > > "AFN" <> wrote in message
    > > news:QVtWb.885$...
    > > > 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, Feb 11, 2004
    #4
  5. AFN

    AFN Guest

    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:...
    > Sort it?, or create a dataview from the dataset that is sorted the way you
    > like it.
    >
    > "AFN" <> wrote in message
    > news:nnwWb.915$...
    > > but how would I intersperse the two recordsets into the right order in

    the
    > > dataset?
    > >
    > >
    > > "Michael Ramey" <raterus@localhost> wrote in message
    > > news:...
    > > > How about using DataAdapter to fill a DataSet twice, with each

    Command.
    > > > Then bind the Datagrid to the dataset.
    > > >
    > > > --Michael
    > > >
    > > > "AFN" <> wrote in message
    > > > news:QVtWb.885$...
    > > > > 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, Feb 11, 2004
    #5
  6. 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:...
    >
    >>Sort it?, or create a dataview from the dataset that is sorted the way you
    >>like it.
    >>
    >>"AFN" <> wrote in message
    >>news:nnwWb.915$...
    >>
    >>>but how would I intersperse the two recordsets into the right order in

    >
    > the
    >
    >>>dataset?
    >>>
    >>>
    >>>"Michael Ramey" <raterus@localhost> wrote in message
    >>>news:...
    >>>
    >>>>How about using DataAdapter to fill a DataSet twice, with each

    >
    > Command.
    >
    >>>>Then bind the Datagrid to the dataset.
    >>>>
    >>>>--Michael
    >>>>
    >>>>"AFN" <> wrote in message
    >>>>news:QVtWb.885$...
    >>>>
    >>>>>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
    dilipdotnet at apdiya.com, Feb 11, 2004
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    3
    Views:
    723
  2. Horacius ReX

    Mix different C source files into a single one

    Horacius ReX, Dec 29, 2007, in forum: C Programming
    Replies:
    6
    Views:
    290
    Thad Smith
    Dec 30, 2007
  3. Horacius ReX
    Replies:
    4
    Views:
    238
    Grant Edwards
    Dec 29, 2007
  4. Replies:
    21
    Views:
    442
    Daniel Crichton
    Jan 19, 2009
  5. Michael Tan
    Replies:
    32
    Views:
    910
    Ara.T.Howard
    Jul 21, 2005
Loading...

Share This Page