CODEWITHSUNDEEP
vb.netms-access
Domitius
Domitius

Reputation: 159

Performing an inner join

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.

Upvotes: 1

Views: 3926

Answers (3)

Tomalak
Tomalak

Reputation: 338128

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.

Upvotes: 4

ole6ka
ole6ka

Reputation: 220

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)

Upvotes: 0

David Schmitt
David Schmitt

Reputation: 59316

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

Upvotes: 5

Related Questions