C# 从 SQL Server 表中删除一行

Delete a row from a SQL Server table

提问by ikathegreat

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:

我正在尝试使用按钮事件从我的 SQL Server 数据库表中简单地删除一整行。到目前为止，我的尝试都没有成功。这就是我想要做的：

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

System.Data.dll 中发生类型为“System.Data.SqlClient.SqlException”的第一次机会异常发生错误：操作数类型冲突：文本与 int 不兼容

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

表中的所有列都是TEXT类型。为什么我不能将类型的函数参数string与列进行比较以找到匹配项？（然后删除该行？）

采纳答案by Ali

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

正如您所说，所有列名都是 TEXT 类型，因此，需要通过在 IDNumber 周围使用单引号将 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));
       }
    }
 }

回答by Jon B

Either IDNumbershould be an intinstead of a string, or if it's really a string, add quotes.

要么IDNumber应该是 anint而不是 a string，或者如果它真的是 a string，请添加引号。

Better yet, use parameters.

更好的是，使用参数。

回答by sam yi

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

看起来 IDNumber 是一个字符串。它需要用单引号括起来。

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

回答by lmatt

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

您可以将“columnName”类型从 更改TEXT为VARCHAR(MAX). TEXTcolumn can't be used with "="。
看到这个话题

回答by Ali

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

无需在 using 语句中关闭连接

回答by robin

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);
    }
}

回答by Miguel

If you are using MySql Wamp. This code work.

如果您使用的是 MySql Wamp。此代码有效。

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);
}
}

回答by Barbi Angel Tupaz

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);

    }
}