Link to home
Start Free TrialLog in
Avatar of Chris Millard
Chris MillardFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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

SOLUTION
Avatar of gplana
gplana
Flag of Spain image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Chris Millard

ASKER

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?
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
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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>I need Faults.Symptom to be assigned to a text label on my VB form

For a specific row? Currently selected datagridview row?
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:-
https://www.experts-exchange.com/questions/27851665/SQL-VB-Net-updating-single-table-from-DataGridView-populated-from-multiple-tables.html

If anyone can help!

Thanks in advance