Urgent~ How to merge 2 datasets in 1 DataGrid?

[JenHu]

Hi experts,

I am not sure if I should use Merge on this case, but I want to add a
Vendor Name column in the datagrid1 where the Vendor Name has to get
from another server (not linked and can't be linked).
The first table contains Job tracking information, which has a
"J_Vendor_ID" column, and no Vendor Name in this server.
The 2nd table on the other server contains VendName and VendorID.
I want to have a Vendor Name column (VendName) in datagrid, and
VendName column has to get from the 2nd table, the VendName should
list only from table1's the VendorID.
The J_vendor_ID from table1 and VendorID from table 2 will be the key
columns for the 2 datasets merging.

I received error when it's doing the dataSet.Merge(dataSet2):

System.Data.DataException: Mismatch columns in the PrimaryKey :
.J_Vendor_ID versus .VendorId. at System.Data.DataSet.

Can someone tell me how should I merge these 2 datasets? Thanks.

------------------------------------------------------------------
Sub BindTheData()
Dim strSql As New SqlCommand
Dim dtrDataGrid As SqlDataReader
Dim dataAdapter As New SqlDataAdapter
Dim dataSet As New DataSet
Dim dataSet1 As New DataSet
Dim dataSet2 As New DataSet
Dim strSql2 As New SqlCommand
Dim dataAdapter2 As New SqlDataAdapter

conHDDb.Open()
conGPDb.Open()

strSql.CommandText = "SELECT Job_Tracking_Table.Job_ID,
Job_Tracking_Table.J_Open_Date, Job_Tracking_Table.J_Due_Date,
Job_Tracking_Table.J_Location, Job_Tracking_Table.J_DM,
Job_Tracking_Table.J_Priority,Job_Tracking_Table.J_Vendor_ID,Job_Tracking_Table.J_Called,
Job_Tracking_Table.J_Status_ID,Job_Tracking_Table.J_M_Category_ID,Major_Category_Table.M_Category_Name,
Sub_Category_Table.Sub_Category_Name FROM Job_Tracking_Table INNER
JOIN Sub_Category_Table ON Job_Tracking_Table.J_Sub_Category_ID =
Sub_Category_Table.Sub_Category_ID INNER JOIN Major_Category_Table
ON Job_Tracking_Table.J_M_Category_ID =
Major_Category_Table.M_Category_ID WHERE
(Job_Tracking_Table.J_Open_Date > GETDATE() - 7) and
Job_Tracking_Table.J_Status_ID=1 order by Job_ID"
strSql2.CommandText = "SELECT VendorId, Vendname FROM
PM00200"
strSql.Connection = conHDDb
strSql2.Connection = conGPDb
strSql.CommandType = CommandType.Text
strSql2.CommandType = CommandType.Text
dataAdapter.SelectCommand = strSql
dataAdapter2.SelectCommand = strSql2
dataAdapter.Fill(dataSet, "Job_Tracking_Table")
dataAdapter2.Fill(dataSet2, "Job_Tracking_Table")

Dim pk1(0) As DataColumn
Dim pk2(0) As DataColumn
pk1(0) = dataSet.Tables(0).Columns("J_Vendor_ID")
dataSet.Tables(0).PrimaryKey = pk1
pk2(0) = dataSet2.Tables(0).Columns("VendorId")
dataSet2.Tables(0).PrimaryKey = pk2

dataSet.Merge(dataSet2)

Dim JobInfo As DataTable = dataSet.Tables("Job_Tracking_Table")
Dim dvJobInfo As New DataView(dataSet.Tables("Job_Tracking_Table"))
Session("JobSession") = dvJobInfo
dvJobInfo.Sort = strViewString
DataGrid1.DataSource = dvJobInfo
DataGrid1.DataBind()

conHDDb.Close()
conGPDB.Close()
End Try
End Sub
-----------------------------------------------------------------------

*-----------------------*
Posted at:
www.GroupSrv.com
*-----------------------*

 

[Grzegorz Danowski]

Hi experts,

I am not sure if I should use Merge on this case, but I want to add a
Vendor Name column in the datagrid1 where the Vendor Name has to get
from another server (not linked and can't be linked).
The first table contains Job tracking information, which has a
"J_Vendor_ID" column, and no Vendor Name in this server.
The 2nd table on the other server contains VendName and VendorID.
I want to have a Vendor Name column (VendName) in datagrid, and
VendName column has to get from the 2nd table, the VendName should
list only from table1's the VendorID.
The J_vendor_ID from table1 and VendorID from table 2 will be the key
columns for the 2 datasets merging.

In my opinion, Instead of using merge you should use expression column, for
example:

1. add to DataSet both tables ("Job tracking information|" and "VendName and
VendorId"),
2. add relation between those tables:

ds.Relations.Add("VendorId_VendorName", _
ds.Tables("PM00200").Column("VendorId"), _
ds.Tables("Job_Tracking_Table").Columns("J_Vendor_ID"),
_
False)

3. add expression column to Job_Tracking_Table:

ds.Tables("Job_Tracking_Table").Columns.Add("VendorName", GetType(String), _
"Parent(VendorId_VendorName).Vendname")

Regards,
Grzegorz