Fill Dataset with two tables

Discussion in 'ASP .Net' started by Laura K, Mar 2, 2006.

  1. Laura K

    Laura K Guest

    This is probably a simple question but I want to make sure I am doing
    it right.

    I have a spoc with two select statements which results in two tables.

    Very Basic

    ---------------------------------------------------------------------------------
    @strProductCode nvarchar (50)

    select *
    from tblProducts
    where strproductCode = @strProductCode

    select * from tblJctProductColors

    -----------------------------------------------------------------------------------

    I will eventually set up a relationship between the two but first I
    need to get them into one dataset as two tables. I am not sure of the
    code. I have looked through past posts but I am still confused. Can
    someone help. This is what I have so far.

    -------------------------------------------------------------------------------------------------
    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
    System.EventArgs) Handles MyBase.Load
    'retreive the prodcut code
    Dim strProductCode = Request.QueryString("ProductCode")

    'create the connection string
    Dim connection As New SqlConnection(connectionString)
    'Create and initialize the command Object
    Dim command As New SqlCommand("New_getDetailsTables",
    connection)
    command.CommandType = CommandType.StoredProcedure


    ' Add an input parameter and supply a value for it
    command.Parameters.Add("@strProductCode", SqlDbType.VarChar,
    50)
    command.Parameters("@strProductCode").Value = strProductCode


    Dim mySqlDataAdapter As SqlDataAdapter = New
    SqlDataAdapter(command)
    Dim dsGetProducts As New DataSet
    mySqlDataAdapter.Fill(dsGetProducts, "Products")

    DataList1.DataSource = dsGetProducts.Tables("products")
    DataList1.DataBind()

    End Sub

    I have got the dataset returning values from the first table in the
    sproc but I need values from the second table.

    I would like to have two tables from in the dataset. Products and
    colors. The first select statement is products and the second is
    colors. Can someone advise?

    Thanks for any help!


    Laura K
    Laura K, Mar 2, 2006
    #1
    1. Advertising

  2. Laura K

    Bruce Barker Guest

    your code is currently loading two tables. the first table "Products"
    contrain the data from tblProduct. the second table "Products_1" (auto
    named) contains the data from the table tblJctProductColors. you can rename
    the second table after the fill.

    -- bruce (sqlwork.com)




    "Laura K" <> wrote in message
    news:...
    > This is probably a simple question but I want to make sure I am doing
    > it right.
    >
    > I have a spoc with two select statements which results in two tables.
    >
    > Very Basic
    >
    > ---------------------------------------------------------------------------------
    > @strProductCode nvarchar (50)
    >
    > select *
    > from tblProducts
    > where strproductCode = @strProductCode
    >
    > select * from tblJctProductColors
    >
    > -----------------------------------------------------------------------------------
    >
    > I will eventually set up a relationship between the two but first I
    > need to get them into one dataset as two tables. I am not sure of the
    > code. I have looked through past posts but I am still confused. Can
    > someone help. This is what I have so far.
    >
    > -------------------------------------------------------------------------------------------------
    > Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
    > System.EventArgs) Handles MyBase.Load
    > 'retreive the prodcut code
    > Dim strProductCode = Request.QueryString("ProductCode")
    >
    > 'create the connection string
    > Dim connection As New SqlConnection(connectionString)
    > 'Create and initialize the command Object
    > Dim command As New SqlCommand("New_getDetailsTables",
    > connection)
    > command.CommandType = CommandType.StoredProcedure
    >
    >
    > ' Add an input parameter and supply a value for it
    > command.Parameters.Add("@strProductCode", SqlDbType.VarChar,
    > 50)
    > command.Parameters("@strProductCode").Value = strProductCode
    >
    >
    > Dim mySqlDataAdapter As SqlDataAdapter = New
    > SqlDataAdapter(command)
    > Dim dsGetProducts As New DataSet
    > mySqlDataAdapter.Fill(dsGetProducts, "Products")
    >
    > DataList1.DataSource = dsGetProducts.Tables("products")
    > DataList1.DataBind()
    >
    > End Sub
    >
    > I have got the dataset returning values from the first table in the
    > sproc but I need values from the second table.
    >
    > I would like to have two tables from in the dataset. Products and
    > colors. The first select statement is products and the second is
    > colors. Can someone advise?
    >
    > Thanks for any help!
    >
    >
    > Laura K
    >
    Bruce Barker, Mar 2, 2006
    #2
    1. Advertising

  3. Laura K

    Brooke Guest

    Try a union join.

    e.g.

    SELECT * FROM Table1
    UNION
    SELECT * FROM Table2



    "Laura K" <> wrote in message
    news:...
    > This is probably a simple question but I want to make sure I am doing
    > it right.
    >
    > I have a spoc with two select statements which results in two tables.
    >
    > Very Basic
    >
    > ---------------------------------------------------------------------------------
    > @strProductCode nvarchar (50)
    >
    > select *
    > from tblProducts
    > where strproductCode = @strProductCode
    >
    > select * from tblJctProductColors
    >
    > -----------------------------------------------------------------------------------
    >
    > I will eventually set up a relationship between the two but first I
    > need to get them into one dataset as two tables. I am not sure of the
    > code. I have looked through past posts but I am still confused. Can
    > someone help. This is what I have so far.
    >
    > -------------------------------------------------------------------------------------------------
    > Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
    > System.EventArgs) Handles MyBase.Load
    > 'retreive the prodcut code
    > Dim strProductCode = Request.QueryString("ProductCode")
    >
    > 'create the connection string
    > Dim connection As New SqlConnection(connectionString)
    > 'Create and initialize the command Object
    > Dim command As New SqlCommand("New_getDetailsTables",
    > connection)
    > command.CommandType = CommandType.StoredProcedure
    >
    >
    > ' Add an input parameter and supply a value for it
    > command.Parameters.Add("@strProductCode", SqlDbType.VarChar,
    > 50)
    > command.Parameters("@strProductCode").Value = strProductCode
    >
    >
    > Dim mySqlDataAdapter As SqlDataAdapter = New
    > SqlDataAdapter(command)
    > Dim dsGetProducts As New DataSet
    > mySqlDataAdapter.Fill(dsGetProducts, "Products")
    >
    > DataList1.DataSource = dsGetProducts.Tables("products")
    > DataList1.DataBind()
    >
    > End Sub
    >
    > I have got the dataset returning values from the first table in the
    > sproc but I need values from the second table.
    >
    > I would like to have two tables from in the dataset. Products and
    > colors. The first select statement is products and the second is
    > colors. Can someone advise?
    >
    > Thanks for any help!
    >
    >
    > Laura K
    >
    Brooke, Mar 2, 2006
    #3
  4. Hello Laura,

    If you have a stored procedure with two select statements in it:

    =-=-=-=-
    ....
    select *
    from tblProducts
    where strproductCode = @strProductCode

    select * from tblJctProductColors
    ....
    =-=-=-=-

    and you call SqlDataAdapter.Fill(dataSet), you will find there are two
    tables (rowsets) returned upon return from the Fill() method (provided both
    select statements return data. If you want to examine the DataSet, you can
    set a breakpoint and open a watch window to verify the contents of the
    dataSet object.)

    You can then name the tables and specify the relationship between the tables
    using syntax similar to:

    =-=-=-=-=-=-
    dataSet.Table[0].TableName = "Products";
    dataSet.Table[1].TableName = "Colors";

    dataSet.Relations.Add("ProductColor",
    dsTables["Products"].Columns["ProductID"],
    dsTables["ProductColor"].Columns["ColorID"]);
    =-=-=-=-=-=-

    Hope this helps.
    --
    brians
    http://www.limbertech.com


    "Laura K" wrote:

    > This is probably a simple question but I want to make sure I am doing
    > it right.
    >
    > I have a spoc with two select statements which results in two tables.
    >
    > Very Basic
    >
    > ---------------------------------------------------------------------------------
    > @strProductCode nvarchar (50)
    >
    > select *
    > from tblProducts
    > where strproductCode = @strProductCode
    >
    > select * from tblJctProductColors
    >
    > -----------------------------------------------------------------------------------
    >
    > I will eventually set up a relationship between the two but first I
    > need to get them into one dataset as two tables. I am not sure of the
    > code. I have looked through past posts but I am still confused. Can
    > someone help. This is what I have so far.
    >
    > -------------------------------------------------------------------------------------------------
    > Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
    > System.EventArgs) Handles MyBase.Load
    > 'retreive the prodcut code
    > Dim strProductCode = Request.QueryString("ProductCode")
    >
    > 'create the connection string
    > Dim connection As New SqlConnection(connectionString)
    > 'Create and initialize the command Object
    > Dim command As New SqlCommand("New_getDetailsTables",
    > connection)
    > command.CommandType = CommandType.StoredProcedure
    >
    >
    > ' Add an input parameter and supply a value for it
    > command.Parameters.Add("@strProductCode", SqlDbType.VarChar,
    > 50)
    > command.Parameters("@strProductCode").Value = strProductCode
    >
    >
    > Dim mySqlDataAdapter As SqlDataAdapter = New
    > SqlDataAdapter(command)
    > Dim dsGetProducts As New DataSet
    > mySqlDataAdapter.Fill(dsGetProducts, "Products")
    >
    > DataList1.DataSource = dsGetProducts.Tables("products")
    > DataList1.DataBind()
    >
    > End Sub
    >
    > I have got the dataset returning values from the first table in the
    > sproc but I need values from the second table.
    >
    > I would like to have two tables from in the dataset. Products and
    > colors. The first select statement is products and the second is
    > colors. Can someone advise?
    >
    > Thanks for any help!
    >
    >
    > Laura K
    >
    >
    =?Utf-8?B?YnJpYW5zW01DU0Rd?=, Mar 2, 2006
    #4
  5. Laura K

    Laura K Guest

    Great it is coming together. I have the tables under control. Now I am
    working on the relationship. I have received the following error


    T"hese columns don't currently have unique values."


    My updated code is as follows:

    Dim dsGetProducts As New DataSet
    mySqlDataAdapter.Fill(dsGetProducts, "Products")

    dsGetProducts.Tables(0).TableName = "Products"
    dsGetProducts.Tables(1).TableName = "Colors"

    dsGetProducts.Relations.Add("ProductsToColors",
    dsGetProducts.Tables("products").Columns("intProductID"),
    dsGetProducts.Tables("colors").Columns("intProductID"))

    Now this is a parent child relationship. The product table returns a
    list of items which match the productcode. ProductID is the PK. The
    color table has productID as a FK. and should return a list of colors
    for each productID.

    I am obviously missing something.

    Thanks for the help so far. I see a light at the end of the tunnel
    ---------

    Laura K
    Laura K, Mar 2, 2006
    #5
  6. Laura K

    Laura K Guest

    Figured the error out. Added a ,false to the relationship and all is
    well...so far. Thanks for all the help.
    Laura K, Mar 2, 2006
    #6
  7. Good you have seen a light at the end of the tunnel :)
    Patrick


    "Laura K" <> wrote in message
    news:...
    > Figured the error out. Added a ,false to the relationship and all is
    > well...so far. Thanks for all the help.
    >
    Patrick.O.Ige, Mar 3, 2006
    #7
    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. John Sutter
    Replies:
    3
    Views:
    397
    Natty Gur
    Jan 13, 2004
  2. Pierre van Rooyen
    Replies:
    3
    Views:
    543
    Cor Ligthert
    May 21, 2004
  3. Bennett Haselton
    Replies:
    2
    Views:
    14,292
    Bennett Haselton
    Oct 7, 2004
  4. Wasco
    Replies:
    3
    Views:
    12,264
  5. shapper
    Replies:
    1
    Views:
    767
    Just Me
    Sep 19, 2007
Loading...

Share This Page