locked
? about cascading updates after lookup table update RRS feed

  • Question

  • (I originally posted this to the data access and general discussion newsgroups but received no replies)

    I've got an Access 2003 mdb that I'm converting to VB.Net.  I'm having
    trouble with getting the main data source to update after updates to a lookup
    table. 

    There are 2 tables with a one-to-many relationship in the mdb.  VB.Net
    recognizes this relationship according to it's dataset designer diagram. 
    Currently I have 2 forms: a main form for data-entry and a form for updates
    to a lookup table.  Data for this lookup data is in a combobox on the main
    form.  When I modify one of the items on the lookup form, the data on the
    main form does not get updated until I've closed the form and reopened it. 
    The data in the lookup table gets modified as expected.

    I assume I have to update the table adapter for the main
    form after saving the changes to the lookup table.  I tried the following
    code after updating the item on the lookup form (not both lines at the same
    time):

           
    Form1.TblPhotosAndImagesTableAdapter.Fill(Me.PhotosAndImagesDataSet.tblPhotosAndImages)

           
    Form1.TblPhotosAndImagesTableAdapter.Update(Me.PhotosAndImagesDataSet.tblPhotosAndImages)

    But the table adapter does not update until I exit the application.  How do
    I update it immediately upon saving the item on the lookup form?  Thanks for
    any help or advice.

    Thursday, November 29, 2007 3:34 PM

Answers

  • You would probably have to explicity start transaction, so you probably don't have to worry.

     

    This seems to be where the problem is:

    Form1.TblPhotosAndImagesTableAdapter.Fill(Me.PhotosAndImagesDataSet.tblPhotosAndImages)

     

    Since I am mostly a Web Forms guy and not Windows Forms, I don't know if I can solve solve. But, it looks like you are reloadeding the TableAdapter, but not rebinding the controls to it. How about something like this: After you call FILL on the adapter, set again the datasource on your binding source

    Form1.bindingSource1.DataSource = Me.PhotosAndImagesDataSet.tblPhotosAndImages
    EDIT: (replace bindingSource1 with the name of your binding source)

    Thursday, November 29, 2007 8:09 PM

All replies

  •  

    I'm having trouble understanding your objective. Are you saying that you have a main form with a coresponding record, and it has a foreign key to another table, which is a simple lookup table? Then, on the UI, you want to be able to edit the actual value of a lookup item text in the combo box, rather than just select a different lookup item for the main record? That seems a little odd from a UI perspective, because changing the value of a lookup item would affect all other related records, not just the one you are working on. I guess that's why most times you have a separate screen to maintain the lookup values.

    Thursday, November 29, 2007 6:06 PM
  •  

    I've done this many, many times in Access with success. I'm trying to duplicate it in VB.Net.  Perhaps I'm not explaining my problem well enough.  This is a photography archive database.  Each record contains data on a picture taken by a photographer.  On the UI, I have a combobox for photographer.  The user chooses the photographer for each record entered.  Many records in this database can have the same photographer.  Using Access, if the user wants to change the photographer name "Duck, Donald" to "Mouse, Mickey", then the user clicks on a button to bring up the photographer lookup table.  The user then navigates to "Duck, Donald" and changes it.  Due to the way the relationship is set up, with cascading updates, all occurrences of "Duck, Donald" get changed to "Mouse, Mickey" automatically w/o the user having to go thru each record and change them manually.

     

    I cannot get all the records in the database updated unless I close the application and reopen.  I would like the update to the lookup table to cascade automatically.

     

     

     

     

    Thursday, November 29, 2007 6:33 PM
  • OK, that makes sense. You are updateing the lookup table in a popup, not in the detailsview iteself. So, you have to manually code the popup, because Windows Forms won't give you that popup datasheet like Access does.

     

    So, let me think through the basics, so I didn't miss anything obvious. I am assuming you have a foreign key, that the primary_key_id is set to the photographer table, and the foreign_key_id is set to the photo table, and you specified cascade-update on the database key.

     

    Here are a few things to think about...

     

    After you update in the popup, you should reload the combobox in the opener, right? Otherwise, the combobox will have the old value, not the new one?

     

    Or, depends how you structured the main form. The dataset for your main form is probably loaded before the user changes the photographer name. So, changes to the photographer table will not be seen, even after the users changes the table. You wouldn't see the new photographer name until you reload the dataset on the main form. Perhaps the only time you reload the main form is when you start the application.

     

    When you are updating in the popup, you are not in some transaction context that is not being committed?

    Thursday, November 29, 2007 7:23 PM
  • Thanks for replying.  I think this is getting closer to being resolved. 

     

    Yes, there is a foreign key in the photo table that relates to the primary key in the lookup table. 

     

    No, I don't reload the combo box.  That's something I need to do.

     

    Yes, the main form is loaded on opening and I understand that it needs to be reloaded when the lookup table is changed.  I attempted to do this as soon as the lookup value was updated and saved on the popup screen.  I have a button on the popup screen to return the user to the main form.  In this button click event I have the following coding in hopes that it would reload the main form (TblPhotosAndImagesTableAdapter is what loads the main form):

     

    Form1.TblPhotosAndImagesTableAdapter.Fill(Me.PhotosAndImagesDataSet.tblPhotosAndImages)

     

    Since this didn't work the way I expected, I tried this coding:

     

    Form1.TblPhotosAndImagesTableAdapter.Update(Me.PhotosAndImagesDataSet.tblPhotosAndImages)

     

    Am I using the wrong syntax?  Am I even in the right place to reload the main form?

     

    I'm sorry, I don't understand your last sentence.  I know about transactions processing in Access but not in VB.Net.

     

    Thanks again for your help on this. 

    Thursday, November 29, 2007 7:57 PM
  • You would probably have to explicity start transaction, so you probably don't have to worry.

     

    This seems to be where the problem is:

    Form1.TblPhotosAndImagesTableAdapter.Fill(Me.PhotosAndImagesDataSet.tblPhotosAndImages)

     

    Since I am mostly a Web Forms guy and not Windows Forms, I don't know if I can solve solve. But, it looks like you are reloadeding the TableAdapter, but not rebinding the controls to it. How about something like this: After you call FILL on the adapter, set again the datasource on your binding source

    Form1.bindingSource1.DataSource = Me.PhotosAndImagesDataSet.tblPhotosAndImages
    EDIT: (replace bindingSource1 with the name of your binding source)

    Thursday, November 29, 2007 8:09 PM
  •  

    Woo-Hoo!  Yes, resetting the datasource did the trick.  Now I need to reload the combobox after updating the lookup table. 

     

    Thanks so much for your help.

    Thursday, November 29, 2007 8:17 PM
  • Glad I could close this out. I've really got a lot to learn about Windows Forms...

    Thursday, November 29, 2007 8:24 PM