CODEWITHSUNDEEP
c#sql
abc
abc

Reputation: 215

How to remove single item in database from a listbox?

How to remove single item in database? When I click on remove button my code removes all items that are same as the selectedItem in listbox. e.g If I choose to remove CocaCola from my listbox I want that it removes that single CocaCola item in database not all CocaCola items.

my code:

conn.Open();
SqlCommand cmd = new SqlCommand("delete from Products Where Product ='"+lstProducts.SelectedItem+"'", conn);
cmd.ExecuteNonQuery();
conn.Close();

Upvotes: 0

Views: 892

Answers (3)

Tim Schmelter
Tim Schmelter

Reputation: 460288

If it doesn't matter which rows remain you can use TOP(1):

DELETE TOP (1)
FROM  Products 
Where Product = @Product

Note that you should use sql parameters instead of string concatanation to prevent sql injection and other less serious issues.

MSDN:

When TOP is used with DELETE, the referenced rows are not arranged in any order and the ORDER BY clause can not be directly specified in this statement. If you need to use TOP to delete rows in a meaningful chronological order, you must use TOP together with an ORDER BY clause in a subselect statement. See the Examples section that follows in this topic. TOP cannot be used in a DELETE statement against partitioned views.

Acc. to the TOP(1):

For backward compatibility, the parentheses are optional in SELECT statements. We recommend that you always use parentheses for TOP in SELECT statements for consistency with its required use in INSERT, UPDATE, MERGE, and DELETE statements in which the parentheses are required.

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172618

You can try to use TOP like this:

SqlCommand cmd = new SqlCommand("delete top (1) from Products Where Product ='"+lstProducts.SelectedItem+"'", conn);
cmd.ExecuteNonQuery();

Also your code is prone to SQL Injection. You can use parameterized query to get rid of it.

Upvotes: 1

Nicolae
Nicolae

Reputation: 301

For this behavior you need to have Product column with foreign key Unique, otherwise you need to pass the ID or another unique column.

Upvotes: 1

Related Questions