Setting checkedlist box from sql querie

falles01

Hi, Can someone please tell me the methods to check the checkedlistbox based on my sql querie? I have this sql code. string sql = "Select e.TechnicalSkillsID from employees e where EmployeeID = '" + FirstnameText.Text.ToString() + "' and EmployeeID = '" + Lastnametext.Text.ToString() + "'"; My c# code - techSkillsCheckListBox2.GetItemText(adoDR["TechnicalSkillsID"].ToString()); another sql statement sql = "Select t.ProgLanguagesDatabase from TechnicalSkills t, employees e where e.TechnicalSkillsID = t.TechnicalSkillsID"; and then finally a for loop which should check the boxes based on techskillsID but nothing happens. for (int i = 0; i < techSkillsCheckListBox2.Items.Count; i++) { if (adoDR["TechnicalSkillsID"]) { if (techSkillsCheckListBox2.Items.Equals(adoDR["TechnicalSkillsID"].ToString())) I think all that is wrong is the 'if' statement because the debigger picks up the correct value. My manager can't even work it out and apparently they think I am the one not progressing. :sigh:

Larantz

Does the query return multiple TechnicalSkills for each employee? In that case, are you also iterating over the various instances of TechnicalSkills per user? (you're not as of what you've showed us so far). Or are you showing only one user at a time? The query should return a table consisting of rows equal to the amount of technical skills for that user, unless you store all the skill ids in one column with a seperator.

falles01 wrote:

if (adoDR["TechnicalSkillsID"])

What's this line supposed to check for?

falles01 wrote:

if (techSkillsCheckListBox2.Items.Equals(adoDR["TechnicalSkillsID"].ToString()))

And this if statement compares a Collection of CheckedListBoxItems with a string. -Larantz-

for those about to code, we salute you
http://www.itverket.no

falles01

well I actually do need multiple entries /skills in the database yes but I am not sure how to do that part either. I have created a new table and have employeeID and TechnicalSkillsID bu thats as far as I've gotten. I haven't written any code for it. Can you help in that department at all? The skills will be saved to the database by checking checkedlistboxes. I am also using dataadapters. Once I've gotten that part right, will it fix the problem of automatically checking the checkedlist box for a given employees skills? Thank you

Larantz

First of all you should decide whether you want the TechnicalSkillsID as a column in the employees table - as you have now. If you keep it that way, and you want one empolyee to be able to own several TechnicalSkills, you'll have to store several ID's with a separating value. A better approach, if you ask me, would be to remove the TechnicalSkillsID column from the employee table entirely, and rather create a TechnicalSkills table which uses the employeeID as a keyvalue. Then add one boolean column per technical skill that you want available. You could then use the column name as a the name of the CheckedListBoxItem and set the Checked property according to the value from the database. I'll write an example of the latter one: Create a query for retrieving the row from the TechnicalSkill table with a employeeID equal to the employee you want to configure skills for. Code for propagating and setting the value for CheckedListBoxItems:

        ... generate and execute the query for returning the techskills for the given employee
        DataTable techSkillsTable = myDataSet.Tables\[0\];

        //Query should return only one row, but we'll go with a foreach
        foreach(DataRow row in techSkillsTable.Rows)
        {
            foreach (DataColumn column in techSkillsTable.Columns)
            {
                string techSkillName = column.ColumnName;
                bool userHasSkill = false;

                try
                {
                    userHasSkill = Convert.ToBoolean(row\[column\]);
                }
                catch
                {
                    //Do nothing here as userHasSkill is initialized to false.
                }

                techSkillsListBox.Items.Add(techSkillName, userHasSkill);
            }
        }

Hope it can be of some help. Best regards! -Larantz-

for those about to code, we salute you
http://www.itverket.no

Please refer to the Forum Guidelines for appropriate posting.