Results 1 to 7 of 7

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

  1. #1

    Thread Starter
    Member
    Join Date
    Sep 2010
    Location
    Huntsville, AL
    Posts
    62

    Resolved [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.
    Last edited by arcanine; Jul 13th, 2011 at 03:57 PM. Reason: I forgot that was inside a With Statement

  2. #2
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Getting Field Values From a Table Using a Foreign Key

    THis is a basic SQL statement:

    Code:
    Select GRADES.Code, HAZARDTYPES.HazardType
    FROM GRADES
    INNER JOIN HAZARDTYPES
       ON GRADES.HazardType_ID = HAZARDTYPES.ID
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  3. #3

    Thread Starter
    Member
    Join Date
    Sep 2010
    Location
    Huntsville, AL
    Posts
    62

    Re: Getting Field Values From a Table Using a Foreign Key

    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.

  4. #4
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Getting Field Values From a Table Using a Foreign Key

    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).
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  5. #5

    Thread Starter
    Member
    Join Date
    Sep 2010
    Location
    Huntsville, AL
    Posts
    62

    Re: Getting Field Values From a Table Using a Foreign Key

    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.

  6. #6
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Getting Field Values From a Table Using a Foreign Key

    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

  7. #7

    Thread Starter
    Member
    Join Date
    Sep 2010
    Location
    Huntsville, AL
    Posts
    62

    Re: Getting Field Values From a Table Using a Foreign Key

    Quote Originally Posted by kevininstructor View Post
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width