bind Listbox in asp.net

Hi Experts,
I am binding a ListBox using below code,
 private void FillRole()
        {
            SkillUpdateRight skUpdateRights = new SkillUpdateRight();
            Dictionary<object, object> dictRole = skUpdateRights.GetAllRole();
            lstRole.DataSource = dictRole;
            lstRole.DataTextField = "Value";
            lstRole.DataValueField = "Key";
            lstRole.DataBind();
      }


skUpdateRights.GetAllRole()

 public Dictionary<object, object> GetAllRole()
        {
            Dictionary<object, object> DictRole = new Dictionary<object, object>();
            //IDataReader dr = DBDataHandler.ExecuteReader(strCon, WDProcedures.GET_ALL_RESOURCES, null);

            IDataReader dr = DBDataHandler.ExecuteReader(strCon, WDProcedures.GET_ROLESFORSKILLRIGHTS, null);
            while (dr.Read())
            {
                if (DictRole == null)
                {
                    DictRole = new Dictionary<object, object>();
                    DictRole.Add(dr["RoleId"].ToString(), dr["RoleName"].ToString());
                }
                else
                {
                    if (!DictRole.ContainsKey(dr["RoleId"].ToString()))
                    {
                        DictRole.Add(dr["RoleId"].ToString(), dr["RoleName"].ToString());
                    }
                }
            }
            dr.Close();

            return DictRole;
        }



Now I have a sql ,

Select RoleId, RoleName, 'false' AS RoleSelected from Role
UNION ALL
Select RoleId, RoleName, 'true' AS RoleSelected from Role
WHERE RoleID IN
(Select lr.RoleId from SkillUpdateRights lr
inner join Role r on r.RoleId = lr.RoleId)

I want to check the RoleSelected is true or false and accordingly bind the ListBox.
If the RoleSelected is true,I will set the item in the list as Selected otherwise it will just appear in the Listbox.

How can I acheive this functionality.
LVL 8
Johny BravoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alfred A.Commented:
Hi,

In your SQL, you could create a third column "IsRoleSelected" as a bit data type for example and use the value of this column (0 or 1) to determine what to do each row of your Role table.
0
Alfred A.Commented:
Hi,

Additional comment, your original SQL would generate at least two duplicate role IDs and role name but with either "true" or "false" as the third column because of the UNION ALL.

For example,

RoleID  RoleName  
  1          TestRole     'true'
  1          TestRole     'false'

Is this sort of the SQL result you are after?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Johny BravoAuthor Commented:
Thanks for the reply.
I want unique RoleID,Rolename.
If it is selected ,them true otherwise false.

Only true or false,not both
RoleID  RoleName  
  1          TestRole     'true'

One more problem is that,how to bind this to Listbox depending on the True,false.
Currently I found one way to set the selected value in the Listbox.It directly set the selected property as true.
Which I want dynamic.I mean accrding to value the query returns (true/false)

 SkillUpdateRight skUpdateRights = new SkillUpdateRight();
            Dictionary<object, object> dictRoleSelected = skUpdateRights.GetSelectedRole();
            foreach (KeyValuePair<object, object> kvp in dictRoleSelected)
            {
                ListItem li = new ListItem(kvp.Value.ToString(), kvp.Key.ToString());
                li.Selected = true;
                lstRole.Items.Add(li);
            }
0
Johny BravoAuthor Commented:
Thanks for the suggestion
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.