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

[Nitesh]

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.

[Nitesh]

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.

[jmcilhinney]

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

[Nitesh]

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

[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)

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

[jmcilhinney]

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.

[Nitesh]

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

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.

[jmcilhinney]

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.

[Nitesh]

oh my greatness. 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

[Nitesh]

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

[jmcilhinney]

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.

[Nitesh]

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.

[jmcilhinney]

It would just be two Update calls:

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.

[Nitesh]

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

[Nitesh]

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.

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.

[jmcilhinney]

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.

[Nitesh]

JM it worked. 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. Hope I can return the favour one day

[jmcilhinney]

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

[Nitesh]

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

[Nitesh]

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.

[Nitesh]

JM how can it be that the code works fine most of the time and then after a few adds and deletes it breaks

[jmcilhinney]

No idea.