保存的Access2003查询在Access中工作正常但在C＃中运行时没有 [英] Saved Access2003 Query Works Fine in Access but not when Run in C#

问题描述

大家好，我有一个Access 2003数据库。该数据库包含大约60,000行处理其供应商数据的数据，例如总TargetWeight，ActualWeight等...我正在从这个数据库中收集数据以填充c＃中的mt gridview，我可以稍后开发一个显示各种类型的摘要报告平均TargetWeight，AVG（ActualWeight）和按ID分组的其他信息。这很容易吗？

查询在Access中运行正常，但是在c＃中我一直得到错误，就像使用保留字一样，事实上我已经尝试了从c＃到Access DB的查询中的所有聚合函数，但到目前为止失败了，如果我遗漏了一些东西，请帮帮我。



command.CommandText =SELECT t1.IngredientLotId1，t1.IngredientName，t1.ActualWeight， t1.TargetWeight，ROUND（（t1.ActualWeight- t1.TargetWeight），0）AS VARIANCE，ROUND（（0.04 * t1.TargetWeight），0）AS TOLERANCE，ROUND（（[t1]。[TargetWeight]），0） ，AVG（t1.TargetWeight）AS AvgTargetWeight FROM CompletedIngredients as t1 INNER JOIN CompletedFormulas as t2 ON t1.RecordNumber = t2.RecordNumber ORDER BY t2.StartTime ASC;

Hello all, I have an Access 2003 database . That database contains around 60,000 rows of data dealing with their vendor data such as total TargetWeight, ActualWeight , etc... I'm collecting data from this database to populate mt gridview in c#which I can later develope a Summary report of sorts showing average TargetWeight, AVG(ActualWeight)and other info grouped by the ID . Easy enough right?
Query works fine in Access ,but in c# I keep getting error like am using a reserved word, And in fact I have tried all the Aggregate Function in my query from c# to Access DB ,but failed up to this point, help me if am missing something thanks.

command.CommandText = " SELECT t1.IngredientLotId1, t1.IngredientName, t1.ActualWeight, t1.TargetWeight, ROUND((t1.ActualWeight- t1.TargetWeight ),0)AS VARIANCE,ROUND( (0.04* t1.TargetWeight ),0)AS TOLERANCE,ROUND(([t1].[TargetWeight]),0),AVG(t1.TargetWeight) AS AvgTargetWeight FROM CompletedIngredients As t1 INNER JOIN CompletedFormulas As t2 ON t1.RecordNumber=t2.RecordNumber ORDER BY t2.StartTime ASC;

推荐答案

有一个逗号不应该在你的 FROM 语句之前。

There is a comma that should not be here just before your FROM statement.

正如我在评论中提到的，一个可能的您的查询不起作用的原因是您忘记添加 GROUP BY 子句以及 AVG（）函数。 Finall查询应如下所示：

As i mentioned in comment to the question, one possible reason that your query does not work is that you forgot to add GROUP BY clause together with AVG() function. Finall query should look like:

SELECT t1.IngredientLotId1, t1.IngredientName, t1.ActualWeight, t1.TargetWeight, ROUND((t1.ActualWeight- t1.TargetWeight ),0) AS VARIANCE,
    ROUND( (0.04* t1.TargetWeight ),0) AS TOLERANCE, ROUND(([t1].[TargetWeight]),0) AS TargetWeight, AVG(t1.TargetWeight) AS AvgTargetWeight
FROM CompletedIngredients As t1 INNER JOIN CompletedFormulas As t2 ON t1.RecordNumber=t2.RecordNumber
GROUP BY t1.IngredientLotId1, t1.IngredientName, t1.ActualWeight
-- ORDER  BY t2.StartTime ASC;

这篇关于保存的Access2003查询在Access中工作正常但在C＃中运行时没有的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！