-
Jul 13th, 2011, 03:54 PM
#1
Thread Starter
Member
[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
-
Jul 14th, 2011, 07:26 AM
#2
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
-
Jul 14th, 2011, 10:31 AM
#3
Thread Starter
Member
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.
-
Jul 14th, 2011, 11:04 AM
#4
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
-
Jul 14th, 2011, 11:14 AM
#5
Thread Starter
Member
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.
-
Jul 14th, 2011, 11:48 AM
#6
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
-
Jul 15th, 2011, 09:43 PM
#7
Thread Starter
Member
Re: Getting Field Values From a Table Using a Foreign Key
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!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|