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
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
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.