使用参数和OleDbCommand、Crystal reports时分组
使用参数和OleDbCommand、Crystal reports时分组
提问于 2011-11-23 19:15:00
我有一个带有group by和参数的sql语句：
string comstring =
"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,
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
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,
([Addblue]/[Gorivo])*100,
NOVI.DATAP,
NOVI.DATAS,
IIf(([NOVI].[KM]<=([NOVI].[KMS1]+[NOVI].[KMS2])),'ZA SERVIS',(IIf(([NOVI].[KM]<=([NOVI].[KMP1]+[NOVI].[KMP2])),'PROVERKA','')))";我在No value given for one or more required parameters中遇到错误
oleDBDataAdapter1.Fill(dataSet2,"Tabela");当它与某些值一起使用时，例如：
string comstring =
"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,
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
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','')))";没有任何问题。
有谁能帮帮我吗？
Stack Overflow用户
发布于 2011-11-23 21:26:35
这就是你的查询的问题：
where (((NOVI.GBR)>=? And (NOVI.GBR)<=?)
AND ( ((NOVI.AB)=?)
OR ((NOVI.AB)=?) )
AND ((NALOG1.DATA)<=? and (NALOG1.DATA)>=?) ) 您需要提供适当的值而不是?，您可能会使用'?'，但我认为它不适用于(NALOG1.DATA)(通过查看其他查询，我猜它的数据类型是Date)
用于注释的编辑：
如果您正在使用参数，则需要按照使用?的顺序传递它们。在oledbcommand中，不能使用如下所示的命名参数
command.Parameters.AddWithValue("@param1", "1001");如果您在查询中使用了6个?，则需要按照顺序传递6个参数。
OleDbParameter[] oledbParams = new OleDbParameter[6];
//To be added in proper order.
oledbParams[0] = new OleDbParameter("PN1", "1001");
oledbParams[1] = new OleDbParameter("PN2", "1002");
.
.
oledbParams[6] = new OleDbParameter("DateValue", DateTime.Now.Date);
foreach (var p in oledbParams)
{
command.Parameters.Add(p);
}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/8241232
复制相关文章
相似问题
腾讯云开发者
