I use access database and I have these queries, i.e. command strings:
comstring1="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, Sum(IIf([sifra]='0992201',[kol],Null))/Sum(IIf([sifra]='0991000',[kol],Null))*100 AS Addbluegorivo 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)<=[data2])) ORDER BY MAGACIN.DATA";
| GBR | DATA | AB | Addblue | Antifriz | Gorivo | Motaslo | Addbluegor |
| 1001 | 10.10.2011 | GP | 115 | ||||
| 1001 | 11.10.2011 | GP | 40 | ||||
| 1001 | 12.10.2011 | GP | 12 | 110 | 10.909090 | ||
| 100 | 13.10.2011 | GP | 12 | 125 | 9.6 |
comstring2 = "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]<=[@data2]),[NALOG1.KRAJKM],NULL)) AS krajkm, (Max(IIf([NALOG1.GBRV]= [@gbr1] And ((NOVI.AB)=[@ab1] or (NOVI.AB)=[@ab2]) and ([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]) ,[NALOG1.KRAJKM],NULL))-(Min(IIf([NALOG1.GBRV] = [@gbr1] And ((NOVI.AB)=[@ab1] or (NOVI.AB)=[@ab2]) And[NALOG1].[POCKM]>0,[NALOG1.POCKM],Null))) ) AS Razlika, Max(NOVI.DATAP) AS Poslprov, Max(NOVI.DATAS) AS Poslserv, Sum(NALOG1.km) AS sumkm, Max(NOVI.KMP1) AS KMP, Max(NOVI.KMS1) AS KMS 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)<=[@data2]) )";
| GBRV | DATA | pockm | krajkm | Razlika | Poslprov | Poslserv | sumkm | kmp | kms |
| 1001 | 10.10.2011 | 43580 | 43867 | 287 | 27.09.2011 | 287 | 41400 | 41400 | |
| 1001 | 11.10.2011 | 43867 | 43967 | 100 | 27.09.2011 | 100 | 41400 | 41400 | |
| 1001 | 12.10.2011 | 43967 | 44254 | 287 | 27.09.2011 | 287 | 41400 | 41400 | |
| 1001 | 13.10.2011 | 44254 | 44541 | 287 | 27.09.2011 | 287 | 41400 | 41400 |
I want to fill two tables in the dataset with them and get a result (joint tables, I can't join them now, the second should be after the Addbluegor column, so I want one table)
| GBR | DATA | AB | Addblue | Antifriz | Gorivo | Motaslo | Addbluegor |
| 1001 | 10.10.2011 | GP | 115 | ||||
| 1001 | 11.10.2011 | GP | 40 | ||||
| 1001 | 12.10.2011 | GP | 12 | 110 | 10.909090 | ||
| 100 | 13.10.2011 | GP | 12 | 125 | 9.6 |
| pockm | krajkm | Razlika | Poslprov | Poslserv | sumkm | kmp | kms | ||
| 43580 | 43867 | 287 | 27.09.2011 | 287 | 41400 | 41400 | |||
| 43867 | 43967 | 100 | 27.09.2011 | 100 | 41400 | 41400 | |||
| 43967 | 44254 | 287 | 27.09.2011 | 287 | 41400 | 41400 | |||
| 44254 | 44541 | 287 | 27.09.2011 | 287 | 41400 | 41400 |
When I use them as queries in access I get correct values, but when I use them in the C# application the result is different.
Could anybody help me please?
Thanks
NelPosted May 3, 2012, 8:39 AM
oleDBDataAdapter1.SelectCommand = command1;
oleDBDataAdapter1.Fill(dataSet2, "baraniotselect");
oleDBDataAdapter1.SelectCommand = command2;
oleDBDataAdapter1.Fill(dataSet2, "PocKrajRazl1");
I don't understand what could be wrong with it? And I fill the crystal report like this:
cryRpt.Load(@"the path\CrystalReport15.rpt");
I use the designer to create the crystal report and there I define the relationship between tables.
And how to combine your suggestion with my code?
Thanks very much in avdance
Kunal VaishyaPosted May 3, 2012, 8:23 AM
http://msdn.microsoft.com/en-us/library/ay82azad%28v=vs.80%29.aspx
NelPosted May 3, 2012, 8:18 AM
thanks for your try. I tried that but I got wrong result which is given in the table below:
GBRV
Remnal.DATA
pockm
krajkm
Razlika
MaxOfDATAP
MaxOfDATAS
sumkm
MaxOfKMP1
MaxOfKMS1
GBR
Remag1.DATA
AB
Addblue
Antifriz
Gorivo
Motmaslo
Addbluegorivo
I got several rows for one date, and I want only one row for one single date, like the table in the previous post which should be one table - the third table and in the same table to have the colums from the fourth table (the third and fourth table combined).
NIlesh PatelPosted May 3, 2012, 7:38 AM
For Combine This two Table User Inner Join
Select Table1.*,Table2.* from table1
inner join table2 on table2.GBR=table1.GBR