Linking Tables Problem RRS feed

  • Question

  • Using C#.


    I'm very new to this environment and need some help please.


    I'm trying to get two tables and two comboboxes to work together:  "tblTank" and "tblTankStatus".  


    tblTank looks something like this:


    AutoID    Tank       Status

    1         Tank1      On

    2         Tank2      Off

    3         Tank3      Off


    tblTankStatus looks something like this:


    AutoID    Status

    1         On

    2         Off

    3         High

    4         Low



    The two Status columns where linked sucessfully in Access. When in Access and  I click in the "Status" column in tblTank, a dropdown appears and I can select from the different status conditions text (but it is linked to the tblTankStatus AutoID). It seems to be working as it should in Access.


    In my project:


    I used the Configure Dataset Wizard to make sure the changes to the tables were loaded in my project.


    It all looks good except I cannot see the links in the Dataset Designer (but other links in other tables are shown). I tried manually linking the two tables in the Dataset Designer, and it showed the link, but that did not help.


    tblTank.Tank is linked to a combobox, and tblTankStatus.Status is linked to another combobox. Both with a DataSouce and DisplayMember. The text appears in both comboxes okay and I can make selections.


    When I change the Tank row in the TankComboBox and then select a status for it in the TankStatusComboBox, the Status selections are not sticking to the Tank when I change Tanks.


    I tried this actual code:


    TankStatusComboBox_SelectedIndexChanged( ... {


    int tankstatus = TankStatusComboBox.SelectedIndex + 1;

    int tank = tblProcesssystemIDBindingSource.Position;

    process_LogDataSet.tblProcesssystemID.Rows[tank]["Status"] = tankstatus;


    // process_LogDataSet.tblProcesssystemID.AcceptChanges();


    The data is saved correctly in the tblTank.Status column, but I really don't like this, and I feel it's wrong. Besides, the proper Status is not coming up in the TankStatusComboBox when I switch Tanks.


    I want to:

    • Select a Tank,
    • then select a Status for that Tank,
    • then select another Tank,
    • then go back to the first Tank and have the previously selected Status appear in the Status combobox (I hope that makes sense).


    A simple problem that I have not been able to solve all day.


    Thanks for any help,






    Sunday, July 1, 2007 12:30 AM

All replies

  • First of all, a dataset is overkill for what you're trying to do. A datareader will work just fine.


    (Also you have to edit the query in designer by right-clicking and selecting configure)


    Secondly the status field is redundant and a little confusing. I think you want to use the ID field instead of a text field.


    You're correct in using the selectedindexchanged event.


    Here's some air code to get you started:


    This query will give you every match you're looking for


    Sub cboTank_SelectedIndexChanged()

       myquery = "SELECT t.Tank, s.Status FROM Tank t JOIN TankStatus s on t.autoid = s.autoid WHERE t.Tank = '" txtTank.text & "'"

       cboTankStatus.Text = DataReader(1)

    End Sub


    Lastly, you want to bind the datasources on comboboxes instead of trying to use a dataset as a catch all approach.




    Sunday, July 1, 2007 1:57 AM