equal sign not recognized

[phpman]

I have this query that works great in vb6, but I am rewriting it for .net 2010. In query builder it tells me that the = sign is not recognized.

SELECT DISTINCT Division, SUM(IIF(`Software Title`= 'MS Office Std', 1, 0)) AS StdProdist, 
SUM(IIF(`Software Title`= 'MS Office Pro Plus', 1, 0)) AS ProProdist
FROM `MS Office Licenses`

Why does it work in vb6 but not .net 2010? Same database is being used, ms access. Same jet engine, or a version of the jet engine.

[stlaural]

Have you tried using "LIKE" instead of the equal sign?

[wild_bill]

Wouldn't this be better?

SELECT Division, [Software Title], count(*)
FROM [MS Office Licenses]
GROUP BY Division, [Software Title]

This is SQL server code, but I think all SQL variations have a group by clause.

[edit] Using derived tables, not sure which databases support this

SELECT d1.Division,StdProdist,ProProdist
FROM
(SELECT Division,  count(*) as StdProdist
FROM [MS Office Licenses]
WHERE [Software Title] = 'MS Office Std') d1
INNER JOIN
(SELECT Division,  count(*) as ProProdist
FROM [MS Office Licenses]
WHERE [Software Title] = 'MS Office Pro Plus') d2 on d1.Divison = d2.Division

[edit again] Judging from IIF, I assume you are using access, and I don't believe it supports derived tables. Can you post the .NET code?

[si_the_geek]

Just the change of "bad fieldname" delimiters (from `` to [], eg: [Software Title] ) might be enough to solve it.

[phpman]

Yes, I have tried "like", then it errors on like. Also I have tried a lot of things like [] `` single quotes and all cry about something in that line.

wild_bill, I am trying to count each software title so I can get a total count across all divisions.

I will try again tomorrow on using brackets. I had it like that from the beginning but was still erroring out on the equal sign.

[phpman]

This is the exact query I am using and it still fails with the same error - Error in list of function arguments: '=' not recognized.
Unable to parse query text.

SELECT DISTINCT Division, SUM(IIF([Software Title] = 'MS Office Std', 1, 0)) AS StdProdist, SUM(IIF([Software Title] = 'MS Office Pro Plus', 1, 0)) AS ProProdist
FROM   [MS Office Licenses]
WHERE  ((Version =  '2007') AND (Replacement = true))
GROUP BY Division
ORDER BY Division

[amrita]

What is the datatype of Replacement ?
I guess the error is with Replacement = true

[phpman]

the datatype for replacement is yes/no so I suppose I could try yes instead of true. but in all reality it is a checkbox so true/false or yes/no should all be the same.

nope, even using a 1 instead of true fails

[phpman]

Also, I can remove the replacement line and it still fails on = not recognized

[GaryMazzone]

Try this

Select Division,[Software Title], COUNT(*)
FROM  [MS Office Licenses]
WHERE WHERE  [Version] =  '2007' AND [Replacement] = true
GROUP BY Division ,[Software Title]
ORDER BY Division , [Software Title]

[phpman]

That works but the output is not what I wanted. I mean it does show the count per division, but not in a nice format. It shows the same division twice, one for each software title.

Of course the editor changed it to Count(*) as Expr1

[GaryMazzone]

then try this:

SELECT DISTINCT Division, SUM(IIF([Software Title] = 'MS Office Std', 1, 0)) AS StdProdist, SUM(IIF([Software Title] = 'MS Office Pro Plus', 1, 0)) AS ProProdist
FROM   [MS Office Licenses]
WHERE  (([Version] =  '2007') AND ([Replacement] = true))
GROUP BY Division
ORDER BY Division

Not sure about replacement but I would bet large sums of cash that Version is a reserved word.

[phpman]

Nope, same error. I even took out Version and just left replacement and it still errored.

[phpman]

So I am thinking that the version of Jet I am using doesn't allow IIF stuff when there is a equal sign in there.

Funny as the version of Jet I was using in VB6 is the same version as what I am using in .NET 2010

Anybody else have an idea?

[wild_bill]

To test your theory, try a little test:

SELECT IIF(1=1, 1, 0)

[phpman]

yes, it tells me the "=" is not recognized

[phpman]

So there is no way to do this? How stupid as it did work in vb6.

[phpman]

I even tried this

SELECT DISTINCT 
                      Division, SUM(CASE [Software Title] WHEN 'MS Office Std' THEN 1 ELSE 0 END) AS StdProdist, 
                      SUM(CASE [Software Title] WHEN 'MS Office Pro Plus' THEN 1 ELSE 0 END) AS ProProdist
FROM         [MS Office Licenses]
WHERE     (Replacement = true)
GROUP BY Division
ORDER BY Division

But it give a big error

Error Message: IErrorInfo GetDescription failed with E_FAIL(0x80004005)

[dee-u]

You could try creating that query in Access itself then just call that query in .Net 2010.

[phpman]

How do you call the query in .NET from access?

[dee-u]

I have tried recreating your scenario and it is odd that I cannot even create your query in Access itself. You are using an aggregate without grouping and Access is supposed to complain about that. This will work for me but not your code:

SELECT Sum([Division]) AS Expr1, SUM(IIF([Software Title]= 'test 2', 1, 0)) AS StdProdist, 
SUM(IIF([Software Title]= 'test 2', 1, 0)) AS ProProdist
FROM table1 GROUP BY  table1.[Software Title]

And this even works in C# 2010:
string test = "SELECT Sum([Division]), SUM(IIF([Software Title]= 'test 2', 1, 0)) AS StdProdist,SUM(IIF([Software Title]= 'test 2', 1, 0)) AS ProProdist FROM table1 GROUP BY table1.[Software Title]";

using (OleDbConnection connection = new OleDbConnection(@"PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\PIS\Database1.mdb;"))
{
    using (OleDbCommand command = new OleDbCommand(test, connection))
    {
        connection.Open();

        using (OleDbDataReader reader = command.ExecuteReader())
        {
            DataTable table = new DataTable();

            table.Load(reader);

            MessageBox.Show("ok");
            // The table can be used here to display the data.
            // That will most likely be done via data-binding but that is NOT a data access issue.
        }
    }
}

Did I miss anything?

[phpman]

I tried that way in .NET and it doesn't give me errors yet it also doesn't get me any values.

I have to think of how to do it a different way I guess.