Last post Aug 05, 2017 08:53 AM by Mr.Torbati
Aug 04, 2017 05:15 PM|Mr.Torbati|LINK
I have codes as below when execute server show error:
Description: An unhandled exception occurred during the execution of the current web request. Please
review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'.
string strUserName = User.Identity.Name.ToString();
SqlConnection con = new SqlConnection(strConnString);
str = "INSERT INTO ProductsOrder (Fish, trackingCode, DateTraconesh) VALUES ('" + SaleReferenceIdLabel.Text + "', '" + SaleOrderIdLabel.Text + "', '" + DateTime.Now.ToString("yy-MM-dd") + "') WHERE userName = '" + strUserName + "' AND trackingCode IS NOT NULL ";
SqlCommand cmd1 = new SqlCommand(str, con);
Anyone know what's wrong?
Aug 04, 2017 05:37 PM|JBetancourt|LINK
the WHERE part doesnt seem to belong to this query, you are inserting new VALUE records in a table, there is no query to run the
WHERE against to.
if you would have "insert into a(f1,f2) select f1,f2 from b
where ..." in that case the where would make sense, but your query looks like:
"insert into a(f1,f2) values(2,3) where ..." this where has no reason to be there
just remove everything after the where including the where
by the way insert add new values, if your intention is to modify some data, that would be a different story, in that case what would be wrong is that you should use "UPDATE.." instead of "INSERT..."
Aug 04, 2017 05:39 PM|mgebhard|LINK
Anyone know what's wrong?
Please learn how to debug your code. First, it is practically impossible for anyone to provide a accurate solution due to the dynamic SQL and not knowing the values being submitted. However, I think the date format need a 4-digit year and I'm not sure what
the WHERE clause is for. Are you trying to do an update? Or Perhaps you want to INSERT the username as well?
You can figure this out yourself simply by placing a break point on the SQL string. Copying the SQL string and paste it into SSMS. Then run the query.
Also consider using a parameterized query which will fix this type of string concatenation errors plus it stops nasty SQL injection vulnerabilities.
Aug 04, 2017 05:42 PM|A2H|LINK
If you intend is to update an existing entry on database then you need to use
instead of Insert
Aug 04, 2017 05:43 PM|DA924|LINK
You don't know what is coming in the data that can make the T-SQL become malformed T-SQL, like commas or quotes coming in the input data. And besides, you are wide open to SQL Injection
attack using T-SQL in the manner you are using it in an Web UI solution.
Using parametrized T-SQL solves both problems.
You could solve both this way too.
Aug 05, 2017 08:50 AM|Mr.Torbati|LINK
thanks for help.
Aug 05, 2017 08:53 AM|Mr.Torbati|LINK
Thanks a lot for correct and important warning.