CODEWITHSUNDEEP
c#ms-access
user4268796
user4268796

Reputation: 31

Can't run MS Access query through C#

I have this query in MS Access that runs excellent! 

SELECT 
    BK_LEVEL9.Aitur AS Aitur, 
    Sum(BK_LEVEL9.M_NewQty) AS M_NewQty, 
    Sum(BK_LEVEL9.ErrorCount) AS ErrorCount, 
    (IIf([M_NewQty]<>0,Round(([ErrorCount]*100)/[M_NewQty],2),0)) AS ErrorProcc
FROM 
    BK_LEVEL9
GROUP BY 
    BK_LEVEL9.Aitur;

In my C# program I try to run this query like this:

SQL = "SELECT Aitur,M_NewQty,ErrorCount,ErrorProcc from MyQUERY";
dsWorkKabat = new DataSet();
adp = new OleDbDataAdapter(SQL, Conn);
adp.Fill(dsWorkKabat, "MyQUERY");
adp.Dispose();

and I got this error:

You tried to execute a query that does not include the specified expression 'IIf(Not [M_NewQty]=0,Round([ErrorCount]*100/[M_NewQty],2),0)' as part of an aggregate function.

What could be the problem ?

Thanks

Upvotes: 1

Views: 67

Answers (2)

bubi
bubi

Reputation: 6491

Making some tests it seems that access computes the first three columns (the group by query) than computes ErrorProcc using the aliases.

Take care because the suggestion of israel altar does not work if BK_LEVEL9.Aitur is not a primary key (I'm quite sure is not otherwise the MyQuery query does not require a Group by clause).
Why doesn't it work: Access computes the Group by before performing sums so the Group by of the IIf is based on the BK_LEVEL9.M_NewQty and BK_LEVEL9.ErrorCount and not on the aliases.

What is the solution? Probably the simplest is to delete the IIf from MyQuery than compute it in the ADO query.

Upvotes: 0

israel altar
israel altar

Reputation: 1794

For almost every DBMS (MySQL is the only exception I'm aware of, but there could be others), every column in a SELECT that is not aggregated needs to be in the GROUP BY clause. In the case of your query you need to group it like that:

GROUP BY 
    BK_LEVEL9.Aitur, IIf([M_NewQty]<>0,Round(([ErrorCount]*100)/[M_NewQty],2),0);

Upvotes: 1

Related Questions