Fill Dataset with two tables

[Laura K]

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]

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" <kl*****@charter.net> wrote in message
news:11*********************@j33g2000cwa.googlegro ups.com...

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]

Try a union join.

e.g.

SELECT * FROM Table1
UNION
SELECT * FROM Table2

"Laura K" <kl*****@charter.net> wrote in message
news:11*********************@j33g2000cwa.googlegro ups.com...

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

[brians[MCSD]]

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

[Laura K]

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("intProdu ctID"),
dsGetProducts.Tables("colors").Columns("intProduct ID"))

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]

Figured the error out. Added a ,false to the relationship and all is
well...so far. Thanks for all the help.

[Patrick.O.Ige]

Good you have seen a light at the end of the tunnel :)
Patrick
"Laura K" <kl*****@charter.net> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.com...

Figured the error out. Added a ,false to the relationship and all is
well...so far. Thanks for all the help.