Hi,
I have a Crystal Report and I use dataset and OleDbDataAdapter to get the values that the user chose on the form.
Here is my code:
ReportDocument cryRpt = new ReportDocument();
string comstring = "SELECT NOVI.GBR, NOVI.AB, NALOG1.DATA,Min(NALOG1.POCKM) AS MinOfPOCKM,Max(NALOG1.KRAJKM) AS MaxOfKRAJKM,(Max(NALOG1.KRAJKM)-Min(NALOG1.POCKM)) AS RAZLIKA FROM (NALOG1 INNER JOIN MAGACIN ON NALOG1.GBRV=MAGACIN.GBR) INNER JOIN NOVI ON NALOG1.GBRV=NOVI.GBR where ( ( (NOVI.GBR)>='?' And (NOVI.GBR)<='?' ) AND ( (NOVI.AB)='?' or (NOVI.AB)='?' ) AND ( ( (NALOG1.DATA)>=?) and ((NALOG1.DATA)<=?) ) ) GROUP BY NOVI.GBR, NOVI.AB, NALOG1.DATA";
OleDbCommand command = new OleDbCommand();
command.Connection = conn;
command.CommandText = comstring;
if (radioButton4.Checked==true){
command.Parameters.AddWithValue("@gbr1", textBox1.Text);
command.Parameters.AddWithValue("@gbr2", textBox1.Text);
}
if (radioButton5.Checked==true){
command.Parameters.AddWithValue("@gbr1", "1001");
command.Parameters.AddWithValue("@gbr2", "1080");
}
if (radioButton6.Checked==true){
command.Parameters.AddWithValue("@gbr1","1081" );
command.Parameters.AddWithValue("@gbr2", "1149");
}
if (radioButton7.Checked == true)
{
command.Parameters.AddWithValue("@gbr1", "1001");
command.Parameters.AddWithValue("@gbr2", "1149");
}
if(checkBox1.Checked==true){
command.Parameters.AddWithValue("@ab1", 1);
command.Parameters.AddWithValue("@ab2", 1);
}
else if (checkBox2.Checked==true)
{
command.Parameters.AddWithValue("@ab1", 2);
command.Parameters.AddWithValue("@ab2", 2);
}
else if((checkBox1.Checked==true) && (checkBox2.Checked==true))
{
command.Parameters.AddWithValue("@ab", 1);
command.Parameters.AddWithValue("@ab",2);
}
command.Parameters.AddWithValue("@data1", dateTimePicker1.Value);
command.Parameters.AddWithValue("@data2", dateTimePicker2.Value);
DataSet dataSet2=new DataSet();
conn.Open();
OleDbDataAdapter oleDBDataAdapter1 = new OleDbDataAdapter(new OleDbCommand(comstring, conn));
OleDbCommand command1 = new OleDbCommand();
command1.Connection = conn;
oleDBDataAdapter1.SelectCommand.CommandText=comstring;
dataSet2.Clear();
oleDBDataAdapter1.Fill(dataSet2);
cryRpt.SetDataSource(dataSet2);
crystalReportViewer1.DisplayToolbar=true;
cryRpt.Load(@"E:\Potrosuvacka-Pregledi\Potrosuvacka-Pregledi\CrystalReport1.rpt");
crystalReportViewer1.ReportSource = cryRpt;
cryRpt.PrintOptions.PaperOrientation = CrystalDecisions.Shared.PaperOrientation.Landscape;
crystalReportViewer1.Refresh();
conn.Close();
System.Data.OleDb.OleDbException was unhandled
Message="No value given for one or more required parameters."
Could anybody help me please?
Thanks in advance
Loading
NelPosted Jan 17, 2012, 7:30 AM
NelPosted Nov 26, 2011, 2:03 PM
SELECT MAGACIN.GBR, Sum(MAGACIN.KOL) AS Gorivo
FROM MAGACIN
WHERE (((MAGACIN.DATA) Between #10/1/2011# And #10/31/2011#))
GROUP BY MAGACIN.GBR, MAGACIN.SIFRA
HAVING (((MAGACIN.GBR)>='1001' And (MAGACIN.GBR)<='1080') AND ((MAGACIN.SIFRA)='0991000'));
for getting the sum of KOL as Gorivo, but can you help me please how to implement t in this query (which is the corrected query from previous post):
SELECT NOVI.GBR, NOVI.AB, Min(NALOG1.DATA) AS MinOfDATA, Max(NALOG1.DATA) AS data2, Min(NALOG1.POCKM) AS MinOfPOCKM, Max(NALOG1.KRAJKM) AS MaxOfKRAJKM, (Max(NALOG1.KRAJKM)-Min(NALOG1.POCKM)) AS RAZLIKA, Count(IIf(([MAGACIN.SIFRA]="0991000"),[MAGACIN.KOL],Null)) AS Gorivo, Avg(IIf(([MAGACIN.SIFRA]="0991000"),[MAGACIN.KOL],Null)/100) AS potrosgor100km, Sum(IIf(([MAGACIN.SIFRA]="0993050" Or [MAGACIN.SIFRA]="0993051"),[MAGACIN.KOL],Null)) AS Motmaslo, Sum(IIf(([MAGACIN.SIFRA]="0992201"),[MAGACIN.KOL],Null)) AS Addblue, ([Addblue]/[Gorivo])*100 AS Addbluegor, Sum(IIf(([MAGACIN.SIFRA]="0999001"),[MAGACIN.KOL],Null)) AS Antifriz, NOVI.DATAP, NOVI.DATAS, IIf(([NOVI].[KM]<=([NOVI].[KMS1]+[NOVI].[KMS2])),"ZA SERVIS",(IIf(([NOVI].[KM]<=([NOVI].[KMP1]+[NOVI].[KMP2])),"PROVERKA",""))) AS Zabeleska
FROM (NALOG1 INNER JOIN NOVI ON NALOG1.GBRV = NOVI.GBR) INNER JOIN MAGACIN ON NOVI.GBR = MAGACIN.GBR
GROUP BY NOVI.GBR, NOVI.AB, ([Addblue]/[Gorivo])*100, NOVI.DATAP, NOVI.DATAS, IIf(([NOVI].[KM]<=([NOVI].[KMS1]+[NOVI].[KMS2])),"ZA SERVIS",(IIf(([NOVI].[KM]<=([NOVI].[KMP1]+[NOVI].[KMP2])),"PROVERKA","")))
HAVING (((NOVI.GBR)>="1082" And (NOVI.GBR)<="1149") AND ((NOVI.AB)="AK") AND ((Min(NALOG1.DATA))>=#10/1/2011#) AND ((Max(NALOG1.DATA))<=#10/31/2011#));
Thanks in advance.
Pravin GhadgePosted Nov 26, 2011, 8:04 AM
This may be due to you have entered wrong column name in ur qury.
Plz check ur query.
Regards,
Pravin