-----------------------------------------------------------
New Message on BDOTNET
-----------------------------------------------------------
From: Ritesh_Kesharwani
Message 2 in Discussion
Hi
Directly you can't do that but incase of diff dataset then Yes You Can ...
Typically one dataGrid is bound with one DataTable. However, consider the
case of two tables both having a common key column called ID. Say one table
contains personal information about employees and another table contains
some official information. EmployeeID is the common in both the tables and
both tables have same number of rows (equal to the no of employees). How to
display such tables in one DataGrid? Read on to know more.
SQL Server Databases
In order to work with this example you need to have following tables in SQL
server database.
REATE TABLE [dbo].[Table1] (
[EmployeeID] [char] (10)
COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Name] [char] (10)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Table2] (
[EmployeeID] [char] (10)
COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Department] [char] (10)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
Note, that the two tables can be linked via EmployeeID column. This is very
simplified view of the tables to keep the example simple.
Filling DataSets
Next we will fill two separate DataSets from these two tables. If you wish
you can also populate them in the same DataSet.
Dim connstr As String = "Integrated Security=SSPI;
User ID=sa;Initial Catalog=Northwind;Data Source=SERVER\netsdk"
Dim cnn As New SqlConnection(connstr)
Dim da1 As New SqlDataAdapter("select * from table1", cnn)
Dim da2 As New SqlDataAdapter("select * from table2", cnn)
Dim ds1 As New DataSet()
Dim ds2 As New DataSet()
da1.Fill(ds1, "table1")
da2.Fill(ds2, "table1")
This is a typical code that you must have used many times. Note that in both
the DataSets the DataTable name is same.
Setting Primary Keys
Now, we will set the primary keys for both of the tables. Why is it
necessary? When we merge results of these two DataSets (discussed in next
section) primary key plays important role.
Dim pk1(0) As DataColumn
Dim pk2(0) As DataColumn
pk1(0) = ds1.Tables(0).Columns("EmployeeID")
ds1.Tables(0).PrimaryKey = pk1
pk2(0) = ds2.Tables(0).Columns("EmployeeID")
ds2.Tables(0).PrimaryKey = pk2
Merging DataSets
In this step, we will merge the two DataSets by calling the Merge method of
the DataSet class.
ds1.Merge(ds2, False, MissingSchemaAction.Add)
In the above method we are merging data from ds2 with ds1. While merging the
DataSets they will be checked for schema information. Depending on the
MissingSchemaAction set by us we can add the missing columns, raise an error
or ignore the error. The second argument - True/False - indicates whether
changes made to ds1 to be preserved while merging operation or not.
Previously I mentioned that setting primary key is important. This is
because if we do net set it the rows from ds2 will be appended to ds1. After
we set the primary key they we be really merged based on the primary key.
Binding to DataGrid
Finally we will bind our DataGrid with the above merged dataset.
DataGrid1.DataSource=ds1
DataGrid1.DataBind()
Regard's
Ritesh Kesharwani
SQL Star Intlernational Ltd.
[EMAIL PROTECTED]
[EMAIL PROTECTED]
www.Riteshk.blogspot.com
Cell - 9849976150
-----------------------------------------------------------
To stop getting this e-mail, or change how often it arrives, go to your E-mail
Settings.
http://groups.msn.com/bdotnet/_emailsettings.msnw
Need help? If you've forgotten your password, please go to Passport Member Services.
http://groups.msn.com/_passportredir.msnw?ppmprop=help
For other questions or feedback, go to our Contact Us page.
http://groups.msn.com/contact
If you do not want to receive future e-mail from this MSN group, or if you received
this message by mistake, please click the "Remove" link below. On the pre-addressed
e-mail message that opens, simply click "Send". Your e-mail address will be deleted
from this group's mailing list.
mailto:[EMAIL PROTECTED]
