Sql statement in C# is giving error sqlexception

Question

0.00/5 (No votes)

See more:

here is my code

C#

//cmd is already declared as string
cmd = "SELECT SUM(purchasePrice) AS price, COUNT(purchasePrice) AS num" +
"FROM ( SELECT TOP ("+quantity+") purchasePrice FROM store.dbo.tblSingleItems" +
"WHERE IsActive='1' AND proID='" + proID + "') AS profit" +
"UPDATE TOP ("+quantity+") tblSingleItems SET IsActive='1',salePrice='"
 + price +"' " +
"WHERE IsActive='1' AND proID='" + proID + "'";

SqlCommand sqlcmd = new SqlCommand(cmd, con);
SqlDataAdapter sda = new SqlDataAdapter(sqlcmd);
DataTable tbl = new DataTable();
sda.Fill(tbl);
con.Close();

when this executes in app, gives Syntax error near '='

What I have tried:

i tried this command in sql server 2014 that gives me output without any error. i had a very careful look but couldnot find any syntax error. if anyone can recognize

Posted 8-Dec-17 4:58am

Arham Anees

Updated 8-Dec-17 5:49am

Add a Solution

Comments

Richard Deeming 8-Dec-17 11:16am

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

3 solutions

Solution 2

If you debug your code and look at the final string, you'll see the problem: you're missing spaces across several lines.

C#

const string query = 
    "SELECT SUM(purchasePrice) AS price, COUNT(purchasePrice) AS num " + // <--Add a space here
    "FROM ( SELECT TOP (@quantity) purchasePrice FROM store.dbo.tblSingleItems " + // <-- And here
    "WHERE IsActive = '1' AND proID = @proID) AS profit; " + // <-- And here
    "UPDATE TOP (@quantity) tblSingleItems SET IsActive = '1', salePrice = @price "
    "WHERE IsActive='1' AND proID = @proID;";

using (SqlCommand sqlcmd = new SqlCommand(query, con))
{
    sqlcmd.Parameters.AddWithValue("@quantity", quantity);
    sqlcmd.Parameters.AddWithValue("@proID", proID);
    sqlcmd.Parameters.AddWithValue("@price", price);
    
    SqlDataAdapter sda = new SqlDataAdapter(sqlcmd);
    DataTable tbl = new DataTable();
    sda.Fill(tbl);
    ...
}

Posted 8-Dec-17 5:21am

Richard Deeming

Solution 3

You need some spaces at the beginning and end of your strings to separate the sql statements so they don't run on to one another. i.e. +"WHERE should be + " WHERE" Also, you really should be using parameters in your query.

Posted 8-Dec-17 5:27am

Wes Carroll

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

David_Wimbley · Accepted Answer · 2017-12-08T05:19:00

First thing, this is not how you should be building a sql statement, you are open to sql injection attacks here.

Parameterizing sql queries c#

Next, simply look at your query. You've got no space after store.dbo.tblSingleItems and the following line of WHERE IsActive = '1'. So your query, when parsed, looks like this, to make it more obvious, here it is all on one line

SQL

SELECT SUM(purchasePrice) AS price, COUNT(purchasePrice) AS numFROM ( SELECT TOP ("+quantity+") purchasePrice FROM store.dbo.tblSingleItemsWHERE IsActive='1' AND proID='" + proID + "') AS profitUPDATE TOP(1) tblSingleItems SET IsActive='1',salePrice='1' WHERE IsActive='1' AND proID='1';

So you probably need to adjust your code to look like this

C#

cmd = "SELECT SUM(purchasePrice) AS price, COUNT(purchasePrice) AS num " +
"FROM ( SELECT TOP ("+quantity+") purchasePrice FROM store.dbo.tblSingleItems " +
"WHERE IsActive='1' AND proID='" + proID + "') AS profit " +
"UPDATE TOP ("+quantity+") tblSingleItems SET IsActive='1',salePrice='" + price +"' " + " WHERE IsActive='1' AND proID='" + proID + "'";

Notice the additional spaces. I think you may have issues in your SELECT statement as it doesn't make much sense to me but if that portion works then great. Just addressing the syntax error.