Multiple table dataset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a classic scenerio - simliar to the Northwind
Customer/Ordes/OrderDetails. I want to select a customer from a combo box,
display pertinent info, and display in a hierarchical datagrid the orders and
ordertails (in to next layer). I am creating 3 dataadapters, one for each
table. I am creating the customers as a parameter query which I pass the ID
value from the combo box to the query and it pulls up the custumer info
nicely.

Howerver, I can make the Orders dataadapter a parameter query using the
cutomer ID, but can't drill down to the Orders detail using that same ID.

I've put all three dataadapters into one dataset establishing the
relationships.

When I run it, I get an error saying that All the rows can't find related
rows. I know what is happening - it is pulling all the rows from subordinate
table and there is not related records in parent tables, thus violating the
referential integrity of the database.

It will work if I set constraints to false, but I can then not update
subordinate tables.

How can I make this classic senerio work - using winforms in vb.net????

It seems to me that there should be a way to force pulling the only the
orders and orderDetails associated with the customer.

Thanks for your help.
 
Northwind stored procedure (can be tailored for your DB):

CREATE PROCEDURE dbo.GetCustomerOrders
(
@CustomerID nchar(10)
)
AS

SELECT c.CustomerID
, c.CompanyName
, c.ContactName
, c.ContactTitle
, c.Address
, c.City
, c.Region
, c.PostalCode
, c.Country
, c.Phone
, c.Fax
FROM Customers c
WHERE c.CustomerID = @CustomerID

SELECT o.OrderID
, o.CustomerID
, o.EmployeeID
, o.OrderDate
, o.RequiredDate
, o.ShippedDate
, o.ShipVia
, o.Freight
, o.ShipName
, o.ShipAddress
, o.ShipCity
, o.ShipRegion
, o.ShipPostalCode
, o.ShipCountry
FROM Orders o
WHERE o.CustomerID = @CustomerID

SELECT od.OrderID
, od.ProductID
, od.UnitPrice
, od.Quantity
, od.Discount
FROM [Order Details] od
JOIN Orders o
ON od.OrderID = o.OrderID
WHERE o.CustomerID = @CustomerID

In your dataset, add relationships for the tables to easily filter the
information.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
Thanks! I'll give it a try and let you know if it works.

Bill

Cowboy (Gregory A. Beamer) - MVP said:
Northwind stored procedure (can be tailored for your DB):

CREATE PROCEDURE dbo.GetCustomerOrders
(
@CustomerID nchar(10)
)
AS

SELECT c.CustomerID
, c.CompanyName
, c.ContactName
, c.ContactTitle
, c.Address
, c.City
, c.Region
, c.PostalCode
, c.Country
, c.Phone
, c.Fax
FROM Customers c
WHERE c.CustomerID = @CustomerID

SELECT o.OrderID
, o.CustomerID
, o.EmployeeID
, o.OrderDate
, o.RequiredDate
, o.ShippedDate
, o.ShipVia
, o.Freight
, o.ShipName
, o.ShipAddress
, o.ShipCity
, o.ShipRegion
, o.ShipPostalCode
, o.ShipCountry
FROM Orders o
WHERE o.CustomerID = @CustomerID

SELECT od.OrderID
, od.ProductID
, od.UnitPrice
, od.Quantity
, od.Discount
FROM [Order Details] od
JOIN Orders o
ON od.OrderID = o.OrderID
WHERE o.CustomerID = @CustomerID

In your dataset, add relationships for the tables to easily filter the
information.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

Bill K said:
I have a classic scenerio - simliar to the Northwind
Customer/Ordes/OrderDetails. I want to select a customer from a combo box,
display pertinent info, and display in a hierarchical datagrid the orders and
ordertails (in to next layer). I am creating 3 dataadapters, one for each
table. I am creating the customers as a parameter query which I pass the ID
value from the combo box to the query and it pulls up the custumer info
nicely.

Howerver, I can make the Orders dataadapter a parameter query using the
cutomer ID, but can't drill down to the Orders detail using that same ID.

I've put all three dataadapters into one dataset establishing the
relationships.

When I run it, I get an error saying that All the rows can't find related
rows. I know what is happening - it is pulling all the rows from subordinate
table and there is not related records in parent tables, thus violating the
referential integrity of the database.

It will work if I set constraints to false, but I can then not update
subordinate tables.

How can I make this classic senerio work - using winforms in vb.net????

It seems to me that there should be a way to force pulling the only the
orders and orderDetails associated with the customer.

Thanks for your help.
Click to expand...
Click to expand...
 
Cowboy - I am using the data adapter wizard and I am only seeing the first
table in the dataset - with the title of the stored procedure. is there a
way using the wizard - or in code to display all three table in the dataset?
if code - can you provide an example?

Thanks again.

Bill

Cowboy (Gregory A. Beamer) - MVP said:
Northwind stored procedure (can be tailored for your DB):

CREATE PROCEDURE dbo.GetCustomerOrders
(
@CustomerID nchar(10)
)
AS

SELECT c.CustomerID
, c.CompanyName
, c.ContactName
, c.ContactTitle
, c.Address
, c.City
, c.Region
, c.PostalCode
, c.Country
, c.Phone
, c.Fax
FROM Customers c
WHERE c.CustomerID = @CustomerID

SELECT o.OrderID
, o.CustomerID
, o.EmployeeID
, o.OrderDate
, o.RequiredDate
, o.ShippedDate
, o.ShipVia
, o.Freight
, o.ShipName
, o.ShipAddress
, o.ShipCity
, o.ShipRegion
, o.ShipPostalCode
, o.ShipCountry
FROM Orders o
WHERE o.CustomerID = @CustomerID

SELECT od.OrderID
, od.ProductID
, od.UnitPrice
, od.Quantity
, od.Discount
FROM [Order Details] od
JOIN Orders o
ON od.OrderID = o.OrderID
WHERE o.CustomerID = @CustomerID

In your dataset, add relationships for the tables to easily filter the
information.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

Bill K said:
I have a classic scenerio - simliar to the Northwind
Customer/Ordes/OrderDetails. I want to select a customer from a combo box,
display pertinent info, and display in a hierarchical datagrid the orders and
ordertails (in to next layer). I am creating 3 dataadapters, one for each
table. I am creating the customers as a parameter query which I pass the ID
value from the combo box to the query and it pulls up the custumer info
nicely.

Howerver, I can make the Orders dataadapter a parameter query using the
cutomer ID, but can't drill down to the Orders detail using that same ID.

I've put all three dataadapters into one dataset establishing the
relationships.

When I run it, I get an error saying that All the rows can't find related
rows. I know what is happening - it is pulling all the rows from subordinate
table and there is not related records in parent tables, thus violating the
referential integrity of the database.

It will work if I set constraints to false, but I can then not update
subordinate tables.

How can I make this classic senerio work - using winforms in vb.net????

It seems to me that there should be a way to force pulling the only the
orders and orderDetails associated with the customer.

Thanks for your help.
Click to expand...
Click to expand...
 
This is a bit beyond wizard, but here is a quick sample in C# and VB.NET
(check my VB.NET code if you go that route, as I normally code in C# and this
is done without testing the code):

C#
---
string connString = "{your connection string here}";
string sql = "sprocName here";

SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.StoredProcedure;

DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();

da.TableMappings.Add("Table", "Customers");
da.TableMappings.Add("Table1", "Orders");
da.TableMappings.Add("Table2", "OrderDetails");

try
{
conn.Open();
da.Fill(ds);
}
finally
{
if(conn.State == ConnectionState.Open)
conn.Close();

conn.Dispose();
}


VB.NET
------

Dim connString As String = "{your connection string here}"
Dim sql As String = "sprocName here"

Dim conn As New SqlConnection(connString)
Dim cmd As New SqlCommand(sql, conn)
cmd.CommandType = CommandType.StoredProcedure

Dim ds As New DataSet()
Dim da As New SqlDataAdapter()

da.TableMappings.Add("Table", "Customers")
da.TableMappings.Add("Table1", "Orders")
da.TableMappings.Add("Table2", "OrderDetails")

Try
conn.Open()
da.Fill(ds)
Finally
if(conn.State == ConnectionState.Open)
conn.Close();

conn.Dispose();
End Try

You should then be able to call the tables using the following

C#
DataTable customers = ds.Tables["Customers"];
DataTable orders = ds.Tables["Orders"];
DataTable orderDetails = ds.Tables["OrderDetails"];

VB.NET

Dim customers As DataTable = ds.Tables["Customers"];
Dim orders As DataTable = ds.Tables["Orders"];
Dim orderDetails As DataTable = ds.Tables["OrderDetails"];

If you want an easier sollution, consider creating a strongly typed dataset,
as it gives you dotted notation. Good luck!


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

Bill K said:
Cowboy - I am using the data adapter wizard and I am only seeing the first
table in the dataset - with the title of the stored procedure. is there a
way using the wizard - or in code to display all three table in the dataset?
if code - can you provide an example?

Thanks again.

Bill

Cowboy (Gregory A. Beamer) - MVP said:
Northwind stored procedure (can be tailored for your DB):

CREATE PROCEDURE dbo.GetCustomerOrders
(
@CustomerID nchar(10)
)
AS

SELECT c.CustomerID
, c.CompanyName
, c.ContactName
, c.ContactTitle
, c.Address
, c.City
, c.Region
, c.PostalCode
, c.Country
, c.Phone
, c.Fax
FROM Customers c
WHERE c.CustomerID = @CustomerID

SELECT o.OrderID
, o.CustomerID
, o.EmployeeID
, o.OrderDate
, o.RequiredDate
, o.ShippedDate
, o.ShipVia
, o.Freight
, o.ShipName
, o.ShipAddress
, o.ShipCity
, o.ShipRegion
, o.ShipPostalCode
, o.ShipCountry
FROM Orders o
WHERE o.CustomerID = @CustomerID

SELECT od.OrderID
, od.ProductID
, od.UnitPrice
, od.Quantity
, od.Discount
FROM [Order Details] od
JOIN Orders o
ON od.OrderID = o.OrderID
WHERE o.CustomerID = @CustomerID

In your dataset, add relationships for the tables to easily filter the
information.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

Bill K said:
I have a classic scenerio - simliar to the Northwind
Customer/Ordes/OrderDetails. I want to select a customer from a combo box,
display pertinent info, and display in a hierarchical datagrid the orders and
ordertails (in to next layer). I am creating 3 dataadapters, one for each
table. I am creating the customers as a parameter query which I pass the ID
value from the combo box to the query and it pulls up the custumer info
nicely.

Howerver, I can make the Orders dataadapter a parameter query using the
cutomer ID, but can't drill down to the Orders detail using that same ID.

I've put all three dataadapters into one dataset establishing the
relationships.

When I run it, I get an error saying that All the rows can't find related
rows. I know what is happening - it is pulling all the rows from subordinate
table and there is not related records in parent tables, thus violating the
referential integrity of the database.

It will work if I set constraints to false, but I can then not update
subordinate tables.

How can I make this classic senerio work - using winforms in vb.net????

It seems to me that there should be a way to force pulling the only the
orders and orderDetails associated with the customer.

Thanks for your help.
Click to expand...
Click to expand...
Click to expand...
 
Thanks. I am catching on - and it is working. You've been a big help.

Bill

Cowboy (Gregory A. Beamer) - MVP said:
This is a bit beyond wizard, but here is a quick sample in C# and VB.NET
(check my VB.NET code if you go that route, as I normally code in C# and this
is done without testing the code):

C#
---
string connString = "{your connection string here}";
string sql = "sprocName here";

SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.StoredProcedure;

DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();

da.TableMappings.Add("Table", "Customers");
da.TableMappings.Add("Table1", "Orders");
da.TableMappings.Add("Table2", "OrderDetails");

try
{
conn.Open();
da.Fill(ds);
}
finally
{
if(conn.State == ConnectionState.Open)
conn.Close();

conn.Dispose();
}


VB.NET
------

Dim connString As String = "{your connection string here}"
Dim sql As String = "sprocName here"

Dim conn As New SqlConnection(connString)
Dim cmd As New SqlCommand(sql, conn)
cmd.CommandType = CommandType.StoredProcedure

Dim ds As New DataSet()
Dim da As New SqlDataAdapter()

da.TableMappings.Add("Table", "Customers")
da.TableMappings.Add("Table1", "Orders")
da.TableMappings.Add("Table2", "OrderDetails")

Try
conn.Open()
da.Fill(ds)
Finally
if(conn.State == ConnectionState.Open)
conn.Close();

conn.Dispose();
End Try

You should then be able to call the tables using the following

C#
DataTable customers = ds.Tables["Customers"];
DataTable orders = ds.Tables["Orders"];
DataTable orderDetails = ds.Tables["OrderDetails"];

VB.NET

Dim customers As DataTable = ds.Tables["Customers"];
Dim orders As DataTable = ds.Tables["Orders"];
Dim orderDetails As DataTable = ds.Tables["OrderDetails"];

If you want an easier sollution, consider creating a strongly typed dataset,
as it gives you dotted notation. Good luck!


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

Bill K said:
Cowboy - I am using the data adapter wizard and I am only seeing the first
table in the dataset - with the title of the stored procedure. is there a
way using the wizard - or in code to display all three table in the dataset?
if code - can you provide an example?

Thanks again.

Bill

Cowboy (Gregory A. Beamer) - MVP said:
Northwind stored procedure (can be tailored for your DB):

CREATE PROCEDURE dbo.GetCustomerOrders
(
@CustomerID nchar(10)
)
AS

SELECT c.CustomerID
, c.CompanyName
, c.ContactName
, c.ContactTitle
, c.Address
, c.City
, c.Region
, c.PostalCode
, c.Country
, c.Phone
, c.Fax
FROM Customers c
WHERE c.CustomerID = @CustomerID

SELECT o.OrderID
, o.CustomerID
, o.EmployeeID
, o.OrderDate
, o.RequiredDate
, o.ShippedDate
, o.ShipVia
, o.Freight
, o.ShipName
, o.ShipAddress
, o.ShipCity
, o.ShipRegion
, o.ShipPostalCode
, o.ShipCountry
FROM Orders o
WHERE o.CustomerID = @CustomerID

SELECT od.OrderID
, od.ProductID
, od.UnitPrice
, od.Quantity
, od.Discount
FROM [Order Details] od
JOIN Orders o
ON od.OrderID = o.OrderID
WHERE o.CustomerID = @CustomerID

In your dataset, add relationships for the tables to easily filter the
information.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

:

I have a classic scenerio - simliar to the Northwind
Customer/Ordes/OrderDetails. I want to select a customer from a combo box,
display pertinent info, and display in a hierarchical datagrid the orders and
ordertails (in to next layer). I am creating 3 dataadapters, one for each
table. I am creating the customers as a parameter query which I pass the ID
value from the combo box to the query and it pulls up the custumer info
nicely.

Howerver, I can make the Orders dataadapter a parameter query using the
cutomer ID, but can't drill down to the Orders detail using that same ID.

I've put all three dataadapters into one dataset establishing the
relationships.

When I run it, I get an error saying that All the rows can't find related
rows. I know what is happening - it is pulling all the rows from subordinate
table and there is not related records in parent tables, thus violating the
referential integrity of the database.

It will work if I set constraints to false, but I can then not update
subordinate tables.

How can I make this classic senerio work - using winforms in vb.net????

It seems to me that there should be a way to force pulling the only the
orders and orderDetails associated with the customer.

Thanks for your help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top