Click here to Skip to main content
15,906,558 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello,

I'm having 1 dataset(ds,"agents"), having fields as: "Agent_Emp_Id","Agent_Id".["Agent_Id" as Primary Key]

Another dataset(ds1,"Emp"), having fields as: "Employee_Id","Emp_Name","Emp_Type".["Employee_Id" as Primary Key]

This "Employee_Id" is Primary key in 2nd table and works as foreign key(Agent_Emp_Id) in 1st table..

I want to join these two and need a dataset with structure like this "Agent_Id","Employee_Id","Emp_Name","Emp_Type"..


dataset.merge()..not working...
Posted
Updated 25-Mar-11 18:50pm
v2

1 solution

hi,
try this link

[^]

you can also achieve this by join query in sqlserver and return that result in DATASET

select tbl1.column1,tbl1.column2,tbl2.column1 from tbl1 inner join tbl2 on tbl1.column1=tbl2.column1
 
Share this answer
 
Comments
DEB4u 26-Mar-11 1:16am    
Ya i've gone through this...and wrote like this...
<pre>MySqlDataAdapter da = new MySqlDataAdapter();
connection = new MySqlConnection();
dbconeector = new DataConnector();
ds11 = new DataSet();
DataSet ds12 = new DataSet();
DataSet ds13 = new DataSet();

//load agent info
ds11.Clear();
connection = dbconeector.connect();
MySqlCommand cmd1 = new MySqlCommand();
cmd1 = connection.CreateCommand();
string qyery1 = "select agents_details.Agent_Employee_Id,agents_details.Agent_Id from agents_details where Agent_Employee_Id=any(select Emp_Id from employee_status where Emp_acc_Stat='OPEN') ";
cmd1.CommandText = qyery1;
da.SelectCommand = cmd1;
MySqlCommandBuilder cb1 = new MySqlCommandBuilder(da);
da.Fill(ds11, "agents_details");
//MessageBox.Show("filled agents id table to dataset");
qyery1 = "select Employee_Id,Emp_Name,Emp_Mname,Emp_Lname from employee_details where Employee_Id=any(select agents_details.Agent_Employee_Id from agents_details where Agent_Employee_Id=any(select Emp_Id from employee_status where Emp_acc_Stat='OPEN') ) ";
cmd1.CommandText = qyery1;
da.SelectCommand = cmd1;
MySqlCommandBuilder cb2 = new MySqlCommandBuilder(da);
da.Fill(ds12, "agents_name");
//MessageBox.Show("filled agents name table to dataset");
dbconeector.close();
dataGridView1.DataSource = ds11.Tables[0];
dataGridView2.DataSource = ds12.Tables[0];


DataTable dt1 = new DataTable("table1");
DataTable dt2 = new DataTable("table2");
dt1 = ds11.Tables["agents_details"];
dt1.PrimaryKey = new DataColumn[] { dt1.Columns["Agent_Id"] };
dt2 = ds12.Tables["agents_name"];
dt2.PrimaryKey = new DataColumn[] { dt2.Columns["Employee_Id"] };
ds13.Tables.Add(dt1);
ds13.Tables.Add(dt2);
DataRelation drel = new DataRelation("EquiJoin", dt2.Columns["Employee_Id"], dt1.Columns["Agent_Employee_Id"]);
ds11.Relations.Add(drel);
DataTable jt = new DataTable("joinedtable");
jt.Columns.Add("Agent_Id", typeof(Int32));
jt.Columns.Add("Agent_Emp_Id", typeof(Int32));
jt.Columns.Add("Emp_Name", typeof(string));
jt.Columns.Add("Emp_Mname", typeof(string));
jt.Columns.Add("Emp_Lname", typeof(string));
ds13.Tables.Add(jt);
foreach (DataRow dr in ds11.Tables["Table1"].Rows)
{
DataRow parent = dr.GetParentRow("EquiJoin");
DataRow current = jt.NewRow();
for (int h = 0; h < ds.Tables["Table1"].Columns.Count; h++)
{
current[h] = dr[h];
jt.Rows.Add(current);
}
}
dataGridView3.DataSource = ds13.Tables["joinedtable"];
}</pre>

it shows error like this...

ds13.Tables.Add(dt1);"Datatable already belongs to another dataset
DEB4u 26-Mar-11 1:24am    
Actually facing prob in join...Here s 1 query : "select agents_details.Agent_Employee_Id,agents_details.Agent_Id from agents_details where Agent_Employee_Id=any(select Emp_Id from employee_status where Emp_acc_Stat='OPEN') "

and another is: "select Employee_Id,Emp_Name,Emp_Mname,Emp_Lname from employee_details where Employee_Id=any(select agents_details.Agent_Employee_Id from agents_details where Agent_Employee_Id=any(select Emp_Id from employee_status where Emp_acc_Stat='OPEN') ) "

need Agent_Id,Emp_Name in 1 table

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900