how do i get multiple data from multiple fields

[casualrich]

hi i need help with this, im trying to gather information from 3 tables at once

    Private Sub butinfo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butinfo.Click
        GroupBox3.Visible = True
        Dim dt As New DataTable
        Dim commStr As String = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
                                "Data Source= EngineerJobs01.MDB"
        Dim sqlStr As String = "SELECT ID, EngineerNO, JobNo FROM [Engineers_Allocated},SELECT Surname, Firstname FROM {Engineers}, SELECT CompanyDetails, ProblemDescription FROM [Jobs]"
        Dim DataAdapter As New OleDb.OleDbDataAdapter(sqlStr, commStr)
        DataAdapter.Fill(dt)
        DataAdapter.Dispose()
        Me.DataGridView2.DataSource = dt

    End Sub
End Class

when i run the program it comes up with this please help
DataAdapter.Fill(dt) =Invalid bracketing of name 'Engineers_Allocated},SELECT Surname, Firstname FROM {Engineers}, SELECT CompanyDetails, ProblemDescription FROM [Jobs'.

 

[jmcilhinney]

Well, for a start, look at the different types of brackets you've used. The error message should be a pretty good clue. You've got (square) brackets around one "Jobs", you've got braces (curly brackets) around "Engineers" and you've got one of each around "Engineers_Allocated". It should be fairly obvious that such inconsistency is going to be unacceptable.

Also, I'm not quite sure why you're using any brackets at all. The reason to use brackets is to force identifiers to be recognised as such when they contain reserved words or illegal characters, like spaces. That's not the case for any of those three values you've wrapped in brackets and none of the other values are wrapped in brackets so why use any at all? Either wrap ALL identifiers in brackets or only those that need it. Don't just arbitrarily bracket some. Inconsistency is always a bad thing in code.

As for getting data from multiple tables, if the tables are related you use the appropriate join syntax. The most common is an inner join, where each record from one table is joined with every record in another table where both records share the same value in one or more columns. Usually it's only one column and the value is the primary key in one of the tables. In such a case the column in the second table is called a "foreign key", i.e. a key from a foreign table. E.g.

SELECT ChildID, Child.Name, Parent.Name AS Parent
FROM Parent INNER JOIN Child
ON Parent.ParentID = Child.ParentID

Note that you don't necessarily have to get all columns from either table. Note also that any column name that appears in both tables must be qualified with the table name. Those that are not ambiguous can be qualified but don't need to be. Again, be consistent. Either qualify ALL columns or only those that need it.

 

[casualrich]

right ok so the code you have supplied me, where is says parent ID, do i jsut change it to suite my code?

 

[vks.gautam1]

Join all three tables with commanfields on these tables.

 

[casualrich]

Join all three tables with commanfields on these tables.

right ok will do like say something called "engineer ref"??

 

[vks.gautam1]

like

select EA.*,E.8 from Engineers_Allocated as EA inner join Engineers as E on EA.EngineerNO=E.EngineerNO inner join Jobs j on EA.EngineerNO=J.EngineerNO

 

[cjard]

These things usually get a bit more logical when we lay them out sensibly:

SELECT
  EA.*,
  E.8 
FROM
  Engineers_Allocated as EA 
  INNER JOIN 
  Engineers as E 
  ON
    EA.EngineerNO=E.EngineerNO 

  INNER JOIN Jobs j 
  ON
    EA.EngineerNO=J.EngineerNO 

WHERE
  EA.EngineerNO = 1234

Here i use indentation to show which tables are being joined at each level. Restrictive clauses such as ON and WHERE have their conditions indented. SELECT, FROM, WHERE, GROUP BY, ORDER BY etc define different "areas" of an SQL, and I think of them like paragraph headings, hence they look like headers.