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?)
This question is related to
c#
sql-server
delete-row
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);
}
}
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
Either IDNumber
should be an int
instead of a string
, or if it's really a string
, add quotes.
Better yet, use parameters.
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);
}
}
You may change the "columnName" type from TEXT
to VARCHAR(MAX). TEXT
column can't be used with "="
.
see this topic
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);
}
}
Looks like IDNumber is a string. It needs single quote wrapped around it.
"DELETE FROM " + table + " WHERE " + columnName + " = '" + IDNumber + "'"
Source: Stackoverflow.com