none
Select, OleDBDataAdapter, "No value given for one or more required parameters."

    Question

  • Hello,

    I have this select statement:

    SELECT
    NOVI.GBR as gbrnov,
    NOVI.AB as abnov,
    NALOG1.DATA as datanov,
    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 as poslprov,
    NOVI.DATAS as poslservis,

    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

    where (((NOVI.GBR)>='1001' And (NOVI.GBR)<='1080') AND ( ((NOVI.AB)='AK') OR ((NOVI.AB)='AK') ) AND ((NALOG1.DATA)>=#10/31/2011# and (NALOG1.DATA)<=#10/31/2011#) )

    GROUP BY NOVI.GBR, NOVI.AB, NALOG1.DATA, ([Addblue]/[Gorivo])*100, NOVI.DATAP, NOVI.DATAS, IIf(([NOVI].[KM]<=([NOVI].[KMS1]+[NOVI].[KMS2])),'ZA SERVIS',(IIf(([NOVI].[KM]<=([NOVI].[KMP1]+[NOVI].[KMP2])),'PROVERKA','')))

    and I use this to fill the dataset in my C# windows application.

    When runnung the program, it throughs an error "No value given for one or more required parameters." when the rows where I define the parameters are commented.

    If I don't comment the rows with parameters, if I use ? instead of concrete values as above in the select, and while running chose the same values as I defined them in the select, I get good result. But I need to use parameters, and also the select should work, when the parameter rows are commented, since I have already given them values.

    Could anybody help me please?

    Thank you in advance.


    • Edited by pet06 Thursday, November 24, 2011 7:52 AM
    Thursday, November 24, 2011 7:52 AM

Answers

All replies

  • Hi pet06,

    I appreciate if you can provide the code you add  the parameters to the dbcommand. This is, so I can help you more effectively.

    Best Regards.


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Friday, November 25, 2011 8:38 AM
    Moderator
  • I solved it
    Friday, November 25, 2011 1:51 PM
  • Hi pet06,

    I'm very glad to hear that you have solved your problem. If you have any problem, please feel free to post in the forum.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Sunday, November 27, 2011 3:54 PM
    Moderator
  • Hi,

    Yes I have solved that, but now I have another problem, the sum in Gorivo and other sums are not correct.

    I made another sql query


    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, which returns correct values, but can you help me please how to implement it in this query (which is the corrected query from previous post):

    SELECT
    NOVI.GBR,
    NOVI.AB,
    Min(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]) And ([NALOG1].[POCKM]>0),[NALOG1.POCKM],Null)) AS pockm,
    Max(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]),[NALOG1.KRAJKM],Null)) AS krajkm,

    (Max(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]),[NALOG1.KRAJKM],Null))-Min(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]) And ([NALOG1].[POCKM]>0),[NALOG1.POCKM],Null))) AS RAZLIKA,

    Sum(IIf(([MAGACIN.SIFRA]="0991000") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]),[MAGACIN.KOL],0)) AS Gorivo,

    Sum(NALOG1.KM) AS SumOfKM, Avg(IIf(([MAGACIN.SIFRA]="0991000" And [NALOG1.KM]<>0),[MAGACIN.KOL],Null)/[NALOG1.KM])*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,

    ((Sum(IIf(([MAGACIN.SIFRA]="0992201"),([MAGACIN.KOL]),Null)))/(Sum(IIf(([MAGACIN.SIFRA]="0991000"),[MAGACIN.KOL],Null))))*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, NOVI.DATAP, NOVI.DATAS, IIf(([NOVI].[KM]<=([NOVI].[KMS1]+[NOVI].[KMS2])),"ZA SERVIS",(IIf(([NOVI].[KM]<=([NOVI].[KMP1]+[NOVI].[KMP2])),"PROVERKA","")))

    HAVING (((NOVI.GBR)>="1002" And (NOVI.GBR)<="1080") AND ((NOVI.AB)="AK"));

    Sorry I have posted a wrong select. Now the second is the corrected one.

    Thanks in addvance. I would be very greatful for any help.

     

    P.S. Sorry I have posted a wrong select, but this is the corrected one.



    • Edited by pet06 Sunday, November 27, 2011 5:37 PM
    Sunday, November 27, 2011 4:21 PM
  • Here is some more help:

    Here is the beggining part of the result table of my select:


    And here is the select:

    SELECT NOVI.GBR, NOVI.AB, Min(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]) And ([NALOG1].[POCKM]>0),[NALOG1.POCKM],Null)) AS pockm, Max(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]),[NALOG1.KRAJKM],Null)) AS krajkm, (Max(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]),[NALOG1.KRAJKM],Null))-Min(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]) And ([NALOG1].[POCKM]>0),[NALOG1.POCKM],Null))) AS RAZLIKA, Sum(IIf(([MAGACIN.SIFRA]="0991000") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]),[MAGACIN.KOL],0)) AS Gorivo, Sum(NALOG1.KM) AS SumOfKM, Avg(IIf(([MAGACIN.SIFRA]="0991000" And [NALOG1.KM]<>0),[MAGACIN.KOL],Null)/[NALOG1.KM])*100 AS potrosgor100km, Sum(IIf(([MAGACIN.SIFRA]="0993050" Or [MAGACIN.SIFRA]="0993051") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]),[MAGACIN.KOL],Null)) AS Motmaslo, Sum(IIf(([MAGACIN.SIFRA]="0992201") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]),[MAGACIN.KOL],Null)) AS Addblue, ((Sum(IIf(([MAGACIN.SIFRA]="0992201") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]),[MAGACIN.KOL],Null)))/(Sum(IIf(([MAGACIN.SIFRA]="0991000") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]),[MAGACIN.KOL],Null))))*100 AS Addbluegor, Sum(IIf(([MAGACIN.SIFRA]="0999001") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]),[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, NOVI.DATAP, NOVI.DATAS, IIf(([NOVI].[KM]<=([NOVI].[KMS1]+[NOVI].[KMS2])),"ZA SERVIS",(IIf(([NOVI].[KM]<=([NOVI].[KMP1]+[NOVI].[KMP2])),"PROVERKA","")))
    HAVING (((NOVI.GBR)>="1002" And (NOVI.GBR)<="1080") AND ((NOVI.AB)="AK"));


    And here is the table MAGACIN that I use in my select only with values for gbr="1002" date between 01.10.2011 and 31.10.2011, and sifra="091000".
    So, the sum of Gorivo, and all the columns where I have sum are not correct. CAn anybody help me please?
    Thanks
    Sunday, November 27, 2011 8:11 PM
  • Hi Pet06,

    I think you can get more help in T-SQL Forum.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, November 28, 2011 2:21 AM
    Moderator