Removing a row from a SQL Server table

Question

I am trying to simply delete a complete row from a SQL Server database table using a button event. So far, none of my attempts have failed. This is what I am 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 getting the error:

The first random error like "System.Data.SqlClient.SqlException" occurred in System.Data.dll. An error occurred: Operand type column: text is not compatible with int

All columns in the table are of type TEXT . Why can't I compare a function argument of type string with columns to find a match? (And then delete the line?)

Answer 1

As you stated, all column names are of type TEXT, so you need to use IDNumber as Text, using a 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)); } } } 

Answer 2

Either IDNumber must be int instead of string , or if it is really string , add quotation marks.

Better yet, use the options.

Answer 3

Try with option

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

No need to close the connection when using the operator

Answer 4

It seems that IDNumber is a string. He needs a single quote wrapped around him.

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

Answer 5

You can change the type "columnName" from a TEXT column to VARCHAR(MAX). TEXT VARCHAR(MAX). TEXT cannot be used with "=" .
see this section

Answer 6

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

Answer 7

If you are using MySql Wamp. This code works.

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

Answer 8

F ** k you and your fu *** NG SQL Server 2015, we will download the new version in 2019 !!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!

Answer 9

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