Tutorial :Performing an inner join



Question:

I've trying to do an inner join select statement where I select two fields from a table, and than all the records of a field in a second table that have the same id as the first table.

The code looks as follow:

    Dim conn As OleDbConnection      Dim cmd As OleDbCommand        Public Sub openDB()          rsConn = New ADODB.Connection          rsConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\VFMS_DB.mdb;" & "Jet OLEDB:System Database=Security.mdw", "ADMIN", "1234")      End Sub        Public Function GetProdDetails(ByVal vegeID As Integer, ByRef dsTask As DataSet) As Integer            Dim retCode As New Integer            Dim da As OleDbDataAdapter            Try              Dim i As Integer = 0                openDB2()                da = New OleDbDataAdapter("SELECT [Vegetables Descriptions.Task], [Vegetables Descriptions.Description], [TasksOcc.When] FROM [Vegetables Descriptions] INNER JOIN [TasksOcc] ON [Vegetables Descriptions.DescID] = [TasksOcc.DescID] WHERE [Vegetables Descriptions.VegeID] = vegeID", conn)              da.Fill(dsTask)                retCode = 0                conn.Close()              Return retCode          Catch ex As Exception              MessageBox.Show(ex.ToString, ex.Message, MessageBoxButtons.OK)              retCode = 1              Return retCode          End Try      End Function  

I get a an exception: "invalid bracketing of name [Vegetables Descriptions.DescID]

if I take it out to make it look as follow I get a "Join expression not supported"

        da = New OleDbDataAdapter("SELECT [Vegetables Descriptions.Task], [Vegetables Descriptions.Description], [TasksOcc.When] FROM [Vegetables Descriptions] INNER JOIN [TasksOcc] ON [DescID] = [DescID] WHERE [Vegetables Descriptions.VegeID] = vegeID", conn)  

I tried folowing examples from the net but where unsuccessful.


Solution:1

Lines wrapped for legibility:

da = New OleDbDataAdapter("    SELECT [Vegetables Descriptions].[Task],            [Vegetables Descriptions].[Description],            [TasksOcc].[When]     FROM   [Vegetables Descriptions] INNER JOIN [TasksOcc]            ON [Vegetables Descriptions].[DescID] = [TasksOcc].[DescID]     WHERE  [Vegetables Descriptions].[VegeID] = vegeID  ", conn)  

Every single identifier goes in square brackets, not every complete name.

BTW: Table names with spaces in them are... Well... They are not what I would do. ;-)

EDIT: This is easier on the eye (you need to use square brackets for table identifiers with "non-standard" names only, and you can use aliases):

da = New OleDbDataAdapter("    SELECT d.Task,            d.Description,            t.When    FROM   [Vegetables Descriptions] AS d INNER JOIN TasksOcc AS t           ON d.DescID = t.DescID    WHERE  d.VegeID = vegeID  ", conn)         ''#  ^                   ''#  |                   ''#  /----- Not sure what this does in this query, though.  


Solution:2

Use [Vegetables Descriptions].[DescID] instead of [Vegetables Descriptions.DescID]. Since "Vegetable Descriptions" contains a space, it has to be the only name within [].


Solution:3

Insert this:

da = New OleDbDataAdapter("SELECT Descriptions.Task, Descriptions.Description, TasksOcc.When FROM Descriptions INNER JOIN TasksOcc ON TasksOcc.DescID = Descriptions.DescID WHERE Descriptions.VegeID = " & vegeID, conn)


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »