none
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

    etc.

     

    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;

    tblProcesssystemIDTableAdapter.Update(process_LogDataSet.tblProcesssystemID);

    // 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,

     

    Dennis

     

     

     

    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.

     

    Adamus

     

    Sunday, July 1, 2007 1:57 AM