Delete data in sql table using c# and present it in gridview

Question

I have the following method:

   Connection c =new Connection();
    SqlCommand cmd = new SqlCommand();
    String empID = toolStripTextBox1.Text;
    cmd.CommandText = "DELETE FROM tblEmployee WHERE employeeNumber='empID'";
    cmd.Connection = c.con;
    c.con.Open();
    dataGridView1.CurrentCell = null;
    int numberDeleted = cmd.ExecuteNonQuery();
    MessageBox.Show(numberDeleted.ToString() + " employees were deleted.<br>");
    c.con.Close();

I'm trying to delete a record in the table and update gridview to show the remaining records in the table. the code doesn't delete any record

Icarus · Accepted Answer · 2013-08-03 16:02:54Z

4

This:

DELETE FROM tblEmployee WHERE employeeNumber='empID'

Should be converted to use a parametrized query in the following way:

cmd.CommandText="DELETE FROM tblEmployee WHERE employeeNumber=@id";

The reason is that your previous statement was simply concatenating the delete from ... part with the string 'empid' which I assume is not what you want. You are after something like this:

delete from ... where employeeNumber={some_number} where some_number is a parameter

In order to pass a parameter to your SQL Statement you do this:

cmd.Parameters.AddWithValue("@id",empID);//I assume empID is a variable containing an ID
cmd.ExecuteNonQuery();

If you want the change immediately reflected on your grid, you need to select the data again and rebind your grid.

edited Aug 3, 2013 at 16:02

answered Aug 3, 2013 at 15:54

Icarus

64k14 gold badges102 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Michelle Over a year ago

This answer would be better with a quick explanation of what's going on in the suggested code.

Icarus Over a year ago

@Michelle will add more details.

user2023203 Over a year ago

Thank's it workes, But can you also tell me how to update my gridview ?

Icarus Over a year ago

@user2023203 how do you bind the data to your gridview? Show the code and I'll be able to help further.

user2023203 Over a year ago

@Icarus, I'm not sure how to be honest. what code are you looking for?

|

DaMachk · Accepted Answer · 2013-08-03 16:17:38Z

-1

The problem is that probably no empID with the value 'empID' exists..

You didn't put the value of the empID in the command, but added an exact string of 'empID', which cannot be found in your table. Put the empID value as a parameter like Icarus advised.

edited Aug 3, 2013 at 16:17

answered Aug 3, 2013 at 15:54

DaMachk

6434 silver badges10 bronze badges

5 Comments

Michelle Over a year ago

You should never concatenate a user-supplied value in an SQL query. This opens you up to SQL injection attacks. The correct way to include values in a query is to add a parameter.

DaMachk Over a year ago

Michelle i know that, but adding a parameter in an SQLCOmmand is still a type of concatenation. I was only pointing out that he was searching for an id with a value of 'empID'.

Michelle Over a year ago

It's not "a type of concatenation". Google "C sharp string concatenation" and this page, accurately describing string concatenation - the exact thing OP shouldn't do, is the first result. "Concatenate the command text with the value" is absolutely the wrong way to describe the solution to this problem.

DaMachk Over a year ago

Well pardon my english, i will change the anwser to improve the correct meaning so noone else needs to stuble upon the exact meaning. My point was different of what you're so eager to stuble at.

Michelle Over a year ago

If your answer is "do what the other answer says," it's probably best to just delete it.

Collectives™ on Stack Overflow

Delete data in sql table using c# and present it in gridview

2 Answers 2

8 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related