[RESOLVED] Getting Field Values From a Table Using a Foreign Key

For simplicity, I have a database with two tables, as follows:

Code:

---

/---------------------------------------\
| Table Name: GRADES                    |
|---------------+---------+-------------|
| Column        | Type    | Comments    |
|---------------|---------|-------------|
| ID            | Integer | Primary Key |
| HazardType_ID | Integer | Foreign Key |
| Code          | Text    |            |
\---------------+---------+-------------/

/------------------------------------\
| Table Name: HAZARDTYPES            |
|------------+---------+-------------|
| Column    | Type    | Comments    |
|------------|---------|-------------|
| ID        | Integer | Primary Key |
| HazardType | Text    |            |
\------------+---------+-------------/

---

On my form, I'm trying to display information about a grade. I'm using a DataSet with BindingSources and TableAdapters. Using the tip from here, I'm populating the selected grade's ID, HazardType_ID and Code. Unfortunately, I want to display the the HazardType, and not the foreign key index. I've accomplished this with the following code:

Code:

---

' get the data
Dim data As ProjectDataSet.GradesRow = DirectCast(GradesBindingSource.Current, DataRowView).Row

' fill in form fields
With data
    lblTypeVal.Text = DirectCast(DirectCast(HazardTypesBindingSource.Item(HazardTypesBindingSource.Find("ID", .HazardType_ID)), DataRowView).Row, ProjectDataSet.HazardTypesRow).HazardType
    txtCode.Text = .Code
End With

---

Is there an easier way of doing this? I'm very new to data driven programs, and I feel like I'm missing something very easy that will revolutionize my understanding.

Note, I'm using VB Express 2008 (.NET 3.5) and connecting to a MS Access database.

Thanks.

THis is a basic SQL statement:

Code:

---

Select GRADES.Code, HAZARDTYPES.HazardType
FROM GRADES
INNER JOIN HAZARDTYPES
  ON GRADES.HazardType_ID = HAZARDTYPES.ID

---

Quote:

---

THis is a basic SQL statement:

---

I knew it was something simple. Thanks for the help...however, where does this command go? Is it a new binding source? Does is go in the .Filter property? Is it a new query in the DataTable? If so, how do I access different queries?

I know these are probably newbie questions, but that's exactly what I am with this.

Once again, thanks.

I generate my own datasets (table adapters) using code and never use the Wizard. Do I create a connection, open it, Create a Command Object, assign the text to as the SQL Statement, Assign the connection to it then execute (Either fill a datatable or a datareader).

I was doing it this way, but constantly opening and closing connections across our slow network was a huge performance hit. Right now, unless I don't understand whats happening, I fill all of the datatables when the file is opened (one network transaction), then modify the datatables in memory. When the file is saved, the datatables are told to update (another network transaction).

I feel like querying the source database every time a form event that changes data occurs is a huge performance hit. I should note, though, that the source database is an MS Access file on our server. So perhaps that's where the performance issues are coming from.

Try the attached VS2008 demo which needs your database placed into the Debug\Data folder Also the following needs to be changed to your database name. Hopefully this might give you some idea to use.

Code:

---

Private DatabaseName As String = "Data\HazardGrades.mdb"

---

The SQL is from Garry with one modification as shown below

Code:

---

        cmd.CommandText = _
        <SQL>
            SELECT
                GRADES.Code,
                HAZARDTYPES.HazardType,
                HAZARDTYPES.ID
            FROM
                GRADES INNER JOIN HAZARDTYPES ON GRADES.HazardType_ID = HAZARDTYPES.ID
        </SQL>.Value

---

The id field is not displayed as per

Code:

---

dt.Columns("ID").ColumnMapping = MappingType.Hidden

---

Work with the ComboBox when it comes up which is populated as follows

Code:

---

cmd.CommandText = _
<SQL>
    SELECT
        ID, HazardType
    FROM
        HAZARDTYPES
    UNION ALL (SELECT TOP 1 0, "No Filter" FROM HAZARDTYPES)
    ORDER BY ID
</SQL>.Value

Dim dtTypes As New DataTable

dtTypes.Load(cmd.ExecuteReader())
cboHazType.DisplayMember = "HazardType"
cboHazType.ValueMember = "ID"

---

Then in SelectedIndexChanged of the ComboBox

Code:

---

If CInt(cboHazType.SelectedValue) = 0 Then
    bsData.Filter = ""
    If DataGridView1.Columns.Contains("HazardType") Then
        DataGridView1.Columns("HazardType").Visible = True
    End If
Else
    bsData.Filter = String.Concat("ID = ", cboHazType.SelectedValue.ToString)
    DataGridView1.Columns("HazardType").Visible = False
End If

---

Quote:

---

Originally Posted by kevininstructor

Try the attached VS2008 demo...

---

I don't know how to get the attached demo.

Nonetheless, I figured out where to use the INNER JOIN in my project. When viewing a typed dataset in design view, I can click on the table adapter for any table in the dataset, and change the "CommandText" property to include the INNER JOIN you mentioned. VS includes a design time query builder to accomplish this rather easily.

Thanks for nudging me in the right direction!