Video: How Do I: Update Related Tables?

Presenter:Beth Massi


Visual Basic programmers will learn how to properly control updates, inserts and deletes in multi-user applications on forms that edit master-detail related data.

Click here to go to original video page


Minute 0
My name is Beth Massey and say I m going to show you how to update related tables when working with data sets and cable adapters on forms and allow simultaneous ad updates and deletes it s important to control the update ordering a situation if your database implements referential integrity between its parent child tables referential integrity keeps our data consistent in the database and basically means that it will not allow us to delete a parent row without deleting any of its children first and conversely we cannot insert child rows without an existing paragraph I didn t show you how to create a form that handles this scenario
Minute 1
Earn orders and products what I wanted you a show you how we can control the update order of orders and its child order detail when his referential integrity setup between these tables it s important to control the update order if are going to allow at updates and deletes on the form at the same time so let s open up this child table and take a look at the database and how it s set up so Mr right click and go to open table definition and this will open up the order details table if we take a look at this field here order ID we can right click select relationships and you ll see that there is a relationship between the orders in the order detail on that key if we expand this tables and columns specification you can see that information here so here we go we have any relationship between orders in order detail on the order ID and this is referential integrity
Minute 2
Okay so I wanted you is I want to create a form that allows us to batch edit orders and order details so the first thing of any dailies connect to a database on is going to go to the data menu add new data source select databases are data source type annual notice it takes up the database that we were working with in our server Explorer and we ve rented this before or so you can check out previous videos for detailed explanation of the data source configuration wizard but I m just going to select order and order detail in the McCall this order s data set and click finish and this will set up our table adapters which connect directly to a database and the data set with two related data tables let s double click on the access the man with a soundly here and you can see it here is our order and order detail in a red relationship directly from the database select I take a look a little bit this relationship I m going to write
Minute 3
On the relationship and say any relation this will open up this relation dialog it ll show that it s related on the order ID value noticed though that by default the data set designer hero selects this as a relation now might mean it s not going to enforce referential integrity here inside of the data set itself what we wanted to do is actually enforce factory constraints and tag it a lot easier to work with the data sets when it s just a relation only on the forms but if you need this come specific referential integrity we need to enable that by selecting this option if they were going to need to enable here so that our updates from work correctly these were going to want to set the update role to cascade and the delete rule to cascade and what this means is that when we see a hearing and a child row into the database deep here it s Tom unique
Minute 4
He is going to end up becoming a foreign key in the child and when we do is update role we tell the data set that it s going to need to update to the child s for an key when the parents primary key is updated by the database I so remember when we update rows inside a database it s going to populate an identity key for us on those parent routes in the child Rosa going to need that identity okay so it s really in Portland were inserting new rows to come enable this update will cascade delete will just means that if we delete the parent to child rows will delete and this is it is as controlling what s happening on the data set side on the client here okay so targeted your to set that up just like that and click okay okay so if you go back over to the form and I m going to show the data sources window now if you don t see the data sources window dismembered book to data menu to say show data sources in ICU
Minute 5
Data set sitting here so I want to do is I m interacting orders the details onto the form one here in about their initial set up by binding source for us and our binding navigator and in what I want to do is drop the order details of related one here and a cheese select a related order detail raised in a drop that is a creative onto the form here and I m going to raise this up just a little bit and were going to set this anchor property and then I m going to just resize the form just a little bit looks little bit better but a great finale got our binding source is in or table adapters all set up here on the forum now when we do then is evil noticeably right click and go to view code ill see that the the designer generated some code for us and so wrote two lines of code now what we need to make sure in the case because we set
Minute 6
Our data set to enforce referential integrity is hurt in a need to watch out about how we fill this case were new needed filled appearance first so it s important that we re going to need to move this line okay so that the pairings are filled before the child thing is that we can t shall fill the children first because referential integrity dictates that we ve must have appearance if we have a child and so that s the key here sorted you that it s honestly say that I so let s just run this and just make sure that it fills properly and just hit F5 mice of the egos of filled properly enough we had it the other way around we would attack on an error by right okay so now before we condemn start right in our state code is another thing we re going to have to deal
Minute 7
Because we have a relationship and a constraint now set between our tables and not only do we have to fill in the proper order apparent how the order we also need to make sure that we can mix the route to the parent on the table before we can enter any children I saw this really means is we need to call end and it s on the orders binding SARS before we even enter any children and that s because were enforcing constraints and we have a relationship and a foreign key constraint specified now a day so let s go ahead and I m hit F5 again and I ll show you what I mean exactly so we enter a new row tank and ridges and enters for instance e mail about the sixth face we have apparent now what this means is believe how this row inside of the orders binding SARS but it hasn t been are pushed the changes have been pushed to the data set and gay and accidentally call and at simply leave it like this in his state and then we start entering children face a
Minute 8
Enter a row here when I move off this relevant grade will push this wrote to the data set unfortunately we don t have the parent inside a data set yet so when I click on his new row you ll see here we get an error tank and this is because we try to enter a child into this data set without a corresponding row and asking it rather confusing okay so by default a that s why a foreign key constraint isn t automatically set up for you because it s difficult to deal with Palm foreign key constraint lower editing so what we really need to do is we need to call and edit on our parent before we start entering a child and so there s a variety of ways we can do this so when you do is I m just going to handling events on our orders detailed data greenfield is going to use the enter event so anytime you go near the dread on it will commit the parent to the data set
Minute 9
Palms on the diet orders binding source and that it contained as we want to call okay so that s go ahead and let s also make a change to our form because we don t want to allow the users to ever modify primary keys so I m just going to go ahead and come make this the only true and it will just go into our grade and choose to edit the columns and in the order detail ID when a make sure that it s read only to already passages make sure and read only true on the foreign key as well okay I itself valued at that setup mind in okay and now it s trying right and we should be able to now I m entered children properly suffice it at and customer ID and now we can go ahead and start entering the
Minute 10
Entire data set up in okay so now we re ready to go ahead and write or save routine that will properly update the database in its correct order as we always want to send ads and updates the database in Kerry Child order but when we send elites to the database you want to make sure we send them in child care in order because Joyce had to delete the children before the parents I is illustrated at go and doubt one thing to note on this technique it s just particular to using the data great deal here okay so easy calling and add it out before you and your children into your data set totally depends on how you designed your form also you need to make sure that the parent has validated and it before you call and that it okay so let s go ahead and start writing up our Savior team saw me go back up to this click event of the save button and my points remove this call
Minute 11
On TV update method of the orders table adapter because we need to also save the children and also do that and a specified order so I was going to create a function called safe antenna return true or false so private function save as Boolean and will return true if it saved properly and false if it did not so let s just create a local variable here called saved as Boolean was false and that sort in a return tank case so let s get a little room here scroll down a case out what it is first is check if there s any changes all because if there aren t any changes in the orders data set and there is absolutely no reason to say that to the database so has changes can check that if he is fall if it has changes and go ahead and run our code and otherwise were
Minute 12
Just return false from their okay so now here s where we need to grab just be added or updated rows and then update the database with that and then grab just deleted rows and then send a database that doubts a case so that serenity was rich control the update order here so first I m going to create a with an array of data rows on call to order updates contain missile container parents as data room tank and to get these were going to use a method on the table on the table so first we get the orders data set areas order s tank died in his view into a table and there s a select method on here came in the select method allows us to accede do a few things we can select just the filter of routes and gay and we can ask a sort them at the same time but
Minute 13
I don t apply any specific outfield filter or sort what I m really interested in is based out record states okay so what I wanted to you is I want to find the record states that are added or modified current a case that means we updated or inserted some routes and a solo act will return to the reference is inside of its orders data table the well references on so that when you send into the database and the database makes updates for instance 2 D on identity keys those are automatically populated back into your Palm order into your data table in this case the orders data table myself out it s really important to use the select method in this case a day so please not getting a copy of the rows are getting actual row reference is so weird it is same thing hors d oeuvre wouldn t call this a because detail updates
Minute 14
As Diderot and K need out orders data set now we want the orders detail order detail select the tank same thing added or modified current day smelling of the parents and at any children I still detail rocks are they okay say the parents and the children in Nice are rated arose and so now we can do is we can now send the orders table at Africa s regarding the sending of parents in first we do updates or and it adds so any consistency update and let the update takes and is on the motives he can take a whole table they can take a data set or it can take a data row or QT can take an array of data routes and that s exactly the overload her baby using so we can send in our order updates here they
Minute 15
So that said it sends in the parents and now we can send in the children by saying a word detail that s the order detail table adapter and there is update and or Dennis ended in a detail updates can so that go ahead in that that went ahead and that sends that he ads in updates to the database again Kerry Child order that s the key to sell it now into the exact same thing except BDE Dataview row say we select is going to be deleted now so that go ahead and say weakens call list in order to leaves it s another array of data rows equals me orders orders data set orders select
Minute 16
Came and we don t need a filter or ask us sort we just want Dataview row state deleted tank and we do the same thing here has detail deletes as a row and closed me orders order detail up sorry orders data set excuse me order detail select and we want deleted or a peso now we can do is they need God
Minute 17
Or which will update the parent rows or delete them in this case and send order order deletes a gang stuff we get here in state equals true today and at what we should do like all good code should have a try catch around it so if we had into any issues we can report them to the user in a much nicer way sound is going to put that rap that around our state here and if it doesn t work will just for this example put up the exception okay so that s how we do our Lord St Sophia move is another way you can take a look at the code ideas here that we are from grabbing the role references and were making sure to send the ads and updates in first in ED parent
Minute 18
Out order and they were grabbing the role references in deletes and with any not in child care and order a pay and so that s how we can control all this so to call it I will just after we end and it will also want to call and edit on E Tom orders order detail of biting source here is an edit today and then we ll just want to say if me save it we can go ahead and put up the MessageBox and take the orders were saying okay so let s go ahead and not hit F5 and tries out so i as I consider solid snake and update for instance he can say quantity six and
Minute 19
Seven dollars and then I ll go ahead and go the very last row and I ll delete that order and then we can go ahead and add a new one and the Canseco s 91 and pick a product ID 122 Priceline it existed different product there you have a case and I think I had impressed safe and this is yours resaved so if it everything in the proper order so the database did not throwing exceptions and is exactly what we wanted so this is how you save related data tables so I d like to thank you for your time if you like to check out the Visual Basic developer Center will be posting updates these videos there please also check out the Visual Basic team blog and my blog for more information basic