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

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

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.

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

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.

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

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

Quote:

---

Originally Posted by Nitesh

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

---

:thumb: What am I, medieval man? Of course I do. ;)

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.

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.

No problem JM. I can't thank you enough. I know you will find the fault:D. 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:wave:

Quote:

---

Originally Posted by Nitesh

I don't have an internet connection at home

---

Now who's medieval man? :(

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

Quote:

---

Originally Posted by jmcilhinney

:thumb: What am I, medieval man? Of course I do. ;)

---

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

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

Quote:

---

Originally Posted by kleinma

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

---

It wouldn't install on Windows 3.11. :(

You guys are halarious :thumb:

it's good to see that in the forums.

good ones guys:D

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

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:(

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.

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.

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:wave:

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.

Sorry for all the trouble:(

Quote:

---

Originally Posted by Nitesh

Sorry for all the trouble:(

---

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

:eek2: 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.

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:

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.

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:

Code:

---

            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?

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.

Thank you so much for all your effort JM. I really appreciate it:thumb:. 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:

Quote:

---

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.

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.

thanks again JM. :afrog:. I'm so relieved now:)

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.

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.

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

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.

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.

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.

Code:

---

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?

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

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

Code:

---

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:

Code:

---

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:wave:

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

:eek2: im about to pull my hair out. No matter what, the child rows just never get inserted in the database. I changed my code even to test. I removed the code that adds rows programmatically to the child table and added them directly into the datagridview. The resultid gets generated in the datatable but doesn't insert in the db. JM i beg you please help.

JM i've descided to just add a new active/inactive field to my tables rather than delete records. I have no idea what is wrong at the moment. I think as long as no rows are deleted it will work fine. Thanks for all the help.

Sorry I haven't got back to you. I haven't had the chunk of time required to dedicate to this.

no problem JM.you've done more that enough for me already.:wave:

Just to make sure JM. I can have an update statemant as my delete command can't I? for example:

Code:

---

Dim delete As New OleDbCommand("UPDATE Result_Header SET Active=@Active WHERE ID = @ID", con)

 delete.Parameters.AddWithValue("@Active", False)
            delete.Parameters.Add("@ID", OleDbType.BigInt, 4, "ID")

---

Quote:

---

Originally Posted by Nitesh

Just to make sure JM. I can have an update statemant as my delete command can't I? for example:

Dim delete As New OleDbCommand("UPDATE Result_Header SET Active=@Active WHERE ID = @ID", con)

---

Never tried but I suppose it would work.

You need to decide what you're actually doing though. Do you want to delete the DataRow in the DataTable or modify it? If you want to delete it then you wouldn't have an @Active parameter because you'll be setting it to False every time, plus you may have to adjust your @ID parameter to make sure it used the correct SourceVersion. If you want to modify it then the DeleteCommand will never be executed anyway and it's a straight update.

I'm not sure:confused:. What do you think is the better method? it seems fine so far but you understand these things much better than I do

Quote:

---

plus you may have to adjust your @ID parameter to make sure it used the correct SourceVersion

---

how do I do that. I do want to delete the datarow from the datatable.

Quote:

---

Originally Posted by Nitesh

how do I do that. I do want to delete the datarow from the datatable.

---

See if it works as is and, if it doesn't, just change the SourceVersion property of the parameter.

oh my greatness:cry:. not even this method will work. I added a row to the header table and it's id was generated as 36 in the dataset. deleted that row without saving anything. then I added a new row and it's id was 37 in the dataset.

When I saved a new record saved to the parent table as 36 but child records had 37 as the resultid. What am I going to do JM. I'm really stuck now:(

If I don't get a miracle soon my boss might scrap this project:cry:.

I haven't used Access for quite a long time. I'm used to using SQL Server and there's an important difference in their ADO.NET providers that I'd forgotten about. Here's a summary of what I know about master/detail relationships with Access, SQL Server and ADO.NET:

The SqlClient ADO.NET provider for SQL Server supports multiple SQL statements in the one SqlCommand. That means that if you have a command that contains an INSERT statement, you can include a SELECT statement immediately after it to retrieve any values generated by the database, including identity and default values. If you insert by calling ExecuteNonQuery you would retrieve those values using output parameters. If you insert using a DataAdapter you would retrieve the entire record and the entire DataRow will be refreshed.

When you generate a DataTable schema from a SQL Server table, the AutoIncrementSeed and AutoIncrementStep properties of the PK column are set to 0 and 1 respectively, so as to closely resemble those values used by the SQL Server identity column. When you add new rows to your DataTable it will generate temporary values in the PK column. When you save those new rows to the database, the PK values will be updated from the database as described above. When inserting parent records, if the DataRelation is configured to cascade updates, the new PK value is propagated to any new child records before they too are inserted.

The Jet (MDB) and ACE (ACCDB) OLEDB providers do not support multiple SQL statements in the same OleDbCommand, so this means that can NOT retrieve AutoNumber values that get generated when you insert new records. If the data is unrelated you can just re-get all the data but this is no good for related data because you can end up with orphan records in the DataSet.

When you generate a DataTable schema from an Access table the AutoIncrementSeed and AutoIncrementStep properties of the PK column are both set to -1. This ensures that, when you add new records, the PK values generated by the DataTable will never clash with the real values generated by the database. When you save new records to the database the PK values in the DataSet are not refreshed, so the PK values in the DataSet don't match the PK values in the database. This doesn't really matter though, because data integrity is still maintained. The ONLY limitation that I can think of is that you cannot add a new row and save it, then delete it straight away. That's because the ID in the DataSet doesn't match the ID in the database, so the DELETE statement wouldn't affect any records. You'd have to get the data anew from the database before you could delete those new records.

Now, what does this mean for you in this project? Here's what I would suggest. In the database, you should configure each foreign key to cascade deletes. It doesn't matter whether updates are cascaded or not because the PKs will never change once generated. In the DataSet you should set the DataRelations to cascade deletes also. Again, whether or not updates are cascaded doesn't matter because the PKs will never change once generated. You should also set the AcceptRejectRule to Cascade. When it comes time to save data, you simply save all the grandparent data with a single Update call, then do the same for the parent data and, finally, the same for the child data. This should all just work as all the changes that need to be cascaded will be cascaded. If there's any chance that you'll need to delete records that you have just inserted then you should discard all the data and get it all again from the database.

As far as I can see, this should all just work, with a total of three lines of code to save the lot.

Wow JM. You really know your stuff. I will try what you said first thing tomorrow. Would you please be so kind as to give me an example of how the 3 lines of code would look. By the way. I only need to save parent and child changes. I use the grandparent just to filter parent records. so maybe 2 lines of code might be needed. Thank you again for your time.

It would just be two Update calls:

vb.net Code:

parentDataAdapter.Update(parentTable)
childDataAdapter.Update(childTable)

Assuming you have everything configured correctly i think that should take care of everything. Sorry for having given you some probably less than completely useful information previously.

Quote:

---

Sorry for having given you some probably less than completely useful information previously.

---

no need to apologise JM. I have learn't alot from almost all your posts:). Getting the kind of knowledge you have given me is quite difficult. I will get working on these changes now. It's just adding those new datarows programmatically that has put doubt in my mind. Will let you know what happens;)

JM, I've done everything you said. It still appears that the child rows never get inserted with these 2 calls even though I am not adding rows to the child table programmatically, but adding them directly into the datagridview.

Code:

---

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

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

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

---

do you think I'd be better off changing the database to sql server? I've had enough of access. And I also feel having an access db in a multiuser environment is going to give me lots of headaches.

I'm guessing that it's the AcceptRejectRule that's the problem. The issue would be that, if you have AcceptRejectRule set to Cascade then AcceptChanges would be called on the Added and Modified child rows when the parent rows are saved, so they never get saved. If you set AcceptRejectRule to None then the Deleted child rows won't be removed from the child DataTable when the parent rows are saved. That means that an attempt to delete the deleted child records will still be made. That's not a problem though because the operation won't throw an exception. It will simply not affect any records. Try setting the AcceptRejectRule to None and see if it works then.

Also, you don't need to call AcceptChanges unless the AcceptChangesOnUpdate property of the DataAdapter is set to False.

JM it worked:D. Also now if I try adding a row, then deleteing, and then saving, it throws an exception which says "cannot add new row as related record is required in Result_header". That's good. It adds the parent but the child ones won't be inserted so my datarelation won't be broken. Will do more testing during the course of the day. I am adding datarows programmatically to the child table and it seems to be coping.


Thank you so much JM. I could never have done it without your expert help:afrog:. Hope I can return the favour one day

It was a merry dance, but we got there in the end. :)

Quote:

---

It was a merry dance, but we got there in the end

---

Yeah, a dance I will never forget. I am very grateful you stuck with me:bigyello:

Hi JM,

Sorry to bring this old thread back to life. I've been busy on other projects. The client is still having this issue. Please advise me. I have no idea what to do. should i change the way the forms work or should i use sql server as my database.