Master-detail datagridview: how to filter details when master is filtered by SelectCommand parameter

Question

0.00/5 (No votes)

See more:

On my form i have a textbox, 2 datagridviews (master-detail), and a search button

SelectCommand for Master DataAdapter has a parameter to limit retrieved rows.

select * from master_table where x_column = some_value

currently, my SelectCommand for Detail DataAdapter is :

select * from detail_table

when i tried to create a DataRelation between master and detail based on master's primary

key, i got an exception :

this constraint cannot be enabled as not all values have corresponding parent values.

i think it's because master rows are filtered by a parameter, while detail rows are not...

what should i do?

C#

public partial class Form1: Form
{
    private String connectionString = null;
    private OracleConnection oracleConnection = null;
    
    private String equQuery = null;
    OracleCommand equCmd = null;
    private OracleDataAdapter equDataAdapter = null;        
    private OracleCommandBuilder equComBldr = null;       
    private DataTable equDataTable = null;
    private BindingSource equBindingSource = null;
    
    private String partQuery = null;
    OracleCommand partCmd = null;
    private OracleDataAdapter partDataAdapter = null;
    private OracleCommandBuilder partComBldr = null;
    private DataTable partDataTable = null;
    private BindingSource partBindingSource = null;
    
    private DataSet dataset = null;
    
    
    
    private void Form1_Load(object sender, EventArgs e)
    {
    
        oracleConnection = new OracleConnection(connectionString);
        oracleConnection.Open();
        
        
        // SELECT COMMAND FOR MASTER TABLE
        equQuery = "select bill_equ_id  ,bill_id ,equ_serial_nbr ,equ_cost ,maintenance_date from bso_equipment_maint_bill_equ where bill_id = :bill_id";
        
        equCmd = new OracleCommand(equQuery, oracleConnection);
        equCmd.Parameters.Add("bill_id", OracleType.Number);

        /* initial value to make master grid retrieve no data on load */
        equCmd.Parameters["bill_id"].Value = -1;
        
        
        // SELECT COMMAND FOR DETAIL TABLE… !!!???????
        /* since command has not parameters, grid is being filled with ALL detail records on load.  i don't know how to get only rows related to retrieved master rows */
        
        partQuery = "select bill_equ_id , part_nbr , part_cost , quantity from bso_equipment_maint_bill_part ";            
        
        partCmd = new OracleCommand(partQuery, oracleConnection);
        
        
        
        // CREATE ADAPTERS
        equDataAdapter = new OracleDataAdapter(equCmd);
        equComBldr = new OracleCommandBuilder(equDataAdapter);
        
        
        partDataAdapter = new OracleDataAdapter(partCmd);
        partComBldr = new OracleCommandBuilder(partDataAdapter);
        
        
        // CREATE AND FILL DATA TABLES
        equDataTable = new DataTable();
        equDataTable.TableName = "bso_equipment_maint_bill_equ";
        partDataTable = new DataTable();
        partDataTable.TableName = "bso_equipment_maint_bill_part";
        
        equDataAdapter.Fill(equDataTable);
        partDataAdapter.Fill(partDataTable);
        
        /* bill_equ_id is a foreign key in details table. It is the primary key in master table. It’s generated on database by a (before-insert trigger) */
        
        partDataTable.Columns["bill_equ_id"].AutoIncrement = true;
        partDataTable.Columns["bill_equ_id"].AutoIncrementSeed = -1;
        partDataTable.Columns["bill_equ_id"].AutoIncrementStep = -1;
        
        // CREATE DATASET AND RELATION
        dataset = new DataSet();
        dataset.Tables.Add(equDataTable);
        dataset.Tables.Add(partDataTable);
        
        
        /* this line throws an exception : this constraint cannot be enabled as not all values have corresponding parent values */
        
        DataRelation relation = new DataRelation("EquPartRel", dataset.Tables["bso_equipment_maint_bill_equ"].Columns["bill_equ_id"], dataset.Tables["bso_equipment_maint_bill_part"].Columns["bill_equ_id"]);            
        dataset.Relations.Add(relation);
        
        
        // CREATE BINDING SOURCES AND BIND TO DATAGRIDVIEWS
        equBindingSource = new BindingSource();
        equBindingSource.DataSource = dataset;
        equBindingSource.DataMember = "bso_equipment_maint_bill_equ";
        equGrid.DataSource = equBindingSource;
        equGrid.Columns["bill_id"].Visible = false;
        equGrid.Columns["bill_equ_id"].Visible = false;
        
        partBindingSource = new BindingSource();
        partBindingSource.DataSource = dataset;
        partBindingSource.DataMember = "bso_equipment_maint_bill_part";
        partGrid.DataSource = partBindingSource;
        partGrid.Columns["bill_equ_id"].Visible = false;
        
        
        oracleConnection.Close();
        
    
    } // end of form load
    
    
    
    private void searchEquButton_Click(object sender, EventArgs e)
    {
    
        equDataAdapter.SelectCommand.Parameters["bill_id"].Value = bill_id;               
        
        equDataAdapter.Fill(equDataTable);
        partDataAdapter.Fill(partDataTable);            
    
    } // end search
    
} // class

data currently in master table :

bill_equ_id    bill_id     equ_serial_nbr  equ_cost   maintenance_date 
1                   1              1            1000      31/08/2014
8                   3              a-400        5000      30/08/2014

data currently in detail table :

C#

bill_equ_id       part_nbr      part_cost     quantity
1                  a-209         500             1
1                  a-210         800             2
8                  a-211         2500            2

Posted 8-Sep-14 1:59am

nina4ever

Updated 8-Sep-14 22:30pm

v4

Add a Solution

Comments

George Jonsson 8-Sep-14 8:41am

This is really not not enough information.
1. You use SELCECT *, which hides all column information. (And it is bad coding standard)
2. You don't show the c# code where you assign the data tables and create the relation.

We cannot see your screen or guess your code.

nina4ever 9-Sep-14 2:29am

okay, i just tried to make my question as simple as possible. i will provide more details soon. thank you for your concern.

George Jonsson 9-Sep-14 2:34am

Well, think about the information you would need to answer the question.

nina4ever 9-Sep-14 4:30am

update done :)

Anand Gunasekaran 8-Sep-14 14:24pm

Sample i/p & o/p need!

George Jonsson 9-Sep-14 5:02am

Is 'bill_equ_id' the primary key in both tables?

nina4ever 9-Sep-14 5:29am

bill_equ_id is primary key in master, and a foreign key in detail. primary key in detail table is (bill_equ_id , part_nbr)

George Jonsson 9-Sep-14 5:04am

What is your wanted outcome?
Maybe you can do what you want by joining the two tables in one SELECT statement.

nina4ever 9-Sep-14 5:30am

i want to be able to display, insert, update and delete records from both master and detail tables, that's why i need two grids.

i haven't written code for insert and update yet, since (select) is not working correctly

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

George Jonsson · Accepted Answer · 2014-09-08T23:53:00

Solution 2

I am not sure but I think the problem might be your composite primary key in the detail table.

I tried this code and it works fine.
I used the data you provided in the question.

C#

DataTable dtMaster = new DataTable("bso_equipment_maint_bill_equ");

// I have to fill the tables manually
DataColumn dcMasterPK = dtMaster.Columns.Add("bill_equ_id", typeof(int));
dtMaster.PrimaryKey = new DataColumn[] { dcMasterPK };
dtMaster.Columns.Add("bill_id", typeof(int));
dtMaster.Columns.Add("equ_serial_nbr", typeof(string));
dtMaster.Columns.Add("equ_cost", typeof(decimal));
dtMaster.Columns.Add("maintenance_date", typeof(DateTime));

dtMaster.Rows.Add(1, 1, "1", 1000, DateTime.ParseExact("31/08/2014", "dd/MM/yyyy", null));
dtMaster.Rows.Add(8, 3, "a-400", 5000, DateTime.ParseExact("30/08/2014", "dd/MM/yyyy", null));

DataTable dtDetail = new DataTable("bso_equipment_maint_bill_part");

dtDetail.Columns.Add("bill_equ_id", typeof(int));
dtDetail.Columns.Add("part_nbr", typeof(string));
dtDetail.Columns.Add("part_cost", typeof(decimal));
dtDetail.Columns.Add("quantity", typeof(string));

dtDetail.Rows.Add(1, "a-209", 500, 1);
dtDetail.Rows.Add(1, "a-210", 800, 2);
dtDetail.Rows.Add(8, "a-211", 2500, 2);

DataSet ds = new DataSet();
ds.Tables.Add(dtMaster);
ds.Tables.Add(dtDetail);

DataRelation relation = new DataRelation("EquPartRel", ds.Tables["bso_equipment_maint_bill_equ"].Columns["bill_equ_id"], ds.Tables["bso_equipment_maint_bill_part"].Columns["bill_equ_id"]);
ds.Relations.Add(relation);

BindingSource bsMaster = new BindingSource(ds, "bso_equipment_maint_bill_equ");
BindingSource bsDetail = new BindingSource(bsMaster, "EquPartRel");

dgMaster.DataSource = bsMaster;
dgDetail.DataSource = bsDetail;

Maybe you should try to printout the contents of both tables before you create the relation.

Posted 8-Sep-14 23:53pm

George Jonsson

Updated 9-Sep-14 1:51am

v3

Comments

nina4ever 9-Sep-14 6:21am

done. this line still gave exception :

DataRelation relation = new DataRelation("EquPartRel", dataset.Tables["bso_equipment_maint_bill_equ"].Columns["bill_equ_id"], dataset.Tables["bso_equipment_maint_bill_part"].Columns["bill_equ_id"]);
dataset.Relations.Add(relation);

I removed the where clause from master SelectCommand, and the exception disappeared, but when i ran the form:
master grid displayed all master table rows
detail grid displayed ALL detail table rows together, even when i click on a row from master grid, detail grid still display all rows, not only corresponding ones

George Jonsson 9-Sep-14 6:55am

I updated the solution.
Please try again.

nina4ever 9-Sep-14 7:07am

dear George, it gave an exception because relation columns (primary keys) are not the same length.
"ParentColumns and ChildColumns" should be the same length

George Jonsson 9-Sep-14 7:45am

I think I led you on the wrong path.
See my updated answer.

nina4ever 9-Sep-14 8:00am

give me a few minutes to test this and compare it to my code. thank you, sir

nina4ever 9-Sep-14 8:18am

sir, thanks to your code, i corrected the DataSource of my detail BindingSource. it was wrong... now the detail grid retrieves rows related to selected master row.

Now back to the main problem: when i add a (where clause) to my master SelectCommand, then i get that same exception at the relation creation statement...

i really need to retrieve certain master rows based on user selection...

thank you so much for your effort

George Jonsson 9-Sep-14 8:30am

Have you checked the contents of equDataTable, once with the WHERE clause and once without.
There should be a difference if you say it works without it.

nina4ever 9-Sep-14 8:49am

yes there is a differece, because when bill_id = 1 , only the first row in master table is retrieved, whereas detail select statement is selecting all rows without parameters. i just don't know how to make detail grid retrieve only related rows (filtered too)..
in other words: master is filtered. detail should (automatically )be filtered too

nina4ever 9-Sep-14 8:55am

P.S: in form load , the parameter (bill_id) is given an initial value (-1) so that the grid is displayed empty on load. only column titles are displayed...
on a button click, i assign the parameter the value inserted by user, and fill grid correspondingly..
however, the exception is thrown on form load when creating the relation, and parameter at that point is (-1) .
i checked retrieved rows, and got the following :
no master rows
all detail rows

George Jonsson 9-Sep-14 9:05am

Why not move the filling of the data tables part to the button click event, and just create the structure in form_Load?

George Jonsson 10-Sep-14 2:04am

Well, glad I could help you.
Mark the question as solved, so it is not still open.

Next time you have a question ask yourself "What information is necessary for other people to understand my question?"

nina4ever 10-Sep-14 2:23am

i am sorry, but i was mistaken ! :( ...
it didn't throw an exception on relation creation, but it threw one when filling detail table... because it also retrieved ALL detail rows from the table (even non-related ones), so it gave me this exception :
failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints

nina4ever 10-Sep-14 2:24am

p.s. : filling data is now on button click. it returns master rows filtered by user input, and returns ALL detail rows

George Jonsson 10-Sep-14 3:10am

Sorry to hear that.
Can you update this question or post a new question with your updated code?
I think though, that if you use a WHERE clause to filter data for one query, you should do it for both.
Looks like you are only interested in one 'bill_id' anyway.

nina4ever 10-Sep-14 3:24am

since bill_id is not a column in details table, then i can't use the same where-clause in detail SelectCommand :( .
if i use BindingSource.Filter, both grids display correct filtered data, but underlying DataTables retrieve all data anyway. Filtering is only applied to the display.

George Jonsson 10-Sep-14 3:34am

Sorry, my mistake about the bill_id, I thought of 'bill_equ_id'.
That is the point of filtering, to only show a selected view without changing the underlying data.

nina4ever 10-Sep-14 3:37am

Do you mean filtering by BindingSource.Filter? yes, but it means that the Fill operation will always retrieve ALL data from tables. isn't that bad when my tables on database grow larger?

George Jonsson 10-Sep-14 4:00am

It depends on how much data you transfer compared to how many requests to the DB you make.
If you filter the data on the client side, you make very few transactions but transfer more data, and vice versa for doing the filtering in the SQL query.
Which is worse for you?

nina4ever 10-Sep-14 4:13am

i solved it ! i will post my new code. i managed to use the same filter in the detail SelectedCommand using an inner select ;)
how did we miss that !

i hope i can manage insert, update and delete, too

George Jonsson 10-Sep-14 4:18am

Good job.

nina4ever 11-Sep-14 3:46am

dear George, i have a problem inserting data. i have searched for a solution and failed.
shall i post my question here, or post a NEW question?
can you help me?

George Jonsson 11-Sep-14 3:54am

Post it as a new question.
Just remember to provide relevant information, e.g. the part of the code that causes you problem, not all your code.

nina4ever 11-Sep-14 4:17am

shall i post the part of code i came out with yesterday to create and fill the grids in the first place?

George Jonsson 11-Sep-14 4:43am

Depends on where your problem is.
Do you have a problem with getting the data from the datagrid or to create the INSERT query?

nina4ever 11-Sep-14 4:59am

the insert query...

http://www.codeproject.com/Questions/817587/Master-detail-datagridview-how-to-insert-master-re

nina4ever 9-Sep-14 6:29am

allow me to summarize in a few words:

i need 2 master-detail DataGridViews to display, insert, update and delete data.
master grid should display only certain rows, depending on user input.
detail grid should display only corresponding rows (i.e. related to displayed master rows)