Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Fill Dataset with two tables

Reply
Thread Tools

Fill Dataset with two tables

 
 
Laura K
Guest
Posts: n/a
 
      03-02-2006
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

 
Reply With Quote
 
 
 
 
Bruce Barker
Guest
Posts: n/a
 
      03-02-2006
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) 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
>



 
Reply With Quote
 
 
 
 
Brooke
Guest
Posts: n/a
 
      03-02-2006
Try a union join.

e.g.

SELECT * FROM Table1
UNION
SELECT * FROM Table2



"Laura K" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) 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
>



 
Reply With Quote
 
=?Utf-8?B?YnJpYW5zW01DU0Rd?=
Guest
Posts: n/a
 
      03-02-2006
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
>
>

 
Reply With Quote
 
Laura K
Guest
Posts: n/a
 
      03-02-2006
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

 
Reply With Quote
 
Laura K
Guest
Posts: n/a
 
      03-02-2006
Figured the error out. Added a ,false to the relationship and all is
well...so far. Thanks for all the help.

 
Reply With Quote
 
Patrick.O.Ige
Guest
Posts: n/a
 
      03-03-2006
Good you have seen a light at the end of the tunnel
Patrick


"Laura K" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) oups.com...
> Figured the error out. Added a ,false to the relationship and all is
> well...so far. Thanks for all the help.
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Gridview. Fill dataset with various tables shapper ASP .Net 1 09-19-2007 08:17 AM
Merge two tables of a Dataset in only one Datatable Wasco ASP .Net 3 12-16-2005 03:23 AM
how to bind a DataGrid to a join of two tables in a DataSet? Bennett Haselton ASP .Net 2 10-07-2004 06:00 AM
Newbie question: Fill dataset will ALL tables from database Pierre van Rooyen ASP .Net 3 05-21-2004 07:43 AM
Why Fill creates two datatables in dataset? John Sutter ASP .Net 3 01-13-2004 04:50 PM



Advertisments