GridView - Join 2 DataTables

Hi, I have a two grid views from two different SQL statements which I want to try join together.

Both have the same 1st column, the persons name, but the second column in both DataTables are differente

so DataTable 1 looks like...

Name, Value
John Smith, 1
Jane Doe, 2
Tom Jones, 3

DataTable 2 looks like...

Name, Value
John Smith, A
Jane Doe, B
Tom Jones, C

Is there anyway I could make the DataTable like this to make a one GridView of the two...

Name, Value
John Smith, A, 1
Jane Doe, B, 2
Tom Jones, C, 3

Thanks very much

Re: GridView - Join 2 DataTables

ofcours you can just use this query and bind it to yor gridview

select a.Name , b.Value ,a.value from Table1 as a inner join Table2 as b on a.Name = b.Name

Re: GridView - Join 2 DataTables

Thanks for your reply,

i'm sorry im a bit of a noob at this part of asp. would you be able to help with more explanation or a simple example

thank you

Re: GridView - Join 2 DataTables

danieldunn10

Thanks for your reply,

i'm sorry im a bit of a noob at this part of asp. would you be able to help with more explanation or a simple example

thank you

Hope this helps:

var querable = (from ds1 in DataSource1
                               join ds2 in DataSource2
                               on ds1.Name equals ds2.Name
                                   select new
                                              {
                                                  ds1.Name,
                                                  ds1.OtherColumn1,
                                                  ds1.OtherColumn12
                                              }).SingleOrDefault();
            GridView1.DataSource = querable;
            GridView1.DataBind();

Re: GridView - Join 2 DataTables

Thanks 2pac, i think that is what I am after, I'm just a little unsure what to use instead of ds1 / ds2 and datasource1 / datasource2. Below my two sql codes I have

dbcomm = New OdbcCommand(sql, MyConnection)

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

and

            dbcomm2 = New OdbcCommand(sql2, MyConnection)

            Dim dt2 As DataTable = New DataTable()
            dbcomm2.Fill(dt2)

What should mine look like?

My columns are FeeEarnerName, Value1 (in table 1), Value2 (in table 2)

Thanks very much, sorry for the simple questions!

Re: GridView - Join 2 DataTables

You could try just one SQL statement instead. Something like:

SELECT t1.FeeEarnerName, t1.Value1, t2.Value2
FROM table1 t1
INNER JOIN table2 t2 ON t1.FeeEarnerName = t2.FeeEarnerName

And then just use that one datatable for your single gridview.

Re: GridView - Join 2 DataTables

Thanks ksqcoder, can i do a SQL statement on my two datatables?

Re: GridView - Join 2 DataTables

You could use Linq on the two datatables like the example from 2pac. The code I suggested would be for another OdbcCommand. If you just wanted the one GridView instead of the two I would just go that route and use the one OdbcCommand and one GridView. If you still needed the two GridViews along with a new merged GridView then you could use Linq. A Linq query might look like:

var results = from t in dt1.AsEnumerable()
join s in dt2.AsEnumerable() on t.FeeEarnerName equals s.FeeEarnerName
select t.FeeEarnerName, t.Value1, s.Value2;

GridViewBoth.DataSource = results;

Or just make three database calls, two for your original GridViews and a third returning the joined data from both tables.

Re: GridView - Join 2 DataTables

hey ,, you can have only one gridview with one sql statment just write the sql statment instead the others and do the binding to the gridview

so you will got a one gridview with the values you want ,, no need for two

Re: GridView - Join 2 DataTables

Thanks for that, I am using this code below

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)


            Dim results = From t In dt.AsEnumerable()
              Join s In dt2.AsEnumerable() On t.FeeEarner Equals s.FeeEarner
              Select t.FeeEarner, t.CurrentValue, s.CurrentValue2

            Employment.DataSource = results
            Employment.DataBind()

I cant understand why its not working though! In my SQL statement I have a column called FeeEarner.

But it underlines t.FeeEarne, s.FeeEarner, t.CurrentValue, s.CurrentValue2 and says...

'FeeEarner' is not a member of system.data.datarow

THanks for any advice

Re: GridView - Join 2 DataTables

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!

Re: GridView - Join 2 DataTables

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,

Re: GridView - Join 2 DataTables

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

Re: GridView - Join 2 DataTables

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

Re: GridView - Join 2 DataTables

Thanks basheerkal, that sounds like what I am looking for!

How would the code look for that?

THanks very much

Re: GridView - Join 2 DataTables

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>