Merge dataTable ?

[yvesadver]

Hi Have 2 datatable and i would like to merge these in one table. The key column is a date. is it possible?

Table 1
res1/year/month
1/2008/06
2/2008/07

Table 2
res2/year/month
23/2008/05
45/2008/06
46/2008/08

result Table 3

res1/res2/year/month
0/23/2008/05
1/45/2008/06
2/0/2008/07
0/46/2008/08

Thanks.

[yasirmturk]

Try this

Make a join query for both tables and then fill your dataset using that query it will be perfect

 string grdqry = "SELECT EMPCARD.CARDID, EMPCARD.CARDNO, EMPCARD.ISSUEDATE, EMPCARD.EXPIRYDATE, EMPCARD.STATUS, EMPDATA.EMPID FROM   EMPCARD INNER JOIN  EMPDATA ON EMPCARD.EMPID = EMPDATA.EMPID ";

            if (!LoadGrid(ref dgvECard, grdqry + " where EMPDATA.DEPTID=" + cmbDept.SelectedValue.ToString()))
            { lbltxt.Text = "Error loading grid."; }

        public bool LoadGrid(ref DataGridView dgv, string query)
        {
            LastError = "";
            dgv.DataSource = null;
            DataSet ds = new DataSet();

            SqlConnection mycon = new SqlConnection(ConStr);
            SqlDataAdapter myda = new SqlDataAdapter(query, mycon);

            try
            {
                mycon.Open();
                myda.Fill(ds);
                if (ds.Tables.Count > 0)
                {
                    //ddl.DisplayMember = ds.Tables[0].Columns[1].ColumnName;// "DEVID";
                    //ddl.ValueMember = ds.Tables[0].Columns[0].ColumnName;
                    dgv.DataSource = ds.Tables[0];

                    return true;
                }
                else return false;
            }
            catch (Exception ex)
            { LastError = ex.Message; return false; }
        }

[yvesadver]

Thanks but it's not a solution for my problem. I don't have a possible relation between the table.
I need this to show statistic.

I have for the moment 2 procedures:

select
Count(Id) as res1,
YEAR(Vacancies. Date) as Year,
MONTH(Vacancies .Date) as Month
from
Vacancies
GROUP BY YEAR(Vacancies. Date), MONTH(Vacancies .Date)
ORDER BY YEAR(Vacancies. Date), MONTH(Vacancies .Date)



select
count(VacancySi mple.Id) as res2,
YEAR(VacancySim ple.Date) as Jaar,
Month(VacancySi mple.Date) as maand
from VacancySimple
Group By YEAR(VacancySim ple.Date), Month(VacancySi mple.Date)
Order By YEAR(VacancySim ple.Date), Month (VacancySimple. Date)


the result is :
res1 Year Month
2 2008 5
1 2008 7
2 2008 8

and

res2 Year Month
50 2008 5
17 2008 6
16 2008 7

this results are stored in 2 datatable.

I want to merge this datatables to only one.

The result would be:
res1 res2 year month
2 50 2008 5
0 17 2008 6
1 16 2008 7
2 0 2008 8

Do you have maybe a sql solution for this or a .net c# solution?

Thanks.

[joedeene]

wouldnt the datatable.merge method work?

http://msdn.microsoft. com/en-us/library/fk68ew7b.aspx

[yvesadver]

Hi,

joedeene , thanks .
It what i want to use but the resultat is strange.

The merge result is not correct.

my code :

DataTable tableVacancy = Data.convertDat aReaderToDataTa ble(vacancyDar, "vacancy");
DataColumn colReactie = new DataColumn("rea ctions", typeof(System.I nt32));
tableVacancy.Co lumns.Add(colRe actie);
tableVacancy.Pr imaryKey = new DataColumn[] { tableVacancy.Co lumns[2] };
DataTable tableVacancieSi mple = Data.convertDat aReaderToDataTa ble(vacancySimp leDar, "vacancieSimple ");
DataColumn colVacatures = new DataColumn("vac atures", typeof(System.I nt32));
tableVacancieSi mple.Columns.Ad d(colVacatures) ;
tableVacancieSi mple.PrimaryKey = new DataColumn[] {tableVacancieS imple.Columns[2]};


tableVacancy.Me rge(tableVacanc ieSimple,false, MissingSchemaAc tion.Add);


the resultat is :

- 2008 5 50
- 2008 7 16
2 2008 8 -
- 2008 6 17

And i need this:

2 2008 5 50
1 2008 7 16
2 2008 8 0
0 2008 6 17

very strange.

Any idee?

Thanks.