Delete Selected Item From ListBox And Database In ASP.NET

This article explains the following:

• How to bind the ListBox from the database.
• How to delete selected item from ListBox as well as in the database.

Database Chamber

Use the following table and Stored Procedure to demonstrate this concept.

Create table Mas_Employee
(
    Id int primary key identity(1,1),
    Name varchar(50)
)

--To get all the employees
Create Procedure USP_Select_Mas_Employee
@Id int = null
AS
Begin
    Select E.Id, E.Name
    From Mas_Employee E
    Where Id = Isnull(@Id, Id)
End

--To Insert employee details
Create Procedure USP_Insert_Mas_Employee
@Name varchar(50)
AS
Begin
    Insert into Mas_Employee(Name) Values(@Name)
End

--To Delete employee details
Create Procedure USP_Delete_Mas_Employee
@Id int
AS
Begin
    Delete From Mas_Employee
    where Id=@Id
End

Application Chamber

To create the project -

• Go to Start, then All Programs and click Microsoft Visual Studio 2010.
• Go to File, New, Project..., Visual C# , Web. Then select ASP.NET Empty Web Application.
• Provide the project a name and specify the location.

Web.Config:

Create the connection string in the Web.Config file as in the following code snippet:

<connectionStrings>
   <add name="conStr"
   connectionString="Password= 1234; User ID=sa; Database=DB_CsharpCorner; Data Source=."
   providerName="System.Data.SqlClient"/>
</connectionStrings>

Next: Right-click on Solution Explorer and add a web form to your project.

Webform Design:

Design you Webform (.aspx page) as in the following:

<form id="form1" runat="server">
        <div style="width: 100%;" align="center">
            <fieldset style="width: 40%;">
                <legend>Delete Selected item from ListBox and Database</legend>
                <table style="width: 100%;">
                    <tr>
                        <td>
                            Name:
                        </td>
                        <td>
                            <asp:TextBox ID="txtEmpName" runat="server"></asp:TextBox>
                        </td>
                        <td>
                            <asp:Button ID="btnAdd" runat="server" Text="Add" OnClick="btnAdd_Click" />
                        </td>
                    </tr>
                    <tr>
                        <td colspan="3" style="text-align:center;">
                            <asp:Label ID="lblMsg" runat="server"></asp:Label>
                        </td>
                    </tr>
                    <tr>

                        <td colspan="2">
                            <asp:ListBox ID="lstEmployee" runat="server"></asp:ListBox>
                        </td>
                        <td>
                            <asp:Button ID="btnDelete" runat="server" Text="Delete Selected Item" OnClick="btnDelete_Click" />
                        </td>
                    </tr>
                </table>
            </fieldset>
        </div>
    </form>

CodeBehind:

Add the following namespaces:

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Drawing;

Invoke the ConnectionString from Web.Config as in the following:

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);

User Defined Functions:

#region User Defined Methods

//To Clear the fields
private void Clear()
{
    txtEmpName.Text = string.Empty;
    lblMsg.Text = string.Empty;
}

//To bind listBox from Database
private void BindListBox()
{
    SqlDataAdapter adp = new SqlDataAdapter("USP_Select_Mas_Employee", con);
    adp.SelectCommand.CommandType = CommandType.StoredProcedure;
    DataSet ds = new DataSet();
    adp.Fill(ds);
    if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
    {
        lstEmployee.DataSource = ds;
        lstEmployee.DataValueField = "Id";
        lstEmployee.DataTextField = "Name";
        lstEmployee.DataBind();
    }
}

//To delete the selected item from Database
private void DeleteSelectedItem(int Id)
{
    SqlCommand cmd = new SqlCommand("USP_Delete_Mas_Employee", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@Id", Id);
    if (con.State == ConnectionState.Closed)
    {
        con.Open();
    }
    int result = cmd.ExecuteNonQuery();
    if (result > 0)
    {
        Clear();
        //BindListBox();
        lblMsg.Text = "Employee deleted successfully";
        lblMsg.ForeColor = Color.Green;
    }
}

//To add the employee from textbox to listBox
private void AddEmployee(string Name)
{
    SqlCommand cmd = new SqlCommand("USP_Insert_Mas_Employee", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@Name", Name);
    if (con.State == ConnectionState.Closed)
    {
        con.Open();
    }
    int result = cmd.ExecuteNonQuery();
    if (result > 0)
    {
        Clear();
        BindListBox();
        lblMsg.Text = "Employee added successfully";
        lblMsg.ForeColor = Color.Green;
    }
}

#endregion

Page Event Handlers:

#region Page Event Handlers

protected void Page_Load(object sender, EventArgs e)
{
    lblMsg.Text = string.Empty;
    if (!Page.IsPostBack)
    {
        BindListBox();
    }
}

protected void btnAdd_Click(object sender, EventArgs e)
{
    if (txtEmpName.Text.ToString() != string.Empty || txtEmpName.Text.ToString() != null)
        AddEmployee(txtEmpName.Text.ToString());
    else
    {
        lblMsg.Text = "Please provide the Name";
        lblMsg.ForeColor = Color.Red;
        return;
    }
}

protected void btnDelete_Click(object sender, EventArgs e)
{
    if (Convert.ToInt32(lstEmployee.SelectedValue) < 0)
    {
        lblMsg.Text = "Please select an item";
        lblMsg.ForeColor = Color.Red;
        return;
    }
    else
    {
        for (int i = lstEmployee.Items.Count - 1; i >= 0; i--)
        {
            if (lstEmployee.Items[i].Selected)
            {
                DeleteSelectedItem(Convert.ToInt32(lstEmployee.Items[i].Value));
                lstEmployee.Items.Remove(lstEmployee.Items[i]);
            }
        }
    }
}
#endregion

Output:

Enter the name and click on Add.



Then the output will be as in the following screenshot:



Select the ListBox Item and click Delete Selected Item button then it will be Deleted in ListBox as well as in the database.



I hope you enjoyed this article. Please provide your valuable suggestions and feedback to make this article much reliable.