I've tried using the merge() method instead which nearly achieves what I want. I've used...
dbcomm = New OdbcDataAdapter(sql, MyConnection)
Dim dt As DataTable = New DataTable()
dbcomm.Fill(dt)
dbcomm = New OdbcDataAdapter(sql2, MyConnection)
Dim dt2 As DataTable = New DataTable()
dbcomm.Fill(dt2)
dt.Merge(dt2, False, MissingSchemaAction.Add)
Employment.DataSource = dt
Employment.DataBind()
The results are like this though
Parson1, 1, null
Person2, 2, null
Person3, 3, null
Person1, null, A
Person2, null, B
Person3, null, C
Why not execute the above inner sql query to return a new datatable with two tables' data and bind to GridView. It is the same to you bind one DataTable to one GridView. Just change a select sql statement.
If it is from 2 databases using 2 SqlConnections and sqlCommands Fill 2 dataTables. Picking field values from these 2 datatables fill a third Datatatable. Set this as the datasource and Databind the GridView
Kindly mark this post as "Answer", if it helped you.
danieldunn10
Member
389 Points
383 Posts
Re: GridView - Join 2 DataTables
Apr 25, 2012 06:29 AM|LINK
I've tried using the merge() method instead which nearly achieves what I want. I've used...
dbcomm = New OdbcDataAdapter(sql, MyConnection) Dim dt As DataTable = New DataTable() dbcomm.Fill(dt) dbcomm = New OdbcDataAdapter(sql2, MyConnection) Dim dt2 As DataTable = New DataTable() dbcomm.Fill(dt2) dt.Merge(dt2, False, MissingSchemaAction.Add) Employment.DataSource = dt Employment.DataBind()The results are like this though
Parson1, 1, null
Person2, 2, null
Person3, 3, null
Person1, null, A
Person2, null, B
Person3, null, C
Is there anyway to combine them?
Thanks!
Qin Dian Tan...
All-Star
113532 Points
12480 Posts
Microsoft
Re: GridView - Join 2 DataTables
Apr 25, 2012 08:00 AM|LINK
Hi,
Why not execute the above inner sql query to return a new datatable with two tables' data and bind to GridView. It is the same to you bind one DataTable to one GridView. Just change a select sql statement.
Thanks,
If you have any feedback about my replies, please contactmsdnmg@microsoft.com.
Microsoft One Code Framework
AmalO.Abdull...
Contributor
3116 Points
764 Posts
Re: GridView - Join 2 DataTables
Apr 25, 2012 08:21 AM|LINK
why are you doing this you can do it in another way
instead of using to sql just use 1 sql statement and dorictly bind it to you gridview
as following
your sql will be
"select t.FeeEarner , t.CurrentValue , s.CurrentValue2 from table1 as t inner join table2 as s on t.FeeEarner = s.FeeEarner"
then
dbcomm = New OdbcDataAdapter(sql, MyConnection)
basheerkal
Star
10672 Points
2426 Posts
Re: GridView - Join 2 DataTables
Apr 25, 2012 02:54 PM|LINK
If it is from 2 databases using 2 SqlConnections and sqlCommands Fill 2 dataTables. Picking field values from these 2 datatables fill a third Datatatable. Set this as the datasource and Databind the GridView
(Talk less..Work more)
danieldunn10
Member
389 Points
383 Posts
Re: GridView - Join 2 DataTables
Apr 28, 2012 08:20 AM|LINK
Thanks basheerkal, that sounds like what I am looking for!
How would the code look for that?
THanks very much
basheerkal
Star
10672 Points
2426 Posts
Re: GridView - Join 2 DataTables
May 01, 2012 03:28 PM|LINK
Try like this..this is a working example. May be some other methods are there to do this ... this is only one of them
void GvFill() { string constr = ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString; string qry1 = "SELECT * FROM table_1"; string qry2 = "SELECT * FROM table_2"; SqlConnection conn = new SqlConnection(constr); conn.Open(); SqlCommand cmd1 = new SqlCommand(qry1, conn); SqlCommand cmd2 = new SqlCommand(qry2, conn); SqlDataAdapter da1 = new SqlDataAdapter(cmd1); SqlDataAdapter da2 = new SqlDataAdapter(cmd2); DataTable dt1 = new DataTable(); DataTable dt2 = new DataTable(); da1.Fill(dt1); da2.Fill(dt2); int Counter = 0; dt2.Columns.Add("Value1"); foreach (DataRow tr in dt2.Rows) { dt2.Rows[Counter][2] = dt1.Rows[Counter][1].ToString(); Counter=Counter+1; } GridView2.DataSource = dt2; GridView2.DataBind(); conn.Close(); } protected void Button1_Click(object sender, EventArgs e) { GvFill(); }This the gridView in my form
(Talk less..Work more)