Hello,
I have a dataset with three data tables. And my database is in MS Access. I fill all of them with separate OleDBDataAdapters. But when I run the windows form it returns empty crystall report. In fact when debugging, I see that the dataset is empty, all of the tables remain empty.
Here are the select strings that I use to fill the datatables:
1. For filling the first table "baraniotselect1" in the dataset I use this select
SELECT MAGACIN.GBR, [@data1] AS Datapoc, [@data2] AS Datakr, Sum(IIf([MAGACIN.SIFRA]="0992201",MAGACIN.KOL,0)) AS Addblue, Sum(IIf([MAGACIN.SIFRA]="0999001",MAGACIN.KOL,0)) AS Antifriz, Sum(IIf([MAGACIN.SIFRA]="0991000",MAGACIN.KOL,0)) AS Gorivo, Sum(IIf((([MAGACIN.SIFRA])="0993050" Or ([MAGACIN.SIFRA])="0993051"),MAGACIN.KOL,0)) AS Motmaslo, Round(((Sum(IIf(([MAGACIN.SIFRA]="0992201") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]) And ([MAGACIN.GBR] Between [@gbr1] And [@gbr2]) And ((NOVI.AB)=[@ab1] or (NOVI.AB)=[@ab2]) ,[MAGACIN.KOL],0))/(Sum(IIf(([MAGACIN.SIFRA]="0991000") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]) And ((NOVI.AB)=[@ab1] or (NOVI.AB)=[@ab2]),[MAGACIN.KOL],0))))*100),2) AS Addbluegor
FROM NOVI INNER JOIN MAGACIN ON NOVI.GBR = MAGACIN.GBR
WHERE (((MAGACIN.DATA) Between [@data1] And [@data2]) AND ((MAGACIN.GBR) Between [@gbr1] And [@gbr2]) AND ((NOVI.AB)=[@ab1] or (NOVI.AB)=[@ab2]) )
GROUP BY MAGACIN.GBR
HAVING ((Sum(IIf([MAGACIN.SIFRA]="0991000",[MAGACIN].[KOL],0))>0));
2. For filling the second table "PocKrajRazl" in the dataset I use:
SELECT NALOG1.GBRV, Min(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]) And ([NALOG1].[POCKM]>0) And ([NALOG1.GBRV] Between [@gbr1] And [@gbr2]),[NALOG1.POCKM],Null)) AS pockm, Max(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]) And ([NALOG1.GBRV] Between [@gbr1] And [@gbr2]),[NALOG1.KRAJKM],Null)) AS krajkm, (Max(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]) And ([NALOG1.GBRV] Between [@gbr1] And [@gbr2]),[NALOG1.KRAJKM],Null))-Min(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]) And ([NALOG1].[POCKM]>0),[NALOG1.POCKM],Null))) AS RAZLIKA, Max(NOVI.DATAP) AS Poslprov, Max(NOVI.DATAS) AS Poslserv, Sum(NALOG1.KM) AS SUMKM, IIf([SUMKM]>=(([NOVI].[KMS1]+[NOVI].[KMS2])-1000),"ZA SERVIS",
(IIf(Sum(NALOG1.KM)>=(([NOVI].[KMP1]+[NOVI].[KMP2])-1000),"PROVERKA",""))) AS Zabeleska
FROM NALOG1 INNER JOIN NOVI ON NALOG1.GBRV = NOVI.GBR
WHERE ((NOVI.AB)=[@ab1] or (NOVI.AB)=[@ab2]) AND ( (NALOG1.GBRV)>=[@gbr1] And (NALOG1.GBRV)<=[@gbr2] )
GROUP BY NALOG1.GBRV, NOVI.KMP1, NOVI.KMP2, NOVI.KMS1, NOVI.KMS2;
in my C# windows form.
And I use these selects i.e. tables to fill the third table with this select:
SELECT baraniotselect1.GBR, baraniotselect1.Datapoc, baraniotselect1.Datakr, baraniotselect1.Gorivo, PocKrajRazl.SUMKM, ROUND(((baraniotselect1.Gorivo/PocKrajRazl.SUMKM)*100),2) AS Prospotrgor, baraniotselect1.Addblue, baraniotselect1.Addbluegor, baraniotselect1.Antifriz, baraniotselect1.Motmaslo, PocKrajRazl.pockm, PocKrajRazl.krajkm, PocKrajRazl.RAZLIKA, PocKrajRazl.Poslprov, PocKrajRazl.Poslserv, PocKrajRazl.Zabeleska
FROM baraniotselect1 LEFT JOIN PocKrajRazl ON baraniotselect1.GBR = PocKrajRazl.GBRV;
Can anybody tell me please what am I doing wrong? Why the dataset remains empty?
I appreciate any help a lot
Loading
NelPosted Feb 27, 2012, 1:33 AM
SenthilkumarPosted Feb 24, 2012, 5:58 AM
checking in the above query is not easy.
I would suggest some thing for you.
Please move all these inline queries into stored procedure. (improving performance)
Next, if it is not giving any error in the code then definitely some issues with the query.
Please execute this query in sql server window and pass the value to the query.
Check any value is giving.
Two reasons:
Joins may be issue, there is no matching data (no reference)
There is no data