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 2008 Forums
 Transact-SQL (2008)
 What is wrong with this Query

Author  Topic 

gaby_58
Starting Member

33 Posts

Posted - 2012-06-25 : 16:35:32
Hi all,

Don't see anything wrong with this query, still it gives as incorrect syntax near GROUP, what is wrong here..thanks for any tips.

conS3 = new SqlConnection(WebConfigurationManager.ConnectionStrings["test"].ConnectionString);
conS3.Open();
cmdS3 = new SqlCommand("SELECT TOP 1 SupplierId, Supplier,"
+ "SUM(TotalValue) FROM viewtest WHERE YEAR(YDate)='"
+ pRefYear.ToString() + "' AND " + strR + "Id IN(" + strId
+ ")AND SupplierId <>"
+ strSupplierId[0]
+ "AND SupplierId <>"
+ strSupplierId[1]
+ "AND SupplierId <>"
+ strSupplierId[2]
+ "GROUP BY SupplierId, Supplier ORDER BY SUM(TotalValue) DESC", conS3);

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-25 : 16:41:54
The glaring issue is that your code is subject to SQL injection. Never use concatenated queries like this. Switch to parameterized queries, and you'll find your code is easier to read and write.

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

Subscribe to my blog
Go to Top of Page

gaby_58
Starting Member

33 Posts

Posted - 2012-06-25 : 16:58:31
Could you please give me an example for the above query? how to write parameterized query for the above types?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-25 : 17:03:50
http://www.codinghorror.com/blog/2005/04/give-me-parameterized-sql-or-give-me-death.html

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

Subscribe to my blog
Go to Top of Page

andersqwe
Starting Member

4 Posts

Posted - 2012-06-25 : 17:05:47
Well it is hard to tell without seeing the parsed sql coukd you get this at runtime via the immediate window?

Its me
Go to Top of Page

andersqwe
Starting Member

4 Posts

Posted - 2012-06-25 : 17:06:22
Well it is hard to tell without seeing the parsed sql coukd you get this at runtime via the immediate window?

Its me
Go to Top of Page

gaby_58
Starting Member

33 Posts

Posted - 2012-06-26 : 09:57:25
Could you please give me the proper version for the above query i mentioned, that way I can use it and see.

Thanks for your help
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-26 : 11:29:49
gaby, we need the parsed query to help you.

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

Subscribe to my blog
Go to Top of Page
   

- Advertisement -