CODEWITHSUNDEEP
c#sqlsql-server
vyclarks
vyclarks

Reputation: 878

Error sqlcommand.ExecuteNonQuery()

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

Upvotes: 1

Views: 1815

Answers (1)

Jon Skeet
Jon Skeet

Reputation: 1500735

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

Upvotes: 9

Related Questions