查询在Access中运行，但在C#DataSet中生成错误 [英] Query runs in Access but generates error in C# DataSet

问题描述

我有此查询，当我在Access中对其进行测试时，witch运行得很好，但是我正在外部C#程序中从TSQL 运行它(老实说，我仍在学习术语，所以我认为TSQL对我正在做的事情是正确的(不是).当我尝试将过程存储在XSD文件中的tableadapter中时，出现错误(请参见查询下方).结果，不会自动创建正确的XML模式.虽然查询仍在运行 ，但它也给出了一个错误.我希望它运行时不会出现错误，但无法弄清楚该错误的真正位置.我尝试通过用CASE语句替换IIF来消除所有的"="，但是随后出现其他错误(如果有帮助，我可以发布该尝试)

I have this query, whoch runs fine when I test it in Access, but I'm running it from TSQL in an outside C# program (honestly, I'm still learning the terms so I think TSQL is correct for what I'm doing (It was not)). When I try to store the procedure in a tableadapter in my XSD file I get an error (see below the query). As a result the proper XML schema is not automatically created. While the query still runs it also gives an error. I'd like it to run without the error but can't figure out where that error really is. I tried eliminating all the "=" by replacing the IIFs with CASE statements but then I got other errors ( I can post that attempt if it would be helpful)

SELECT  IIF(ISNULL(Owners.OwnFirstName), Owners.OwnLastName, 
         Owners.OwnFirstName + ' ' + Owners.OwnLastName) AS [Owner's Name], SUM(iif(UnitBarcodes.NephStatus = 'N', 1, 0)) 
         AS [Neph units], SUM(iif(UnitBarcodes.NephStatus = 'F', 1, 0)) AS [Filter units], COUNT(UnitBarcodes.NephStatus) 
         AS [Total Units]
FROM  (Owners INNER JOIN
         ((UnitBarcodes INNER JOIN
         AssembledUnits ON UnitBarcodes.ID = AssembledUnits.CaseID) INNER JOIN
         OwnerUnits ON AssembledUnits.ID = OwnerUnits.AssembledUnitID) ON Owners.ID = OwnerUnits.OwnerID)
GROUP BY IIF(ISNULL(Owners.OwnFirstName), Owners.OwnLastName, 
         Owners.OwnFirstName + ' ' + Owners.OwnLastName)

查询生成器中的错误消息

Error message from Query Builder

Error in list of function arguments: '=' not recognized.
Unable to parse query text.

无论如何，谢谢您的帮助！

Anyway, thanks for the help!

这是我以前用CASE替换IIF的尝试

This was my previous attempt at replacing the IIFs with CASEs

SELECT   (case when ISNULL(Owners.OwnFirstName) then Owners.OwnLastName else (Owners.OwnFirstName + ' ' + Owners.OwnLastName) end) AS [Owner's name],
          SUM(case where UnitBarcodes.NephStatus = 'N' then  1 else 0 end)) 
          AS [Neph units], SUM( case when UnitBarcodes.NephStatus = 'F' then 1 else 0 end)) AS [Filter units], COUNT(UnitBarcodes.NephStatus) 
          AS [Total units]
FROM         (Owners inner JOIN
          ((UnitBarcodes inner JOIN
          AssembledUnits ON UnitBarcodes.ID = AssembledUnits.CaseID) INNER JOIN
          OwnerUnits ON AssembledUnits.ID = OwnerUnits.AssembledUnitID) ON Owners.ID = OwnerUnits.OwnerID)
GROUP BY (case when ISNULL(Owners.OwnFirstName) then Owners.OwnLastName else (Owners.OwnFirstName + ' ' + Owners.OwnLastName) end) AS [Owner's name]

我得到的错误是

Error in list of function arguments: 'ISNULL' not recognized.
Error in list of function arguments: ')' not recognized.
Unable to parse query text.

因此该查询可以正常工作

So this query works just fine

SELECT     IIF(ISNULL(Owners.OwnFirstName), Owners.OwnLastName, 
            Owners.OwnFirstName + ' ' + Owners.OwnLastName) AS [Owner's Name], COUNT(UnitBarcodes.NephStatus) 
            AS [Total units]
FROM         (Owners INNER JOIN
            ((UnitBarcodes INNER JOIN
            AssembledUnits ON UnitBarcodes.ID = AssembledUnits.CaseID) INNER JOIN
            OwnerUnits ON AssembledUnits.ID = OwnerUnits.AssembledUnitID) ON Owners.ID = OwnerUnits.OwnerID)
GROUP BY IIF(ISNULL(Owners.OwnFirstName), Owners.OwnLastName, 
            Owners.OwnFirstName + ' ' + Owners.OwnLastName)

但是，一旦我添加任何总和(通过CASE或IIF)，查询就会生成错误.

But as soon as I add any Sum (be it through CASEs or IIFs) the query generates an error.

推荐答案

您需要将IIF语句转换为CASE语句，因为IIF不是有效的T-SQL语法.

You need to convert your IIF statements to CASE statements, IIF is not valid T-SQL syntax.

针对您的评论，这不是在t-sql中执行空检查的方式，这是一个示例:

In response to your comment, that's not how a null check is done in t-sql, here is an example:

CASE
     WHEN SomeValue IS NULL THEN
          SomeDefault
     ELSE
          SomeValue 

END

这篇关于查询在Access中运行，但在C#DataSet中生成错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！