GridView - Join 2 DataTables RSS

• • Reply
  

  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!

• • Reply
  

  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,

  Please mark the replies as answers if they help or unmark if not.
  If you have any feedback about my replies, please contactmsdnmg@microsoft.com.
  Microsoft One Code Framework
• • Reply
  

  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)

  Dim dt As DataTable = New DataTable()
  dbcomm.Fill(dt)

  Employment.DataSource = dataTable 

  Employment.DataBind()

  that's it ,,, that what was iam telling you in  my previous post 

  
  

  Please "Mark as Answer" if this post helps.Thank You :)
• • Reply
  

  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

  Kindly mark this post as "Answer", if it helped you.
  
  (Talk less..Work more)
• • Reply
  

  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

• • Reply
  

  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

  <asp:GridView ID="GridView2" runat="server"></asp:GridView>

   

  Kindly mark this post as "Answer", if it helped you.
  
  (Talk less..Work more)
  • Edited by basheerkal on May 01, 2012 03:30 PM
  • Marked as answer by Qin Dian Tang - MSFT on May 02, 2012 01:47 AM