Chris Millard
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:-
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>I need Faults.Symptom to be assigned to a text label on my VB form
For a specific row? Currently selected datagridview row?
For a specific row? Currently selected datagridview row?
ASKER
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
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
ASKER
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?