checkListBox selectedindex and sql mapping

Question

I have a combo box and a checkListbox in my windows form application. In my combo box, I mapped my items to my sql table using this query and displayed it to the my combo box

select * from job_table

SQL table

job_detail

    --------------------
    jobId   |   jobName
    -------------------
    1       |   McDonald           
    2       |   Buger King
    3       |   Security Officer
    4       |   IT Manager
    5       |   Teacher

code:

 string query = "select jobId, jobName from job_detail";
                SqlDataAdapter da = new SqlDataAdapter();
                myConn.Open();
                DataTable dt = new DataTable();

                SqlCommand command = new SqlCommand(query, myConn);

                SqlDataReader reader = command.ExecuteReader();

                dt.Load(reader);

                DataRow dr;
                dr= dt.NewRow();
                dr.ItemArray = new object[] { 0, "<----------Select a job -------> " };
                dt.Rows.InsertAt(dr, 0);

                applicationComboBox.DataSource = dt;
                applicationComboBox.ValueMember = "jobId";
                applicationComboBox.DisplayMember = "jobName";

After the comboBox binding, I call a method call fill_workerCheckList to display a list of worker who is currently on that job.

    -----------------------------
    workerId   |   workerName   |             
    -----------------------------
    1          |   Amy          |
    2          |   Jim          |
    3          |   Peter        |
    4          |   Pam          |
    5          |   David        |
    6          |   Sara         |
    7          |   Laura        |
    8          |   Bob          |

I joined the two tables to get their relationship: 1-N: 1 worker can have many job using this join query:

select * from worker_detail as wd
        LEFT JOIN job_detail as jd
        ON wd.jobId = jd.jobId


    -----------------------------------------
    workerId   |   workerName   |  jobId           
    ------------------------------------------
    1          |   Amy          |   1
    2          |   Jim          |   1
    3          |   Peter        |   2
    4          |   Pam          |   5
    5          |   David        |   3
    6          |   Sara         |   2
    7          |   Laura        |   4
    8          |   Bob          |   4

To display the workerName to a checkListBox, I was thinking to filter by comboBox.selectedIndex, but this is not a good idea because what happen if 1 job was cut, than all the record will be messed up

This is the query that I thought of using SQL injection

select * from worker_detail as wd
        LEFT JOIN job_detail as jd
        ON wd.jobId = jd.jobId where wd.jobId = '"+comboBox.SelectedIndex+"'

My original code:

            SqlCommand myCommand = new SqlCommand(query, myConn);

            SqlDataReader dr = myCommand.ExecuteReader();

            comboBox.Items.Clear();

            string s = "Select All";
            comboBox.Items.Add(s);  

            while (dr.Read())
            {
                string workerName = dr.IsDBNull(1) ? string.Empty : dr.GetString(1);
                checkListBox.Items.Add(name);
            }

You can immediately see the problem here. What if the job somehow get cut? then the jobId is gone and the comboBox index won't work. What I want to know is is there a better way to map by the workerId instead of the index?

I assume this time you are confused with the tables. After reading this, I could figure out that you need three tables. Out of 3, your two tables will be primary table viz. (Job and worker) and third table will be transaction table (JobWorkersDetail).. Transaction table will have only two columns i.e. JobID, WorkerID .. let me know if you are on similar lines.. — Amnesh Goel
– Amnesh Goel, Commented Sep 30, 2015 at 6:14

Amnesh Goel · Accepted Answer · 2015-09-30 06:36:42Z

To understand the table flow, you may check this SQLFiddle. This will help you to understand Primary and Transaction table.

And your main problem is you don't understand the different between SelectedIndex and SelectedValue property of your control. Well you can read whole difference here, however for your reference let me add important content as an answer.

SelectedIndex

SelectedIndex property always returns the index of selected item in the control. Index always starts from 0. The first item in the control has 0 index and second item has 1 index and so on.

SelectedValue

Well, this is the property with which people are confused the most. Most of them thinks that SelectedValue property returns the text of the item we have selected. Yes it returns the text BUT only when you have not specified the value separately. The value in these controls is something which is not shown to the user. Let's take an example, you are adding items into ListBox1 dynamically like-

ListBox1.Items.Add("iknowledgeboy");

When you do like this then the text and value of this item becomes "iknowledgeboy". So, when you say-

ListBox1.SelectValue;

then you get "iknowledgeboy" for sure. BUT when you are binding your ListBox1 with Database then you need to specify DataTextField and which is DataValueField of ListBox1. At that time, the value of each item in ListBox1 or Dropdown or CheckBoxList or RadioButtonList is usually integer. So, then if you say-

ListBox1.SelectedValue;

then you won't get the text that you are seeing rather you will get that integer value.

Hope this is a help for you..

Collectives™ on Stack Overflow

checkListBox selectedindex and sql mapping

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related