Master / Detail Data - major issue/Bug?

[PhillD]

I have made several posts on here lately regarding master/detail or parent/child data setups but no one has been able to help. It seems like a subject people don't like to tackle BUT I really need help.

I'm having a real hard with the DataView.RowFilter functionality and it is driving me insane.

For the most part, I have the basic master/detail functionality working. When you select a record in the first grid, it shows the correct data in the second grid. When you enter a record in the first grid, you can enter related data in the second grid. I accomplish this by using a dataview and row filter bound to the 2nd grid. When you change the record in the first grid, the rowfilter for the 2nd grid is restated and reapplied.

The problem arises when I overwrite the primary field value in the first grid and the detail records become orphaned. You would think all I have to do is change the ID in the 2nd grid to match the new ID in the 1st grid but this is where I am having problems. I wrote a loop to change the ID of each record currently loaded in the 2nd grid to match the newly changed ID in the first grid. The code is here:

Dim I As Integer

        For I = AuthorizationGroupsDGV.Rows.Count - 2 To 0 Step -1
            AuthorizationGroupsDGV.Rows(I).Cells(0).Value = StrEmployeeID
        Next

I have used this code before and it works great. It has to step backwards through the grid because when the ID's are changed, they no longer match the RowFilter criteria and they disappear from the grid. They still exist in the dataset. The problem is, while the code "works" the intended effect does not.

Some of the records completely disappear from the dataset and do not reload when I restate the row filter against the new ID number. I hope I am explaining this ok.

I will post some screen shots so you can see what I am talking about.

Someone please help me with this, I have been working on it for 2 weeks.

 

[jmcilhinney]

You're doing things the hard way. You should use BindingSources and configure your data-bindings correctly, plus configure the DataRelation in your DataSet to propagate updates across the relation and then there's no code required.

Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

That will handle the data-binding and automatic filtering. For the automatic propagation of keys from parent to child, make sure the DataRelation.ChildKeyConstraint.UpdateRul is set to Cascade.

 

[PhillD]

I tried using the relation but I have a major problem with it. It you wipe out the primary key from the master table (which in reality is entirley possible), you will loose your records in the child table. It doesn't wipe them immediatley but when you enter a new primary key value, the child records are lost.

 

[jmcilhinney]

Why would you be "wiping out" a primary key? What exactly does that even mean? The only reason that a primary key should be changing is when records are added and saved for the first time. When you add a new record to your DataTable it will generate a temporary ID. When you save the data to the database, a permanent ID is generated that may or may not be the same as the original. If it's different then you need to update the ID in the parent table, which can happen automatically for many databases (e.g. SQL Server) but not all (e.g. Access). If you've configured the DataRelation as I said then that new value is automatically propagated across the relation to the child records and everyone is happy. It all happens automatically, i.e. no code, if you have configured everything properly.

I'll ask now what database you're using. This is the sort of information that should be provided as part of a full description of the problem upfront. If you're using Access then that makes things more difficult, which is one reason to use a "proper" database.

 

[PhillD]

I am using SQL 2005 and the only time the primary key changes is when the user first enters their record.

The primary key is an employeeID. The user is able to enter the employeeID and then enter child records against that employee. The user could realize they are entering data against the wrong employee and change the employee which should propagate to the child records. As you have mentioned, this is handled by setting up a relationship. However, if the user delete's the employeeID, or they enter a duplicate employeeID, the child entries will be lost because the relationship deletes the child entries when you enter the corrected employeeID. In this particular form they will loose a maximum of 7 child records. Some was say "boo friggin hoo" but I say "my program should not display limitations because of Microsoft’s lack of forethought when coding their objects".

Our current ERP system (that is written in .net) does not have this limitation in their system, therefore neither should mine.

 

[jmcilhinney]

OK, I may have misread the situation here but what you seem to be saying doesn't actually make sense. You say:

The problem arises when I overwrite the primary field value in the first grid and the detail records become orphaned.

First up, if you have a foreign key constraint then it's not possible for child record to be orphaned. If the child records exist then there must be a parent record. Secondly, if you really are actually changing a value in a record then there are two possibilities:

1. That field is the primary key, in which case a cascading update rule will propagate the change to the children.
2. That field is not the primary key, so the relation is unchanged so there's no issue.

If child records are disappearing from your grid though, I can only assume that you are actually selecting a different parent record, rather than editing the selected parent record. Those are two very different actions.

Regardless, I think you just need to provide a clearer explanation of exactly what the data is and what the relationships are, as well as what the workflow is. Whatever it is that you want, you can write code to make it happen. It's just not really clear exactly what you want from what you've posted so far.

 

[cjard]

Dim I As Integer

        For I = AuthorizationGroupsDGV.Rows.Count - 2 To 0 Step -1
            AuthorizationGroupsDGV.Rows(I).Cells(0).Value = StrEmployeeID
        Next

I have used this code before and it works great.

I'd never advocate writing code that hammers on GUI elements (that are showing data held elsewhere) because you end up with a rather tedious link of poking data around in the app. Grids show data and provide a method for the user to edit it. If you want to edit data, edit the data model itself, rather than poking values into the grid that is showing it

Always bear in mind that when yu poke data into controls, some controls or interim devices in the link will maintin a cached list of the data for their own purposes, and they only flush it to the underlying model occasionally. This is why if you want to edit the model, edit the model rather than poking the datagridview.. For more info look up BindingSource.EndEdit

I'm struggling to believe that you've asked your qestions here several times and noone has answered; my memory isnt great, but I dont recall seeing several questions from you regarding related data.

Were I you I'd clearly define and design what my user is supposed to be able to do on a particular screen. Orphaned data is not allowed, so deleting the parent should delete the children. If this is not to happen, give the user the option to wipe the parent but leave the related record intact for repopulation, merge the parent into another (merging the children too - i.e. find the children and change their ID to the ID of the new parent then remove the old parent) or delete the entire tree

 

[cjard]

The user could realize they are entering data against the wrong employee and change the employee which should propagate to the child records.

And what is to happen to the parent? In .NET's modus operandi youre editing the primary key of the parent - for sure it can update the children in a related and automatic fashion but the parent is also updated. This may clash with an existing parent in which case there won't be an update of either the parent or consequently the children. .NET is good but it sounds like youre expecting it to be artificially intelligent and know when the user has chosen the wrong parent.Given that it cannot do this, you have to make a button/facility that "moves the children to another parent":

"You just changed the parent employee number. Is this because you picked the wrong employee number and you'd like to switch to the new employee but carry with you all the children you've just created, or do you just want to change the employee number of the current employee, or do you want to just go to the new employee number and dump the children you added in the bit bucket?"

Go on.. tell me how you expect .NET to know which of these options to pick

However, if the user delete's the employeeID, or they enter a duplicate employeeID, the child entries will be lost because the relationship deletes the child entries when you enter the corrected employeeID.

I never had this happen. If you delete the employeeID you get an error that the primary key cannot be null. If you duplicate it you get an error that it cannot be a duplicate. The children don't update because the change is never commited to the underlying row. If you can post an example project that reproduces this behaviour then maybe we can raise a bug with MS for it because youre purporting to have broken the definition of a primary key in their dataset

Microsoft’s lack of forethought

I think they do a pretty good job, on the whole.. Were you expecting them to have written your app for you?

 

[PhillD]

I was hoping the screen shot's would have cleared this up but Ok, here's a couple of things you need to know.

1. I have coded my own datasets, they have not been producred by a datawizard.
2. There are no constraints setup in the database.
3. None of the data has been saved to the database, this is all GUI/data entry based issues.

Please take a look at my first scrren shot. It shows an employee entry grid.
1. The user starts by entering an employee ID. E.G. 123
2. My code kicks in and applies a filter to the RIGHT grid for employee ID 123.
3. The user then enters 3 group records in the RIGHT grid for employee 123.
4. The user has entered 1 employee record and 3 group records.
5. The user has made a mistake, they entered the wrong employee in the LEFT grid so they change the EmployeeID in the LEFT grid to 456.
6. My code kicks in and changes all the values in the EmployeeID column in the RIGHT grid to 456.
7. The right grid has a filter for employeeID = 123, I have just changed the employeeID to 456. The values in the right grid dissapear because they do not meet the rowfilter criteria.
8. I change the row filter for the RIGHT grid to employeeID = 456 and refresh the grid
Please look at screen shot 2
9. 1 of the child records is missing.

This is the meat of the problem.

The explanation of the primary key being wiped is this.
1. The user starts by entering an employee ID. E.G. 123
2. My code kicks in and applies a filter to the RIGHT grid for employee ID 123.
3. The user then enters 3 group records in the RIGHT grid for employee 123.
4. The user has entered 1 employee record and 3 group records.
5. The user has made a mistake, they entered the wrong employee in the LEFT grid so they delete/change the employeeID to blank.
6. My code kicks in and changes all the values in the EmployeeID column in the RIGHT grid to blank.
7. The user then enters the correct employeeID of 456
8. My code kicks in and changes all the values in the EmployeeID column in the RIGHT grid to 456.

If I was using the relationship method as you described, step 7 would cause all the child elements to be lost. I tested this in an example from microsoft. Relationships can't handle null vales. This is why I cannot use the rerlationship method to link the master/detail data.

 

[PhillD]

cjard. I think you are correct that programmatically changing the data in the grid is causing the problem, when I manually enter the new employeeID into the child datagridview I don't get the errors I have described. I did write some code that programmatically changed the record in the DataVIEW but had exactly the same problem. It is ridiculous that you cannot change data in this way. Why does the control cache it's own data? Shouldn't it be directly linked to the DataTable? How does it sync? Are you left in a situation where something might sync and it might not, depends on what mood it's in? If a control is bound to a dataset, it shouldn't matter if you manually enter data or programmatically change it, it should still propagate to the DataSet.

My comment on posting this several times before was mistaken, I had this question on another forum, so sorry for that.

Please see my last post for an explanation of the full issue. You will see the exact problem.

If the user enters a duplicate value, an error is displayed and the employeeID is changed to blank (this is handled by code that I wrote) (and subsequently changes the child record employeeID to blank).

I am not expecting .net to know anything, in fact, I code the data connections and datasets myself so that I am not tied to the restrictions/constraints the wizard creates. I want to control my data, after all you said it perfectly, how is .net supposed to know what you really want to do.

 

[PhillD]

I wanted to let you know that I have found a way around the problem. I added a new integer based primary key field called ExpenseUserID. When I create a new line in the LEFT grid, I create a new ExpenseUserID Key, then I use the key in the RIGHT grid to link the two sets of information. So regardless of which employee I am entering, I can keep the two grids synchronized. I didn't really want to have to do it that way, the EmployeeID would have sufficed as the primary key. The problem with changing values in a filtered DataView is still valid however. I don't know why other people have not run into it.

 

[cjard]

Quite often I've found, when I run into some major issue while coding that seems to be a framework limitation, I was just going about things in the wrong way..

If you have a filtered DataView and you change a record so that it does not pass the filter, the record is hidden. I'd say this is intended behaviour. If you cannot tolerate it, Filter on another column (boolean IsHidden) and set the value of that column yourself manually at some other time more suited to the way you want the app to work

 

[PhillD]

The problem wasn't that the record is hidden. It's that when you change the value, it doesn't propograte to the underlying datatable correctly. In my case it was actually leaving the old records in tact and creating new records.

 

[PhillD]

I have found another problem, this time with the DefaultValuesNeeded

Setup a form with single DataGridView and 2 columns using default names.

Go into form design and in the Form header write a Dim I as interger line

Create a DefaultValuesNeeded event for the grid and enter the following code.

        I = I + 1
        e.Row.Cells(0).Value = I

Run the project and open the form. You should see a number in the first coumn.
1. Enter some text in the 2nd column and click off the line.
2. Delete the 1st line

You should now see a new line with a new default value supplied. This is good, it's how it should work.

Now bind the grid to a table (here is the code I used). Obviously this is built for my data and will need customizing if you want to try this.

Imports System.Data
Imports System.Data.SqlClient

Public Class Form2



    'Connections
    Dim MyPhantomConn As SqlConnection

    'ExpenseUsers
    Dim ExpenseUsersAdapter As New SqlDataAdapter
    Dim ExpenseUsersDataSet As DataSet
    Dim ExpenseUsersCommandBuilder As SqlCommandBuilder


    Dim I As Integer

    Private Sub DataGridView1_DefaultValuesNeeded(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) Handles DataGridView1.DefaultValuesNeeded
        I = I + 1
        e.Row.Cells(0).Value = I
    End Sub


    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'load the expense Header DataSet
        'set connection string
        MyPhantomConn = New SqlConnection(My.Settings.PhantomConnectionString)

        LoadExpenseUser()
    End Sub

    Private Sub LoadExpenseUser()
        ExpenseUsersAdapter.SelectCommand = New SqlCommand
        ExpenseUsersAdapter.SelectCommand.Connection = MyPhantomConn
        ExpenseUsersAdapter.SelectCommand.CommandText = "Select * From ExpenseUser ORDER BY EmployeeID"

        'automatically create insert/delete/update statements
        ExpenseUsersCommandBuilder = New SqlCommandBuilder(ExpenseUsersAdapter)

        ExpenseUsersDataSet = New DataSet()

        ExpenseUsersAdapter.Fill(ExpenseUsersDataSet, "ExpenseUsers")

        With DataGridView1
            .AutoGenerateColumns = False
            .DataSource = ExpenseUsersDataSet
            .DataMember = "ExpenseUsers"

            .Columns(0).DataPropertyName = "ExpenseUserID"
            .Columns(1).DataPropertyName = "EmployeeID"

        End With

    End Sub
End Class

Repeat steps 1&2 above. A new default value is not supplied after you delete the first record. This is incorrect behavior. The DefaultValuesNeeded event is not firing correctly when the grid is bound to data and you delete a line. I will have to write more code into the other event's to compensate for this "shortfall".

 

[cjard]

The problem wasn't that the record is hidden. It's that when you change the value, it doesn't propograte to the underlying datatable correctly. In my case it was actually leaving the old records in tact and creating new records.

If you can post up a reproduction of the "bug" we can either explain it, or tell you how to report it to Microsoft