Question Retrieve the data rows with already selected items

andrewmanuja

Well-known member
Joined
May 30, 2019
Messages
75
Programming Experience
Beginner
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.

DataGridView.png


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
 
I'll repeat what I said in post #11.
The "Value" in Equals(Value) is not the same Value from "SELECT Value FROM tbl_Allocation". There is no way for the SQL server to go reach out from the server and modify the memory location for Value that is currently running. I suspect that Value is a class variable or constant that you have declared somewhere else in your class.

And again, I'll ask: Why are you doing this: dataGridView1.Columns["Value"].Equals(Value)? You are check to see if the column is equal to Value which you said is an integer. Perhaps you meant check the value in the "Value" column of the current row that you are iterating over. dataGridView1.Rows[row].Cells["Value"].Value == Value ?
 
Hi,

I'll explain the my requirement.

Basically, I got a form window which looks exactly the same as below. There are three tables;

tbl_Product : ProdID, ProdName, Price
tbl_Department : DepartmentID, DepartmentName
tbl_Allocation : ProdID, DepartmentID , CombinedCode (I just changed the field name "Value" to "CombinedCode" to avoid any confusions)

There are six products in the tbl_Prodcut and 5 Department in the tbl_Department table.

Each department will be allocated products based on their business operation. (Ex - Department HR with Product A, Product B and Department Finance with Product A, Product D, Product E, etc.)

Thus, if I select a department I need to display all the products in the dataGridView and, if the selected department has already allocated with some product, only the respective products should be checked. (Ec - If the user selects Department HR, out of all the 6 products in the dataGridView, Product A and Product B should have checked. The "Selected Item" column is a "DataGridViewCheckBoxColumn".

1567658107039.png


Thus,
C#:
dataGridView1.Columns["CombinedCode"].Equals(CombinedCode)
is to check whether the dataGridView's "CombinedCode" column got any matching value with the datareader "CombinedCode" retreived from the tbl_Allocation and if satisfies, I need to check the check box. If not, set the checkbox value to false.

Hope my requirement is clear now.

Thank you.

Kind regards,

Andrew
 

Attachments

  • 1567657201951.png
    1567657201951.png
    11 KB · Views: 29
All you did was repeat what you said in your original post and your follow up post. Let me try a more direct set of questions:

Where is your variable CombinedCode declared?

How does it get initialized?
 
Hi,

Sorry for delay in replying.

Was sick and couldn't get in to a computer.

Actually, I am not declaring a variable named, "CombinedCode". I create the "CombinedCode" value, joining the ProdID and DepartmentID when loading the product data into the dataGridView. This data will save into the tbl_Allocation table at the "Save" event.

I am using the for loop to check the generated "CombinedCode" (as specified above) value against the "CombinedCode" value loaded from the tbl_Allocation.

Am I doing a mistake here?

Appreciate your feedback.

Thank you.

Kind regards,


Andrew
 
Okay, so that is the origin of your CombinedCode variable. Can you explain why you think that this:
C#:
dataGridView1.Columns["CombinedCode"].Equals(CombinedCode)
will:
check whether the dataGridView's "CombinedCode" column got any matching value with the datareader "CombinedCode" retreived from the tbl_Allocation

How does the value read by the SqlDataReader dr make it into the dataGridView1.Columns["CombinedCode"] so that you can make a reasonable comparison? For reference, here's how I assume you are using the SQL data reader:

C#:
SqlCommand cmd = new SqlCommand("SELECT CombinedCode FROM tbl_Allocation WHERE DepartmentID = @aDepartmentID", con);
cmd.Parameters.Add("@aDepartmentID", SqlDbType.Int).Value = cmbDepartmentID.Text;
:
using (SqlDataReader dr = cmd.ExecuteReader())
{
    :
    if (dataGridView1.Columns["CombinedCode"].Equals(CombinedCode))
    :

From my point of view, the value of the "CombinedColumn" from the data reader never makes it into the data grid view. To examine what data reader got back you'll need to do something like below:
C#:
using (SqlDataReader dr = cmd.ExecuteReader())
{
    while (dr.Read())
    {
        if (dr["CombinedValue"].ToString() == ...)
        :
    }
}
to iterate over every row, and then try to match those up with the existing rows you have in your datagrid view.

There is actually a much better way to achieve what you want by letting the database do all the work. There is a way to build a SQL query that will give you your desired value fro the "Selected Value" column as a boolean value and simultaneously provide you with the rows and columns of your desired view. Unfortunately, I'm in the NoSQL camp and can't give you any reliable advice on exactly how to build that query. This maybe a starting for you though: How To Return Boolean Values From SQL Server
 
Hi,

Thank you very much for your valuable feedback.

Actually what I am trying to do at,

Okay, so that is the origin of your CombinedCode variable. Can you explain why you think that this:
Code:

dataGridView1.Columns["CombinedCode"].Equals(CombinedCode)
is to match the dataGridView's column "CombinedCode" value against the datareader's "CombinedCode" value.

However, as you specified, I'll also try to get the required data from the SQL server database end.

Will give an update accordingly.

Thanks again for your help.

Kind regards,

Andrew
 
Back
Top Bottom