SQL delete command?

Question

I am having trouble with a simple DELETE statement in SQL with unexpected results , it seems to add the word to the list??. Must be something silly!. but i cannot see it , tried it a few different ways. All the same result so quite confused.

public void IncludeWord(string word)
{
    // Add selected word to exclude list
    SqlConnection conn = new SqlConnection();
    String ConnectionString = "Data Source = dev\\SQLEXPRESS ;" + "Initial Catalog=sml;" + "User id=** ;" + "Password =*;" + "Trusted_Connection=No";

    using (SqlConnection sc = new SqlConnection(ConnectionString))
    {
        try
        {
            sc.Open();

            SqlCommand Command = new SqlCommand(
               "DELETE FROM excludes WHERE word='@word'" +
                 conn);


           Command.Parameters.AddWithValue("@word", word);  
            Command.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            Box.Text = "SQL error" + e;
        }
        finally
        {
           sc.Close();
        }
        ExcludeTxtbox.Text = "";

       Box.Text = " Word : " + word + " has been removed from the Exclude List";

        ExcludeLstBox.AppendDataBoundItems = false;
        ExcludeLstBox.DataBind();
    }

That code will not add an entry rather than delete it. Your issue is elsewhere. Please post any relative bits that are called in junction with this method. — Matthew Cox, Jun 20 '11 at 15:52

Darin Dimitrov · Accepted Answer · 2011-06-20T15:58:43.480

Try removing the single quotes. Also why are you concatenating your SQL string with a connection object (.. word='@word'" + conn)???

Try like this:

try
{
    using (var sc = new SqlConnection(ConnectionString))
    using (var cmd = sc.CreateCommand())
    {
        sc.Open();
        cmd.CommandText = "DELETE FROM excludes WHERE word = @word";
        cmd.Parameters.AddWithValue("@word", word);  
        cmd.ExecuteNonQuery();
    }
}
catch (Exception e)
{
    Box.Text = "SQL error" + e;
}
...

Notice also that because the connection is wrapped in a using block you don't need to Close it in a finally statement. The Dispose method will automatically call the .Close method which will return the connection to the ADO.NET connection pool so that it can be reused.

Another remark is that this IncludeWord method does far to many things. It sends SQL queries to delete records, it updates some textboxes on the GUI and it binds some lists => methods like this should be split in separate so that each method has its own specific responsibility. Otherwise this code is simply a nightmare in terms of maintenance. I would very strongly recommend you to write methods that do only a single specific task, otherwise the code quickly becomes a complete mess.

Thanks i went back around all my code and found it branching off from a function I never wish to invoke. I re-wrote the delete function as well and its all working!. thanks for the tips. — user685590, Jun 21 '11 at 09:08

score 3 · Answer 2 · answered Jun 20 '11 at 15:52

SqlCommand Command = new SqlCommand(
                   "DELETE FROM excludes WHERE word='@word'" +
                     conn);

should be replaced with

SqlCommand Command = new SqlCommand(
                   "DELETE FROM excludes WHERE word='@word'",
                     conn);

Also try by removing single quotes as suggested by others like this

SqlCommand Command = new SqlCommand(
                   "DELETE FROM excludes WHERE word=@word",
                     conn);

score 1 · Answer 3 · answered Jun 20 '11 at 15:52

1

The @Word should not be in quotes in the sql query.

Not sure why you're trying to add the connection on the end of the sql query either.

answered Jun 20 '11 at 15:52

George Duckett

31,770
9
95
162

score 0 · Answer 4 · edited Sep 26 '19 at 15:35

See the code below:

private void button4_Click(object sender, EventArgs e)
        {
            String st = "DELETE FROM supplier WHERE supplier_id =" + textBox1.Text;

        SqlCommand sqlcom = new SqlCommand(st, myConnection);
        try
        {
            sqlcom.ExecuteNonQuery();
            MessageBox.Show("delete successful");
        }
        catch (SqlException ex)
        {
            MessageBox.Show(ex.Message);
        }
    }


    private void button6_Click(object sender, EventArgs e)
    {
        String st = "SELECT * FROM supplier";

        SqlCommand sqlcom = new SqlCommand(st, myConnection);
        try
        {
            sqlcom.ExecuteNonQuery();
            SqlDataReader reader = sqlcom.ExecuteReader();
            DataTable datatable = new DataTable();
            datatable.Load(reader);
            dataGridView1.DataSource = datatable;
        }
        catch (SqlException ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

score 0 · Answer 5 · edited Sep 26 '19 at 15:41

See the code below:

String queryForUpdateCustomer = "UPDATE  customer SET cbalance=@txtcustomerblnc WHERE cname='" + searchLookUpEdit1.Text + "'";
            try
            {
                using (SqlCommand command = new SqlCommand(queryForUpdateCustomer, con))
                {

                command.Parameters.AddWithValue("@txtcustomerblnc", txtcustomerblnc.Text);


                con.Open();
                int result = command.ExecuteNonQuery();

                // Check Error
                if (result < 0)
                    MessageBox.Show("Error");

                MessageBox.Show("Record Update of Customer...!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
                con.Close();

                loader();
            }

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            con.Close();
        }

Fissure · Answer 6 · 2020-08-03T16:29:20.383

You can also try the following if you don't have access to some of the functionality prescribed above (due, I believe, to older versions of software):

using (var connection = _sqlDbContext.CreatSqlConnection())
{
    using (var sqlCommand = _sqlDbContext.CreateSqlCommand())
    {
        sqlCommand.Connection = connection;
        sqlCommand.CommandText = $"DELETE FROM excludes WHERE word = @word";
        sqlCommand.Parameters.Add(
                            _sqlDbContext.CreateParameterWithValue(sqlCommand, "@word", word));
        connection.Open();
        sqlCommand.ExecuteNonQuery();
    }
}
...

I'm an associate dev. Hence the "I believe" above.

score 0 · Answer 7 · answered Jun 20 '11 at 15:53

To debug this, examine the CommandText on the SqlCommand object. Before reading further, you should try this.

The issue comes with adding the single quotes around a string that is parameterized. Remove the single quotes and life is beautiful. :-)

Oh, and your conn is an object and needs a comma, not a +.

SQL delete command?

7 Answers7

Linked