Question Retrieve the data rows with already selected items

[andrewmanuja]

Hi All,

I got three tables as shown below;
tbl_Product : ProdID, ProdName, Price
tbl_Department : DepartmentID, DepartmentName
tbl_Allocation : ProdID, DepartmentID

The requirement is, the products has to be allocated accordingly to the different departments (assume there are 10 products in total) and time to time I should be able to manage the products assigned to the departments.
Assume I have already assigned 3 products to the "Department A", and now I want to add a new product to the same department.
Thus, when I load the data for the "Department A", I need to view all the 10 products and importantly, a tick in front of the selected 3 products for the respective department.

Note
Please find the attached sample datagridview I am using in this example.

As a result, I got 7 products available to assign to the "Department A" at the moment.

Appreciate your feedback on how to accomplish this task.

Thank you.

Kind regards,

Andrew

 

[NoUserHere]

Seems straight forward, so what have you tried, thought of or attempted? Just so you understand, we don't write code for you. We help you with the code you already have attempted to use.

Lets see what you've tried and explain your problem.

 

[andrewmanuja]

Hi Sheepings,

Thanks for your reply.

Basically, the logic I have written so far is,

1. Loading all the "Departments" to a combobox on the form window allowing the user to select a Department.
2. Once the user selected a department, user will be loaded with all the "Products" in the DataGridView. THe respective code looks like below;

  dataGridView1.Rows.Clear();
            SqlDataAdapter sda = new SqlDataAdapter("SELECT ProdID, ProdName FROM tbl_Product", con);
            DataTable dt = new DataTable();
            sda.Fill(dt);

            foreach(DataRow item in dt.Rows)
            {
                int n = dataGridView1.Rows.Add();
                dataGridView1.Rows[n].Cells[1].Value = item["ProdID"].ToString();
                dataGridView1.Rows[n].Cells[2].Value = item["ProdName"].ToString();
                dataGridView1.Rows[n].Cells[3].Value = cmbDepartmentID.Text;
            }

For this testing example, I have not used the parameters when retrieving the data.

3. Thus, the user will select the products which has to be allocated to the selected department and save the data in to a DB table called, "tbl_Allocation".

The respective code looks like below;

 for (int i=0; i<dataGridView1 .RowCount -1; i++)
            {
                if(Convert.ToBoolean (dataGridView1 .Rows[i].Cells["selectedItem"].Value) == true)
                {
                    con.Open();
                    cmd = new SqlCommand (@"INSERT INTO tbl_Allocation (ProdID,DepartmentID ) VALUES('"+dataGridView1.Rows[i].Cells[1].Value + "', '"+dataGridView1.Rows[i].Cells[3].Value + "')",con);
                    cmd.ExecuteNonQuery();
                    con.Close();
                }               
            }

My requirement is explained below;
4. When the user opens the form, and if the previously selected Department is selected, the dataGridView should load with the data with all the Products, and the already allocated products should have a tick under the respective "Selected Item" column.
Basically, when step No 2 of the logic is running, I want to check whether the selected department got some data related to the products and tick the products accordingly.

Appreciate your feedback.

Kind regards,

Andrew

 

[NoUserHere]

Sorry for the late reply, I've been busy.

So what's the problem? You select a department, and depending on the department, you fill your control with data, and then with the selected checkboxes, you can iterate and check their checkstates with DataGridView checkBox column and do as you please with those object values.

Make it a practice to start using parameters, regardless what type of database you might be working on.

 

[andrewmanuja]

Hi,

Thanks for your comment. I wrote the below command and getting the error of "There is already an open DataReader associated with this Command which must be closed first".

 if (con.State != ConnectionState.Open)
            {
                con.Open();
                dataGridView1.Rows.Clear();
                SqlDataAdapter sda = new SqlDataAdapter("SELECT ProdID, ProdName FROM tbl_Product", con);
                DataTable dt = new DataTable();
                sda.Fill(dt);

                foreach (DataRow item in dt.Rows)
                {
                    int n = dataGridView1.Rows.Add();
                    dataGridView1.Rows[n].Cells[1].Value = item["ProdID"].ToString();
                    dataGridView1.Rows[n].Cells[2].Value = item["ProdName"].ToString();
                    dataGridView1.Rows[n].Cells[3].Value = cmbDepartmentID.Text;
                    dataGridView1.Rows[n].Cells[4].Value = item["ProdID"].ToString() + cmbDepartmentID.Text;
                }
                con.Close();
            }              
   
            cmd = new SqlCommand("SELECT Value FROM tbl_Allocation WHERE DepartmentID = @aDepartmentID", con);
            cmd.Parameters.Add("@aDepartmentID", SqlDbType.Int).Value = cmbDepartmentID.Text;
            if (con.State != ConnectionState.Open)
                {
                   con.Open();
                 
                    SqlDataReader dr = cmd.ExecuteReader();
               
                    if (dr.HasRows)
                    {
                        DataTable dt1 = new DataTable();
                        SqlDataAdapter sda1 = new SqlDataAdapter(cmd);
                        sda1.Fill(dt1);                      
                        for (int row = 0; row <= dataGridView1.Rows.Count -1; row++)

                            if (dataGridView1.Columns["Value"].ToString().Equals(Value))
                            {
                                dataGridView1.Rows[row].Cells["selectedItem"].Value = true;
                            }
                            else
                            {
                                dataGridView1.Rows[row].Cells["selectedItem"].Value = false;
                            }
                    }
                    dr.Close();
                    dr.Dispose();              
                }
                con.Close();

Please find the above code for the said issue. Since I couldn't test the entire code(because of the above error), could you also advice me on the checkstatus of the products (Checkbox) against the department where the products have already allocated to.

Note
tbl_Allocation got the fields: ProdID, DepartmentID, Value (This is the concatination of "ProdID" and "DepartmentID").

The updated datagridview got an additional column named, "Value".


Look forward to hearing from you.

Thank you.

Kind regards,

Andrew

 

[NoUserHere]

You only need to wrap your reader SqlDataReader dr = cmd.ExecuteReader(); in using blocks or provide a new command/connection for SqlDataAdapter sda1 = new SqlDataAdapter(cmd); and close your previous one with your reader. While your reader reads, consider reading the results into a suitable object where you can access your data when your reader is finished executing. Doing so will allow the rest of your code to execute.

 

[andrewmanuja]

Hi,

Sorry for the trouble created.

Still slightly confused with wrapping the datareader object. Is it something like using the "using" command to wrap the reader?

I created a new connection for the datareader too.

cmd = new SqlCommand("SELECT Value FROM tbl_Allocation WHERE DepartmentID = @aDepartmentID", con2);
            cmd.Parameters.Add("@aDepartmentID", SqlDbType.Int).Value = cmbDepartmentID.Text;
            if (con2.State != ConnectionState.Open)
                {
                 con2.Open();

                 using (SqlDataReader dr = cmd.ExecuteReader()) {
                
                    if (dr.HasRows)
                    {
                        DataTable dt1 = new DataTable();
                        SqlDataAdapter sda1 = new SqlDataAdapter(cmd);
                        sda1.Fill(dt1);                       
                        for (int row = 0; row <= dataGridView1.Rows.Count -1; row++)

                            if (dataGridView1.Columns["Value"].ToString().Equals(Value))
                            {
                                dataGridView1.Rows[row].Cells["selectedItem"].Value = true;
                            }
                            else
                            {
                                dataGridView1.Rows[row].Cells["selectedItem"].Value = false;
                            }
                    }
                    //dr.Close();
                    //dr.Dispose();
                }
            }
                con2.Close();

However, still I am getting the same issue.

Appreciate your feedbak.

Kind regards,

Andrew

 

[NoUserHere]

Your error is here SqlDataAdapter sda1 = new SqlDataAdapter(cmd); ?

Apply the second and third part from post 6. You've only added the using blocks. Your using block runs straight into the next command, and you need to alter your code and close off before executing your new command on line 12. What you done is not what I was advising. I don't see where you read your results into an object either...

The error is clear : "There is already an open DataReader associated with this Command which must be closed first".
From 7 to 12, you use the same command when it's already executing. Reread previous reply again.

 

[NoUserHere]

Your use of data reader is rather pointless. If all you want to do is check if there are rows. ExecuteScaler would be better. If you want to check if there are matching records, you can count them. You should use the using statement as much as possible, as it will also save on cleaning up later.

 

[andrewmanuja]

Hi Sheepings,

I understood that both the datareader and the SqlDataAdapter was using the same "cmd" command which was a mistake.

I adjusted the code as below and have removed the SqlDataAdapter.

Could you please advise me on the issue of the below code,

SqlCommand cmd = new SqlCommand("SELECT Value FROM tbl_Allocation WHERE DepartmentID = @aDepartmentID", con);
            cmd.Parameters.Add("@aDepartmentID", SqlDbType.Int).Value = cmbDepartmentID.Text;
            if (con.State != ConnectionState.Open)
                {
                 con.Open();

                 using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    if (dr.HasRows)
                    {               
                        for (int row = 0; row <= dataGridView1.Rows.Count -1; row++)
                                              
                           if (dataGridView1.Columns["Value"].ToString().Equals(Value))
                            {
                                dataGridView1.Rows[row].Cells["selectedItem"].Value = true;
                            }
                            else
                            {
                                dataGridView1.Rows[row].Cells["selectedItem"].Value = false;
                            }
                    }
                   dr.Dispose();
                }
            }
                con.Close();

basically, I am trying to iterate through individual data records of the DataGridView and compare the "Value" columns against the Value retrieved from the "tbl_Allocation" table and if a match is found, I am setting the "selectedItem" checkbox value to "true".
Appreciate your valuable advise. Sorry, if I am still missing something again.

Thank you.

Kind regards,

Andrew

 

[Skydiver]

The Value in your your SQL statement on line 1 will not magically appear as the Value in your C# code on line 13. You need to have some extra bit of code to read that value out of the data row dr.

Why are you comparing the the column "Value" on line 13? Shouldn't you be looking at each row whose cell column "Value"?

 

[andrewmanuja]

Hi,

Your comment is not incorrect.

I have not added the entire code in to the post 10 and please find the code fro the respective event below.

 if (con.State != ConnectionState.Open)
            {
                con.Open();
                dataGridView1.Rows.Clear();
                SqlDataAdapter sda = new SqlDataAdapter("SELECT ProdID, ProdName FROM tbl_Product", con);
              
                DataTable dt = new DataTable();               
                sda.Fill(dt);

                foreach (DataRow item in dt.Rows)
                {
                    int n = dataGridView1.Rows.Add();
                    dataGridView1.Rows[n].Cells[1].Value = item["ProdID"].ToString();
                    dataGridView1.Rows[n].Cells[2].Value = item["ProdName"].ToString();
                    dataGridView1.Rows[n].Cells[3].Value = cmbDepartmentID.Text;
                    dataGridView1.Rows[n].Cells[4].Value = Convert.ToInt32 (item["ProdID"].ToString()+cmbDepartmentID.Text);
                } 
                con.Close();
            }
          
            SqlCommand cmd = new SqlCommand("SELECT Value FROM tbl_Allocation WHERE DepartmentID = @aDepartmentID", con);
            cmd.Parameters.Add("@aDepartmentID", SqlDbType.Int).Value = cmbDepartmentID.Text;
            if (con.State != ConnectionState.Open)
                {
                 con.Open();

                 using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    if (dr.HasRows)
                    {               
                        for (int row = 0; row <= dataGridView1.Rows.Count -1; row++)
                                              
                           if (dataGridView1.Columns["Value"].Equals(Value))
                            {
                                dataGridView1.Rows[row].Cells["selectedItem"].Value = true;
                            }
                            else
                            {
                                dataGridView1.Rows[row].Cells["selectedItem"].Value = false;
                            }
                    }
                    //dr.Close();
                    dr.Dispose();
                }
            }
                con.Close();

At the moment I am able to get the Items checked only for the departments which got products assigned/allocated. However, the issue is, assume only a couple of products have allocated to a department out of a total of 6 products, the loaded data for a department will check all the products irrespective of the products which are already allocated.

Note
Please find the sample dataGridView of my project

Appreciate your valuable feedback on what I am missing here.

Kind regards,

Andrew

 

[NoUserHere]

Making use of the DataGridViewCheckBoxColumn Class (System.Windows.Forms) would be useful here me thinks. What do you think?

Where are you pulling this value from : if (dataGridView1.Columns["Value"].Equals(Value))?

 

[andrewmanuja]

Hi,

Yes I am using the "DataGridViewCheckBoxColumn" for the Selected Item column.

The issue is even though I have allocated only two products to a department, when I select it, it check all the check boxes (all the products) irrespective of the allocated number o f products.

Seems like the allocation of products to a department is evaluating correctly but, does not check the only items which have allocated to the department.

Appreciate your thoughts.

Kind regards,

Andrew

 

[andrewmanuja]

Missed the remainder of your response.

In regards to pulling the values,
dataGridView1.Columns["Value"] is generate when loading all the product data in to the dataGridView (ex -

 dataGridView1.Rows[n].Cells[4].Value = Convert.ToInt32 (item["ProdID"].ToString()+cmbDepartmentID.Text);

)

Equals(Value) is from the tbl_Allocation table. Where the data is saved as an integer.

Thank you.

Andrew