SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 What is wrong with this Query		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gaby_58
Starting Member

8 Posts
Posted - 06/25/2012 :  16:35:32  Show Profile  Reply with Quote
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

USA
35007 Posts
Posted - 06/25/2012 :  16:41:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

8 Posts
Posted - 06/25/2012 :  16:58:31  Show Profile  Reply with Quote
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

USA
35007 Posts
Posted - 06/25/2012 :  17:03:50  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

United Kingdom
4 Posts
Posted - 06/25/2012 :  17:05:47  Show Profile  Reply with Quote
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

United Kingdom
4 Posts
Posted - 06/25/2012 :  17:06:22  Show Profile  Reply with Quote
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

8 Posts
Posted - 06/26/2012 :  09:57:25  Show Profile  Reply with Quote
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

USA
35007 Posts
Posted - 06/26/2012 :  11:29:49  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000