Making an internal connection
I am trying to make an inner join select statement in which I select two fields from a table, not all of the field records in the second table that have the same ID as the first table.
The code looks like this:
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 an exception: "Invalid name bracketing [Description Vegetables .DescID]
If I take it to make it look like this, I get "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 using examples from the net, but where failed.
+1
a source to share
3 answers
Lines wrapped for readability:
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 is sent in square brackets, not every full name.
BTW: Table names with spaces in them ... Well ... They're not what I'm going to do .; -)
EDIT: this is easier on the eyes (you need to use square brackets for table IDs with "non-standard" names, 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.
+4
a source to share