Combine two datatables in one datatable using select query

Question

Hello Can anybody help me please how to combine two datatables in one resulting datatable in the same dataset using select statement? I made three select commands the first two for filling the first two tables and the third which combines them for filling a third datatable. These works fine when run in access. But I don't know how to use the third combining select command to fill the third datatable in the dataset. Then I want to use this third datatable for populating my Crystal Report. If somebody has an idea I would appreciate your help a lot. Thanks in advance.

Herry Ng · Answer

as far as I know  dataset does not support SELECT-SQL you could use LINQ but why not initially SELECT from the datasource ex: Select * from table1 --> Fill datatable1 in dataset Select * from table2 --> Fill datatable2 in dataset Select * from table1 table2 where table1.key=table2key --> Fill datatable3 in dataset Best Regards

Nel · Answer

Yes I want to use SQL select. In fact I have these select queries which work in access. 1.Select query for the first datatable: SELECT NALOG1.GBRV NALOG1.DATA Min(IIf([NALOG1.GBRV]=[@gbr1] And ((NOVI.AB)=[@ab1] Or (NOVI.AB)=[@ab2]) And [NALOG1].[POCKM]>0[NALOG1.POCKM]Null)) AS pockm Max(IIf([NALOG1.GBRV]=[@gbr1] And ((NOVI.AB)=[@ab1] Or (NOVI.AB)=[@ab2]) And ([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA] FROM NALOG1 INNER JOIN NOVI ON NALOG1.GBRV = NOVI.GBR GROUP BY NALOG1.GBRV NALOG1.DATA HAVING (((NALOG1.GBRV)=[@gbr1]) AND ((NALOG1.DATA)>=[@data1] And (NALOG1.DATA) 2. Select query for the second datatable: SELECT MAGACIN.GBR MAGACIN.DATA NOVI.AB Sum(IIf([sifra]='0992201'[kol]Null)) AS Addblue Sum(IIf([sifra]='0999001'[kol]Null)) AS Antifriz Sum(IIf([sifra]='0991000'[kol]Null)) AS Gorivo Sum(IIf([sifra]='0993050'[kol]Null)) AS Motmaslo FROM MAGACIN INNER JOIN NOVI ON MAGACIN.GBR = NOVI.GBR GROUP BY MAGACIN.GBR MAGACIN.DATA NOVI.AB HAVING (((MAGACIN.GBR)=[@gbr1]) AND ((NOVI.AB)=[@ab1] or (NOVI.AB)=[@ab2]) AND ((MAGACIN.DATA)>=[data1] And (MAGACIN.DATA) ORDER BY MAGACIN.DATA; 3. and the select query for the combination of theboth datatables: SELECT NalogNov.DATA MagNov.GBR Max(IIf([MagNov].[DATA]=[NalogNov].[DATA][MagNov].[Gorivo]0)) AS Gorivo1 Max(IIf([MagNov].[DATA]=[NalogNov].[DATA][MagNov].[Addblue]0)) AS Addblue1 Max(IIf([MagNov].[DATA]=[NalogNov].[DATA][MagNov].[Antifriz]0)) AS Antifriz1 Max(IIf([MagNov].[DATA]=[NalogNov].[DATA][MagNov].[Motmaslo]0)) AS Motmaslo1 NalogNov.pockm NalogNov.krajkm [krajkm]-[pockm] AS RAZLIKA Max(NalogNov.Poslprov) AS MaxOfPoslprov Max(NalogNov.Poslserv) AS MaxOfPoslserv Max(NalogNov.KMS) AS MaxOfKMS Max(NalogNov.KMP) AS MaxOfKMP FROM NalogNov LEFT JOIN MagNov ON NalogNov.GBRV = MagNov.GBR WHERE (((MagNov.GBR)=[NalogNov].[GBRV])) GROUP BY NalogNov.DATA MagNov.GBR NalogNov.pockm NalogNov.krajkm [krajkm]-[pockm] ORDER BY NalogNov.DATA; So I fill the first two datatables by using oleDBDataAdapter1.SelectCommand = command1; oleDBDataAdapter1.Fill(dataSet2 NalogNov) and respectively for the other datatable. I don't know how to fill the third datatable.

Muralidharan Deenathayalan · Answer

Do you want to use datatable.select or sql select statement?

Nel · Answer

Thanks but the columns of bothdata tables are not the same. How to use the select query which works fine when run in access to fill the third datatable?

Muralidharan Deenathayalan · Answer

Please try the following. 1) You can use datatable.merge to merge two datatable into one. DataTable dt1 = new DataTable(); DataTable dt2 = new DataTable(); DataTable dt3 - dt1.Copy(); dt3.Merge(dt2); http://msdn.microsoft.com/en-us/library/system.data.datatable.merge.aspx 2) You can import the data rows from different tables and load into one. http://msdn.microsoft.com/en-us/library/system.data.datatable.loaddatarow(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.data.datatable.importrow(v=vs.100).aspx