Error sqlcommand.ExecuteNonQuery()

Question

I have a table in my sql server 2008 database

COMMENT(idcom,content,username);

This table has 2 records which username = lecturer

Now I want to delete all comments which have username= lecturer:

First, I test the query in SQL Server:

DELETE FROM COMMENT WHERE USERNAME='lecturer' -> it works fine: 2 records were deleted.

Then I applied that query in my c# code :

public bool delete(string userdeleted)
{
    string sql="DELETE FROM COMMENT WHERE USERNAME="+userdeleted; //userdeleted= lecturer
    try
     {
               SqlConnection sqlconnection = new SqlConnection();
                SqlCommand sqlcommand = new SqlCommand();
                sqlconnection.ConnectionString = connstring;
                sqlconnection.Open();
                sqlcommand.Connection = sqlconnection;
                sqlcommand.CommandType = CommandType.Text;
                sqlcommand.CommandText = sql;
                sqlcommand.ExecuteNonQuery();
                sqlconnection.Close();
                sqlcommand.Dispose();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
}

When I built that code, program jump inside catch statement and return false???.

Try to debug, it has an error: $exception:{"Invalid column name 'lecturer'."} at line sqlcommand.ExecuteNonQuery();.

Help!!! Why the code doesnt work even it works fine in SQL SERVER???

Consider a using for the SqlConnection and SqlCommand objects. ie, using (var sqlConnection = new ...) — Austin Salonen
– Austin Salonen, Commented Nov 8, 2013 at 17:45

Jon Skeet · Accepted Answer · 2013-11-08 17:40:16Z

9

I suspect the value of userdeleted is just lecturer rather than 'lecturer'. Therefore your SQL statement is:

DELETE FROM COMMENT WHERE USERNAME=lecturer

That's the immediate problem - it's looking for a column called lecturer. If you use that SQL in SQL Server Studio (or wherever you were testing before) you'll get the same error.

However, you shouldn't just add quotes - you should use parameterized SQL:

string sql="DELETE FROM COMMENT WHERE USERNAME=@username";
...

sqlcommand.Parameters.Add("@username", SqlDbType.NVarChar).Value = userdeleted;

Parameterized SQL is important in a number of ways:

It helps to prevent SQL injection attacks
It keeps the SQL clearer (you don't end up with a load of concatenation, and fixing up quoting issues like this)
It avoids conversion issues, particularly for date/time values

answered Nov 8, 2013 at 17:40

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Perrenoud Over a year ago

Haha, got in there before me. +1 my friend!

Collectives™ on Stack Overflow

Error sqlcommand.ExecuteNonQuery()

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related