• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1859
  • Last Modified:

VB 2012 - SQL Query two tables

Hi,

I'm playing with VB 2012 and SQL 2012.  I have followed this YouTube video to attach to my DataSource:-

http://www.youtube.com/watch?v=bXEkX_Z4bf8&feature=relmfu

I have a SQL database that has (more than) two tables. The tables I am interested in are called ACTIONS and FAULTS.

My current code queries the ACTIONS table for a specific fault number and populates some selected columns in a DataGridView

I'd like to take this a step further, but I don't know how. Both the ACTIONS and FAULTS table have field called FaultID.

What I want to do is populate the DataGridView as I am now, BUT I then want to look up the same FaultID from the FAULTS table, get the data from a field called Symptom and display this text as a label.

Here is my code so far:-

    Private Sub FillByToolStripButton_Click(sender As Object, e As EventArgs) Handles FillByToolStripButton.Click
        If FIDToolStripTextBox.Text = "" Then Exit Sub
        Try
            Me.ACTIONSTableAdapter.FillBy(Me.MySQLDataSet.ACTIONS, CType(FIDToolStripTextBox.Text, Integer))
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
        If DataGridView1.RowCount = 1 Then
            MsgBox("Cannot find Fault ID " & FIDToolStripTextBox.Text, vbExclamation)
        End If

    End Sub

Open in new window

0
Chris Millard
Asked:
Chris Millard
2 Solutions
 
gplanaCommented:
I don't know about .Net, but you can get both tables in a single query by using this SQL syntax:

SELECT *
FROM Actions a, Faults f
WHERE a.FaultID = f.FaultID;

Hope it helps.
0
 
Chris MillardAuthor Commented:
In SQL, your query works, but when I try this:-

SELECT ACTIONS.Faultid FROM Actions a, Faults f WHERE a.Faultid=f.Faultid;

I get this error:-

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "ACTIONS.Faultid" could not be bound.

Also, how would I write this query if I wanted to return the results if the Faultid is 110300 for example?
0
 
Chris MillardAuthor Commented:
Ah - got the query I need:-

SELECT ACTIONS.Faultid, ACTIONS.actionnumber, ACTIONS.timetaken, ACTIONS.nonbilltime, ACTIONS.actioncode, Faults.Symptom FROM dbo.ACTIONS, dbo.Faults where ACTIONS.Faultid = Faults.Faultid AND ACTIONS.Faultid = 110301

However, all of this information appears in the DataGridView - I need Faults.Symptom to be assigned to a text label on my VB form
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
gplanaCommented:
Then put just faults.symptom on the select clause and use a cursor or recordset to get the value and assign it to a variable.
0
 
Anthony PerkinsCommented:
You are using the old deprecated JOIN syntax and as suggested previously, you should get in the habit of using aliases as in:
SELECT  a.Faultid,
        a.actionnumber,
        a.timetaken,
        a.nonbilltime,
        a.actioncode,
        f.Symptom
FROM    dbo.ACTIONS a
        INNER JOIN dbo.Faults f ON a.Faultid = f.Faultid
WHERE	a.Faultid = 110301

Open in new window

0
 
CodeCruiserCommented:
>I need Faults.Symptom to be assigned to a text label on my VB form

For a specific row? Currently selected datagridview row?
0
 
Chris MillardAuthor Commented:
I've got the label showing the symptom value now, but I'm needing help with writing data back to the database.

I've opened a new question at:-
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_27851665.html

If anyone can help!

Thanks in advance
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now