VB 2012 - SQL Query two tables

Posted on 2012-09-02
Last Modified: 2012-09-03

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

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
            Me.ACTIONSTableAdapter.FillBy(Me.MySQLDataSet.ACTIONS, CType(FIDToolStripTextBox.Text, Integer))
        Catch ex As System.Exception
        End Try
        If DataGridView1.RowCount = 1 Then
            MsgBox("Cannot find Fault ID " & FIDToolStripTextBox.Text, vbExclamation)
        End If

    End Sub

Open in new window

Question by:Chris Millard
    LVL 15

    Assisted Solution

    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.
    LVL 17

    Author Comment

    by:Chris Millard
    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?
    LVL 17

    Author Comment

    by:Chris Millard
    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
    LVL 15

    Expert Comment

    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.
    LVL 75

    Accepted Solution

    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,
    FROM    dbo.ACTIONS a
            INNER JOIN dbo.Faults f ON a.Faultid = f.Faultid
    WHERE	a.Faultid = 110301

    Open in new window

    LVL 83

    Expert Comment

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

    For a specific row? Currently selected datagridview row?
    LVL 17

    Author Comment

    by:Chris Millard
    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:-

    If anyone can help!

    Thanks in advance

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now