Fill Dataset with two tables

L

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
 
B

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)
 
G

Guest

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.
 
L

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("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
 
L

Laura K

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

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,734
Messages
2,569,441
Members
44,832
Latest member
GlennSmall

Latest Threads

Top