Save and Retrieve CheckBoxList values as per User Roles in ASP.Net

Last Reply on Feb 26, 2014 11:03 PM By Azim

Posted on Feb 26, 2014 07:14 AM

Hello everyone i need a script on ASP.net that can build this form.

1.Users === Drop Down Menu then in the Drop down should be the names of all Taafoo staff.


 2. when i choose from the Drop down list lets say Andrew ...once i select andrew it should display all the roles in Taafoo for example (Uploading, IT, Inventory, Sales etc)  in form of a check Box or Option Button. then the roles assign to me will be checked while those not assign wont be checked.
Then lastly an UPDATE button that when Andrew is been moved to sales ,,,since sales was not checked and i later checked ite an and clicked Update it should updat
e and save.


Thats all

Thanks

Posted on Feb 26, 2014 11:03 PM

This Way:

Ref:

 
HTML:
<div>
        <asp:DropDownList ID="ddlUsers" runat="server" >
            <asp:ListItem Text="Azim" />
            <asp:ListItem Text="Andrew" />
            <asp:ListItem Text="Sabah" />
        </asp:DropDownList>
        Hobbies:
        <asp:CheckBoxList ID="chkHobbies" runat="server">
        </asp:CheckBoxList>
        <br />
        <asp:Button ID="btnUpdate" runat="server" Text="Button" OnClick="UpdateHobbies" />
    </div>

C#:

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            this.PopulateHobbies();
        }
    }

    private void PopulateHobbies()
    {
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = ConfigurationManager
                    .ConnectionStrings["constr"].ConnectionString;
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "select * from hobbies";
                cmd.Connection = conn;
                conn.Open();
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        ListItem item = new ListItem();
                        item.Text = sdr["Hobby"].ToString();
                        item.Value = sdr["HobbyId"].ToString();
                        item.Selected = Convert.ToBoolean(sdr["IsSelected"]);
                        chkHobbies.Items.Add(item);
                    }
                }
                conn.Close();
            }
        }
    }

    private void Delete()
    {
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = ConfigurationManager
                    .ConnectionStrings["constr"].ConnectionString;
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "Delete from UserHobby Where UserName = @UserName";
                cmd.Connection = conn;
                conn.Open();
                foreach (ListItem item in chkHobbies.Items)
                {
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@UserName", ddlUsers.SelectedItem.Text);
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }
    }


    protected void UpdateHobbies(object sender, EventArgs e)
    {
        this.Delete();
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = ConfigurationManager
                    .ConnectionStrings["constr"].ConnectionString;
            using (SqlCommand cmd = new SqlCommand("ManageHobbies", conn))
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                conn.Open();
                foreach (ListItem item in chkHobbies.Items)
                {
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@Name", ddlUsers.SelectedItem.Text);
                    cmd.Parameters.AddWithValue("@IsSelected", item.Selected);
                    cmd.Parameters.AddWithValue("@HobbyId", item.Value);
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }
    }

SP:

ALTER PROCEDURE ManageHobbies
	@Name VARCHAR(50)
	,@HobbyId INT
	,@IsSelected BIT
AS
BEGIN	
	SET NOCOUNT ON;

	INSERT INTO UserHobby (UserName,HobbyId,IsSelected) VALUES(@Name,@HobbyId,@IsSelected)
END
GO

 

CREATE TABLE [dbo].[UserHobby](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [varchar](50) NOT NULL,
	[HobbyId] [int] NOT NULL,
	[IsSelected] [bit] NOT NULL,
 CONSTRAINT [PK_UserHobby] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Thank You.