[RESOLVED] jmcilhinney Please help with my master/detail, dataadapter problems

[Nitesh]

JM, can I please please send you my project. I've posted a few threads now. I've been having major issues with my data adapter and my mster/detail datarelation etc. If i post my project will you please have a look and help me.

I have a Result_Header(master) table which has a productid as a foreign key and when I select a product in a dropdown the result_header datagridview displays records from the resul_header table which matches the selected product, and a Result_Detail(child) table. When I add a master record and click save, my code goes and looks in a prodtest table for existing tests on the selected product and adds new rows to the child table.

Now if I add 2 new records and look at the result_header (say primary keys 5565 and 5566) and result_detail table the child table has the correct values in the resultid field(so 4 new records for each parent has 5565 and 5566) which is the ID fields value from Result_header. when I add a 3rd record(5567), for some reason, it overwrites 5565 with 5566's info in the master table and 5567's with 5566's info. Resul being 5565 now has eight chil records, 5566 has 4 and 5567 has 0.

It's quite confusing but if you see my project you'll know what I mean

[kleinma]

This is a public forum. If you want to contact John directly, the forum has a private message system. Please do not post in a public forum, a message directed at a single user.

[Nitesh]

Hi Kleinma. I wouldn't mind help from others. Its jus that John always helps me so I thought Ii I put his name there he would help me quicker. Also I remember seeing that he prefers not being private messaged

[jmcilhinney]

When you have an issue that you can't pin down, I find that the best way to proceed is to create a new test project that is as simple as possible so that you can isolate the thing that's giving you problems. I suggest that you create a simple project with a simple data: two tables with ID and Name and a foreign key. You can then try to reproduce your issue with the absolute minimum of work and then try to fix it without being distracted by all the noise. I'd be prepared to have a look at that sort of project.

[Nitesh]

Thank you JM. I will get cracking now. I really appreciate your help

[Nitesh]

JM incase I do end up posting py project. Do you have Office 2007. I have an office 2007 db.

[jmcilhinney]

JM incase I do end up posting py project. Do you have Office 2007. I have an office 2007 db.

What am I, medieval man? Of course I do.

[Nitesh]

JM it's done it even in the test project. Here's a rundown on how it works.

I select a product from the dropdown in the HeaderBindingNavigator. That brings up the records from the Result_Header table with a matching productid.

If I click on the master record in the master datagridview it brings up the child records in the child datagridview from the result_detail table.

I added a new record to the mater table, it's ID was 3. if a new row is added, I go check the prodtest table for test's that exist for that product. If tests do exist I add new datarows to the child table with the new resultid and the testid.

These added fine on the first save. I had two records in the child table with resultid(foreign key which is the id from result_header) 3. Then without closing the form I added record number 4 to the result_header datagridview.

I clicked save and it saved test3 again as record number 4 and then test4 as record number 5 in the parent table.

and in the child table 4 new records where added with resultid 3 and two new records with resultid 4.

there should have been in the header table records 1,2,3,4 only.

and in the child table there should have been two records with resultid 3, two with resultid 4.

Please have a look. I would really really appreciate it. I know im asking for alot. Thank you very much.

[jmcilhinney]

It's 5.30 PM here and I'm about to leave work. I'll take a look at it at home over the weekend, unless someone else beats me to it.

[Nitesh]

No problem JM. I can't thank you enough. I know you will find the fault. I don't have an internet connection at home but I will use my cellphone as a modem and logon during the weekend. Have a nice weekend

[jmcilhinney]

I don't have an internet connection at home

Now who's medieval man?

[Nitesh]

he he. if you see my pc you will really think of me as medieval

[stanav]

What am I, medieval man? Of course I do.

I feel really bad now... I'm still using Office 2000

[kleinma]

Don't feel bad.. John was probably using Office 95 before he got an MSDN subscription

[jmcilhinney]

Don't feel bad.. John was probably using Office 95 before he got an MSDN subscription

It wouldn't install on Windows 3.11.

[TheUsed]

You guys are halarious

it's good to see that in the forums.

[Nitesh]

good ones guys

[Nitesh]

hey guys, im back with my medieval internet connection he he.

[Nitesh]

JM for the life of me I can't figure this out. I tried but with no success so far today. time to call it a day

[jmcilhinney]

Can you provide a list of steps that I can follow with your database as it was when you posted it? Tell us what to do, what you expect to happen and what does happen.

[Nitesh]

Ok here goes:

step 1: Select a product from the dropdown in the header binding navigator
step 2: Add a new record to the 1st datagridview(Enter a new Batch Number)
step 3: Click the save button in the header binding navigator.

Whats supposed to happen:
A record is created in the Result_Header table with the relevant productid and the new batchno.

then I go check the prodtest table for any existing tests for the selected product and if they exits I create rows with existing testid from the prodtest table and the new resultid(which is the id from the parent table) in the result_detail table which is the child table.

You will see new rows being added to the child table when you click save.

Now it saves fine the first time. For example I selected SG 3378 which is productid 1. I entered test3 in my header datagridview/ I clicked save and a new record was created in my result_header table:

ID ProductId BatchNo
3 1 test3

Since productid 1 has 2 records in the prodtest table 2 new records where created in the result_detail table:

ID ResultID TestId
3 3 1
4 3 2

this is what should happen correctly.

Next I enter a new record in the master datagridview:

ID ProductId BatchNo
4 1 test4

When I click save all hell breaks loose:
Now 2 new records get added to the result_header table instead of 1:

ID ProductId BatchNo
4 1 test3
5 1 test 4

whereas the db should have had just 1 new record now:

ID ProductId BatchNo
4 1 test4

and in the result_detail table I get 4 new entries for resultid 3 and two for resultid 4 for when I should have only got 2 new recorsd for resultid4

Hope this helps. thanks again JM.

[Nitesh]

JM it seems the row I save first, it's state is still determined as rowstate.added when I click save the second time. Why would this happen? Have you managed to replicate my scnario by any chance

[jmcilhinney]

I first downloaded your project onto my desktop, only to discover that ADO.NET can't open Access databases on 64-bit systems. I later transferred it to my laptop and had a look, but wasn't 100% sure what i was looking for. Now that I have your steps I can run through them and see if I get the same results and try to work out why.

[Nitesh]

Sorry for all the trouble

[jmcilhinney]

Sorry for all the trouble

No need to apologise. You ask, we say yes or no.

[Nitesh]

this issue is driving me crazy. I've looked at examples on msdn and the web and I can't see where I am wrong. ssuming the code in my save button is incorrect somehow. JM, i'd love to see the code for how you would handle the saving on this form.

[Nitesh]

JM i've found the root of the problem.

TestForChanges = Me.data.Tables("Parent").GetChanges(DataRowState.Added)


when I add a new record and click save the above code renders this output:

ID Batchno
8 test 6

and after my code adds new rows to the child table this code:

            TestForChanges = Me.data.Tables("Child").GetChanges(DataRowState.Added Or DataRowState.Detached)

generates a datatable that looks like this:

ID ParentID
17 8
18 8

this is correct. Now after calling the respective adapter.update the changes should be commited to the dataset.

However if I now add another new parent record it still picks up the previous records rowstate as added. thus the testforchanges datatable that tests fro new parent records looks like this:

ID BatchNo
8 Test6
9 Test7

and the childs testforchanges is this:

ID ResultId
17 8
18 8
19 8
20 8
21 9
22 9

Why is this the case. Second time round testforchanges should only pick up 1 row as rowstate.added.

[Nitesh]

JM I thought calling the adapter.update would call acceptchanges on the datatable. But I added these 2 lines of code at the end of my save button sub:

            Me.data.Tables("Parent").AcceptChanges()
            Me.data.Tables("Child").AcceptChanges()

and it seems to have fixed my problem. Please, please confirm for me that this is correct. Are the above 2 lines of code in the correct place?

[jmcilhinney]

Calling Update on a DataAdapter DOES call AcceptChanges on the DataTable, but you're not passing the original DataTable when you call Update. You're passing the DataTable created by calling GetChanges. That's a different DataTable altogether so you must explicitly call AcceptChanges on the original DataTable, which is exactly what you've demonstrated in post #28.

[Nitesh]

Thank you so much for all your effort JM. I really appreciate it. You should get a reward for being so helpful. I have tons of respect for you. I hope this fixes my problem. I got more testing to do. Will you please check my save button code one last time and let me know if all is correct.

I got one more question. This is what I read on msdn:

If foreign key constraints exist on the DataTable, changes accepted or rejected using AcceptChanges and RejectChanges are propagated to child rows of the DataRow according to the ForeignKeyConstraint.AcceptRejectRule. For more information, see DataTable Constraints (ADO.NET).

Is my code right. I have acceptreject rule set to none.

[jmcilhinney]

If AcceptRejectRule is set to None then calling AcceptChanges or RejectChanges will not propagate to child rows, so you'd have to call AcceptChanges or RejectChanges on the child table yourself.

[Nitesh]

thanks again JM. . I'm so relieved now

[Nitesh]

JM my problems are not over. Say I delete a record from the parent table and the related records from the child table. so I delete records 18 and 19 from the database. and the related children.

Now if I add a new record to my parent datagridview it shows the id as 18.

When it gets inseted into the database it's id is actually 20 but the child records get inserted with id 18. I do have the updaterulke set to cascade.

Please try this and advise me. It's tha last hurdle to get over.

[jmcilhinney]

It's basically the same problem. You are creating a new DataTable by calling GetChanges so when you save the parent data the original parent DataTable doesn't get updated so the child table doesn't get updated. After saving you will need to merge the new DataTable into the original in order to update the original parent data and cascade those changes to the child data. You'll need to look at the DataTable.Merge method.

[Nitesh]

oh no. I've read about the merge Method and I have no idea where to start. This master/detail stuff is getting to me

[Nitesh]

JM it only happens when I delete rows manually. Please show me in my current save button code where the table merging would fit in. I tried but it hasn't worked.

[jmcilhinney]

To answer your question, you call GetChanges, then Update, then Merge.

There really is an easy way to avoid all this though. If you were to have relationships defined in the database as well as the DataSet, and if those relations cascaded deletes, then everything would happen automatically. Consider this:

You have grandparent, parent and child tables in the database with foreign key relations defined to cascade deletes. You have grandparent, parent and child DataTables in your DataSet with DataRelations between them set to cascade updates and deletes and also to cascade accept or reject changes. You then make all manner of changes to all three tables. If you delete a parent record the related child records are deleted too. If you add a parent record then add a child record the child record will contain a temporary parent ID. When you then Update the parent table the parent records are deleted from the database and those deletes cascade to delete the related child records, so there's no need for you to explicitly delete them. The parent IDs are also updated from the database and those updates are cascaded to the child table. AcceptChanges is automatically called on the parent tabel and the AcceptRejectRule cascades that to the child table so all the deleted child records are automatically removed from the child table, so they are never explicitly deleted from the database. You then Update child table and the child records are then inserted and updated. All nice and easy.

[Nitesh]

cool. I'm going to go with this. My way is causing me alot of stress. I've setup the relationships in the database and enforced referential integrity so that deletes and updates are cascaded.

the part that confuses me is where I am adding new rows to the child table.

TestForChanges = Me.data.Tables("Parent").GetChanges(DataRowState.Added)

            If TestForChanges IsNot Nothing Then
                HeaderAdapter.Update(TestForChanges)
            End If

            'Me.data.Tables("Parent").Merge(TestForChanges, False, MissingSchemaAction.AddWithKey)

            If TestForChanges IsNot Nothing Then
                Dim j As Integer

                For j = 0 To TestForChanges.Rows.Count - 1

                    Dim readchild As New OleDbCommand("SELECT @ResultID, PT.TestID,T.TestName " & _
                        "FROM ProdTests PT INNER JOIN Tests T ON PT.TestID = T.TestID WHERE PT.ProductID = @ProductID", con)


                    Dim intResultID As Integer = CInt(TestForChanges.Rows(j).Item(0))
                    Dim intProductID As Integer = CInt(TestForChanges.Rows(j).Item(2))

                    With readchild
                        .Connection = con
                        con.Open()
                        readchild.Parameters.AddWithValue("@ResultID", intResultID)
                        readchild.Parameters.AddWithValue("@ProductID", intProductID)
                        childReader = .ExecuteReader
                    End With

                    While childReader.Read

                        Dim nRow As DataRow
                        nRow = Me.data.Tables("Child").NewRow
                        nRow("ResultID") = childReader.GetInt32(0)
                        nRow("TestID") = childReader.GetInt32(1)

                        Me.data.Tables("Child").Rows.Add(nRow)

                    End While
                    con.Close()

                Next


            End If


            TestForChanges = Me.data.Tables("Child").GetChanges(DataRowState.Added Or DataRowState.Detached)

            If TestForChanges IsNot Nothing Then
                DetailAdapter.Update(TestForChanges)
            End If

I will still need to do that. But do I check for additions to the parent like above and add to the child like above and then call acceptchanges on the parent table. Is that the right sequence of events to follow?

[Nitesh]

ok heres my code in the save button. deletes are being cascaded. But additions are not.

'here i add to the child table if there are new parent rows:

TestForChanges = Me.data.Tables("Parent").GetChanges(DataRowState.Added)

If TestForChanges IsNot Nothing Then
                Dim j As Integer

                For j = 0 To TestForChanges.Rows.Count - 1

                    Dim readchild As New OleDbCommand("SELECT @ResultID, PT.TestID,T.TestName " & _
                        "FROM ProdTests PT INNER JOIN Tests T ON PT.TestID = T.TestID WHERE PT.ProductID = @ProductID", con)


                    Dim intResultID As Integer = CInt(TestForChanges.Rows(j).Item(0))
                    Dim intProductID As Integer = CInt(TestForChanges.Rows(j).Item(2))

                    With readchild
                        .Connection = con
                        con.Open()
                        readchild.Parameters.AddWithValue("@ResultID", intResultID)
                        readchild.Parameters.AddWithValue("@ProductID", intProductID)
                        childReader = .ExecuteReader
                    End With

                    While childReader.Read

                        Dim nRow As DataRow
                        nRow = Me.data.Tables("Child").NewRow
                        nRow("ResultID") = childReader.GetInt32(0)
                        nRow("TestID") = childReader.GetInt32(1)

                        Me.data.Tables("Child").Rows.Add(nRow)

                    End While
                    con.Close()

                Next


            End If

then I update the tables:

HeaderAdapter.Update(Me.data.Tables("Parent"))
            DetailAdapter.Update(Me.data.Tables("Child"))
            Me.data.Tables("Parent").AcceptChanges()
            Me.data.Tables("Child").AcceptChanges()

please point out my faults. I know this is dragging on but please bear with me. i have a feeling it's almost done

[Nitesh]

JM would me adding child rows with the resultid be causing the child data not to save?