Delete a row from a SQL Server table

Question

I'm trying to simply delete a full row from my SQL Server database table using a button event. So far none of my attempts have succeeded. This is what I'm trying to do:

public static void deleteRow(string table, string columnName, string IDNumber)
{
    try
    {
    using (SqlConnection con = new SqlConnection(Global.connectionString))
    {
         con.Open();
         using (SqlCommand command = new SqlCommand("DELETE FROM " + table + " WHERE " + columnName + " = " + IDNumber, con))
         {
               command.ExecuteNonQuery();
         }
         con.Close();
    }
    }
    catch (SystemException ex)
       {
       MessageBox.Show(string.Format("An error occurred: {0}", ex.Message));
       }
    }
}

I keep receiving the error:

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll An error occurred: Operand type clash: text is incompatible with int

All of the columns in the table are of TEXT type. Why cannot I compare the function argument of type string to the columns to find the match? (And then delete the row?)

I'm not sure how to use parameters, any examples? Resources? — ikathegreat, Mar 06 '12 at 04:24
Also, as a side-note: first of all, you shouldn't use `TEXT` anymore - as of SQL Server 2005 it's deprecated. Use `VARCHAR(MAX)` instead. Also: having **all columns** be `VARCHAR(MAX)` smells like a terrible design - do you **REALLY** need 2 GB of text for **each column**?? **REALLY!?!?!?** Design and performance tip: use appropriate data types — marc_s, Mar 06 '12 at 05:50

score 20 · Accepted Answer · edited Nov 05 '15 at 04:45

As you have stated that all column names are of TEXT type, So, there is need to use IDNumber as Text by using single quote around IDNumber.....

    public static void deleteRow(string table, string columnName, string IDNumber)
    {
    try
    {
    using (SqlConnection con = new SqlConnection(Global.connectionString))
    {
         con.Open();
         using (SqlCommand command = new SqlCommand("DELETE FROM " + table + " WHERE " + columnName + " = '" + IDNumber+"'", con))
         {
               command.ExecuteNonQuery();
         }
         con.Close();
    }
    }
    catch (SystemException ex)
       {
       MessageBox.Show(string.Format("An error occurred: {0}", ex.Message));
       }
    }
 }

You should ALWAYS use parameterised queries for this type of operation. — Halden Collier, Oct 14 '21 at 15:17

score 6 · Answer 2 · answered Mar 06 '12 at 03:09

6

Either IDNumber should be an int instead of a string, or if it's really a string, add quotes.

Better yet, use parameters.

answered Mar 06 '12 at 03:09

Jon B

51,025
31
133
161

using regular quotes (") i get this back: `An error occurred: Invalid column name '50012'.` where 50012 was the IDNumber (string). – ikathegreat Mar 06 '12 at 04:23
@ikathegreat - try kashif's example. Using parameters avoids the hassle of mucking with data types like this. Also, as other posters said, you almost certainly don't want all of these fields to be TEXT. – Jon B Mar 06 '12 at 11:42

Ali · Answer 3 · 2012-03-06T04:59:42.407

Try with paramter

.....................
.....................

    using (SqlCommand command = new SqlCommand("DELETE FROM " + table + " WHERE " + columnName + " = " + @IDNumber, con))
             {
                   command.Paramter.Add("@IDNumber",IDNumber)
                   command.ExecuteNonQuery();
             }

.....................
.....................

No need to close connection in using statement

score 1 · Answer 4 · answered Mar 06 '12 at 04:11

1

Looks like IDNumber is a string. It needs single quote wrapped around it.

"DELETE FROM " + table + " WHERE " + columnName + " = '" + IDNumber + "'"

answered Mar 06 '12 at 04:11

sam yi

4,806
1
29
40

this returns: `An error occurred: The data types text and varchar are incompatible in the equal to operator.` – ikathegreat Mar 06 '12 at 04:21

score 1 · Answer 5 · edited May 23 '17 at 11:54

1

You may change the "columnName" type from TEXT to VARCHAR(MAX). TEXT column can't be used with "=".
see this topic

edited May 23 '17 at 11:54

Community

1
1

answered Mar 06 '12 at 04:43

lmatt

197
4

score 0 · Answer 6 · answered Nov 07 '15 at 09:15

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 suppliers";

    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 7 · answered Jan 17 '19 at 06:23

If you are using MySql Wamp. This code work.

string con="SERVER=localhost; user id=root; password=; database=dbname";
public void delete()
{
try
{
MySqlConnection connect = new MySqlConnection(con);
MySqlDataAdapter da = new MySqlDataAdapter();
connect.Open();
da.DeleteCommand = new MySqlCommand("DELETE FROM table WHERE ID='" + ID.Text + "'", connect);
da.DeleteCommand.ExecuteNonQuery();

MessageBox.Show("Successfully Deleted");
}
catch(Exception e)
{
MessageBox.Show(e.Message);
}
}

score 0 · Answer 8 · edited Oct 01 '19 at 16:25

private void DeleteProductButton_Click(object sender, EventArgs e)
{


    string ProductID = deleteProductButton.Text;
    if (string.IsNullOrEmpty(ProductID))
    {
        MessageBox.Show("Please enter valid ProductID");
        deleteProductButton.Focus();
    }
    try
    {
        string SelectDelete = "Delete from Products where ProductID=" + deleteProductButton.Text;
        SqlCommand command = new SqlCommand(SelectDelete, Conn);
        command.CommandType = CommandType.Text;
        command.CommandTimeout = 15;

        DialogResult comfirmDelete = MessageBox.Show("Are you sure you want to delete this record?");
        if (comfirmDelete == DialogResult.No)
        {
            return;
        }
    }
    catch (Exception Ex)
    {
        MessageBox.Show(Ex.Message);

    }
}

What does your answer improve upon the accepted answer on this 7 year old question? — skolldev, Oct 01 '19 at 15:33

Delete a row from a SQL Server table

8 Answers8