COUNT verb not recognized in query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

COUNT verb not recognized in query

Author

Topic

parrot
Posting Yak Master

132 Posts

Posted - 2008-06-04 : 14:53:11

I get a 'Specified cast is invalid' error message when I use the following query statement which has a COUNT verb in it.

SELECT DATAFIELD COUNT(DATAFIELD) AS 'Count' FROM EETABLE BY DATAFIELD

I only get this error when running the query in my C# program which is using SQL SERVER 2005 under VISTA. If I manually submit a query in Microsoft SQL Server Management Studio I receive no error. Also, I get no error if I run this program on an XP computer using SQL SERVER 2000. So where do I look for the source of the problem. Any help is appreciated.
Dave

parrot
Posting Yak Master

132 Posts

Posted - 2008-06-04 : 14:57:07

I forgot to mention that I am developing this program with Visual Studio 2005.
Dave

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-04 : 15:04:11

Are you sure that's your query as you are missing a comma and the word group before the by?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

parrot
Posting Yak Master

132 Posts

Posted - 2008-06-04 : 16:04:56

I am sorry for the incorrect statement. The correct statement is below:
SELECT DATAFIELD, COUNT(DATAFIELD) AS 'Count' FROM EETABLE GROUP BY DATAFIELD

Another interesting twist is that an older version of this program does not have this problem. The older version was developed in the same Visual Studio 2005 environment in January. Now when I compile the program I have this problem. I don't know if an update to Visual Studio 2005 caused the problem or what but it is really mystifying.
Dave

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-04 : 16:06:06

What data type is DATAFIELD?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

parrot
Posting Yak Master

132 Posts

Posted - 2008-06-04 : 16:08:17

It is an nvarchar(6) field.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-04 : 16:10:47

I don't see any reason why you would get that error in Visual Studio as everything is fine. Perhaps you need to get your VS up to the latest service pack and patch.

If you can't get around this error, I'd suggest wrapping it into a stored procedure and calling the stored procedure from your application rather than using inline SQL. That way, VS will have no idea what is inside it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

parrot
Posting Yak Master

132 Posts

Posted - 2008-06-04 : 16:34:47

Thanks for your feedback. I have installed Visual Studio 2008. Do you think it would be advisable now to migrate the system over to 2008 to see if that takes care of it? I am under a 2 week time pressure to get this thing working for a client.
Dave

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-04 : 16:36:41

Why can't you just use a stored procedure instead?

I certainly wouldn't want to go to VS 2008 just to fix this error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

parrot
Posting Yak Master

132 Posts

Posted - 2008-06-04 : 16:37:03

Also, I am using this in a program that is supposed to be a dynamic report builder which means using a Stored Procedure is out of the question as I have no idea how many different permutations my client will use in building an SQL query through my program.
Dave

parrot
Posting Yak Master

132 Posts

Posted - 2008-06-04 : 16:57:49

One other thing to consider is that the version of Visual Studio in which it doesn't work has SP1 whereas the earlier version of Visual Studio does not have this problem. It sure seems like a bug to me and I can't believe that someone else doesn't have this problem. Is there a later service pack beyond SP1?
Dave

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources