How can I delete a single entry from a table

Question

I have a listbox with usernames, and a remove button I want the selected a user (with all entered data associated with that user) to be deleted when the remove button is clicked.

My code

SqlConnection con = new SqlConnection("Data Source=JAMES-PC\\SQLEXPRESS;Initial Catalog=staff;Integrated Security=True");
con.Open();

string sql = @"DELETE FROM staff1;";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.ExecuteNonQuery();
con.Close();

this code deletes the whole table.

How can I just delete the selected user?

score 2 · Accepted Answer · edited May 23 '17 at 10:26

2

You need a WHERE clause to select the required record. You have to get the username of the selected user to be deleted and pass it to @UserName parameter.

 var userName = (listBox.SelectedItem as DataRowView)["UserName"].ToString();
 string sql = @"DELETE FROM staff1 WHERE Username = @UserName;";

 SqlCommand cmd = new SqlCommand(sql, con);
 cmd.Parameters.AddWithValue("@UserName",useName);

 cmd.ExecuteNonQuery();
 con.Close();

See this thread on how to use parameters in the SQL.

edited May 23 '17 at 10:26

Community

1
1

answered Aug 05 '15 at 11:10

CharithJ

46,289
20
116
131

DELETE FROM staff1 WHERE Username =@UserName if i use this what should i put in the @ username? the column name? – jamesmc1535 Aug 05 '15 at 12:52
@jamesmc1535: See my update. You have to get the username of the selected user to be deleted and pass it to UserName parameter. – CharithJ Aug 05 '15 at 23:15
thanks man , so i copied the updated >sorry if im asking stupid questions , i hate sql connections it confuses me, lol anyways in the listbox the table name is (name) so do i replace @UserName with name ? and if i do that i keep on getting error on debug/ object reference not set to an instance of an object – jamesmc1535 Aug 06 '15 at 07:33
@jamesmc1535: you have to get the username of the selected listbox item. What's the field name of the listbox's username column? Show how you fill listbox items. – CharithJ Aug 06 '15 at 09:04

Zippy · Answer 2 · 2015-08-06T07:53:36.353

1

When you execute a delete query, in order to delete only 1 row from the table you need to add a WHERE clause.

Based on the comments the workflow should be something like: you click on a delete button, you send the name of the staff you want to delete to the command, which looks like:

 string sql = @"DELETE FROM staff1 where Name=@Name;";

 SqlCommand cmd = new SqlCommand(sql, con);
 cmd.Parameters.AddWithValue("@Name","NameReceivedFromList");

 cmd.ExecuteNonQuery();
 con.Close();

edited Aug 06 '15 at 07:53

answered Aug 05 '15 at 11:12

Zippy

1,804
5
27
36

i used this one but then it does nothing so im thinking i did something wrong string sql = @"DELETE FROM staff1 where Id=@Id;"; – jamesmc1535 Aug 05 '15 at 12:53
You can change the Id with the column on which you want to delete (preferable an unique column in order to avoid deleting more rows). – Zippy Aug 05 '15 at 13:21
'SqlConnection con = new SqlConnection("Data Source=JAMES-PC\\SQLEXPRESS;Initial Catalog=staff;Integrated Security=True"); con.Open(); string sql = @"DELETE FROM staff1 where Id=@name;"; SqlCommand cmd = new SqlCommand(sql, con); cmd.Parameters.AddWithValue("@name", 1); cmd.ExecuteNonQuery(); con.Close();' } did this but when i click the button nothing happens so i mightve done something wrong :s sorry i dont know how to post a code so its in its own square thingy – jamesmc1535 Aug 06 '15 at 07:38
Can you edit your question and add the columns from table staff1 ? – Zippy Aug 06 '15 at 07:41
cant edit it . the columns are/ name / surname / homead / location / position / cellnum / email / i have a form with textboxes next to that, the listbox displays the names and when u select a "staff" member iit will display all his information in the textboxes, but that i can do , im just struggling to delete the selected user from the listbox, – jamesmc1535 Aug 06 '15 at 07:47
Edited answer with more info. For more info on how to edit questions & more you can check [this](http://stackoverflow.com/tour) – Zippy Aug 06 '15 at 07:54
thanks! all column names >/ name / surname / homead / location / position / cellnum / email listbox uses (name) – jamesmc1535 Aug 06 '15 at 08:28

score 0 · Answer 3 · edited Aug 05 '15 at 13:39

When you are deleting you should remember to add a Where clause, it is actually very powerfull here is some examples that will get you started

The following query will delete only one element

DELETE FROM Table WHERE Table.PrimaryField = Value

The following query will delete all items that matches

DELETE FROM Table WHERE Table.Field = Value

You can also have a join in your delete statement for more complex delete queries

DELETE A FROM Table A 
INNER JOIN TableB B ON A.Key = B.Key
WHERE B.PrimaryField = Value

How can I delete a single entry from a table

3 Answers3