How to display fields from multiple datatable in a datagrid

[programatix]

Hi,

I have a problem and would be happy if you guys out there can help me out with this.

Let say I have 2 tables. One of them is "Customer" and another is "State".

The "Customer" table has a field which is linked to the "State" table using relationship.

Usually in VB6, I would open a recordset using the "JOIN" statement and bind it to a datagrid.

In VB .NET, if I don't want to use the "JOIN" statement but to open the 2 tables in a dataset, how do I display the data correctly in a datagrid?

To make the picture clear, so here goes,

*** Data in the 2 tables ***

Customer Table
--------------------
CustomerID: 1
Name: Peter
StateID: 1

CustomerID: 2
Name: John
StateID: 2


State Table
--------------
StateID: 1
Name: Virginia

StateID: 2
Name: Ohio


*** The result I wanted to be displayed in the datagrid ***

+----------+----------+
| Name     | State    |
+----------+----------+
| Peter    | Virginia |
| John     | Ohio     |
+----------+----------+

Thanks in advance

[Scott Penner]

Use an SQL query:
SELECT *
FROM Customer, State
WHERE Customer.StateID = State.StateID

Then, use the result from this query to fill your datagrid.
I think there should be a query builder in the IDE, if you want to fool around with the query a little bit. In principle, you could save this query and link it as the data source to the DBGrid at design time.

[programatix]

By doing this, the record is not updatable. If I want to use dataset, is there any way to do so?

Thanks.

[blackcat]

You need a Data Relation object.

You can build one in code (I think there is a sample in the on line help) or you can do it visually thus:

Create the dataset so that it contains both tables

Double click on the dataset's .xsd file in Solution Explorer and you will get a view of both tables on screen.

From the toolbox drag a relation object onto the window containing the tables and drop it on the child table.

Follow you nose from there on, it should be straightforward.

Cheers

[programatix]

Ok, we're going somewhere. Creating the Data Relation object is not a problem. I did actually tried it out before but.... I still can't map data from different tables to the same row of the datagrid.


Thanks.

[programatix]

I manage to solve the problem. But please, anyone out there confirm if my solution is valid.

Set the SelectCommand for the SqlDataAdapter to,

SELECT Customer.CustomerID, Customer.Name, Customer.StateID, State.Name AS State
FROM Customer LEFT OUTER JOIN State ON Customer.StateID= State.StateID

Set the UpdateCommand for the SqlDataAdapter to,

 
UPDATE Customer
	SET Name = @Name, StateID = @StateID
WHERE
	(CustomerID = @Original_CustomerID);
SELECT Customer.CustomerID, Customer.Name, Customer.StateID, State.State AS State
FROM Customer LEFT OUTER JOIN State ON Customer.StateID= State.StateID
WHERE (CustomerID = @CustomerID)

How do I get all those SQL statements above? Well, this is what I did.
1) Create 2 SqlDataAdapters named SqlDataAdapter1 and SqlDataAdapter2.
2) Configure SqlDataAdapter1 using its wizard to select the whole Customer table.
3) Configure SqlDataAdapter2 using its wizard to select the 2 tables.
4) Copy the SelectCommand from SqlDataAdapter2
5) Paste it into SqlDataAdapter1's SelectCommand
6) Edit SqlDataAdapter1's UpdateCommand and replace the SELECT statement with the one from step (4)
7) Repeat step (6) for SqlDataAdapter1's InsertCommand
8) Delete SqlDataAdapter2
9) Generate dataset.

By doing so, I get what I wanted and the dataset can be updated.

[programatix]

I've been thinking... if I use the code above... then what is the purpose of dataset?

I thought by using dataset, we don't need to use SQL statement "JOIN" anymore...

Anyone out there have any idea?

Thanks.

[programatix]

I finally solved my problem. I found a user control in the forum here "DropDownColumn.zip". I actually forgot which thread it is and from where I downloaded the file.

Anyway, after modifying several lines, I am able to get what I want without using the JOIN statement in the SQL statement.