How to link multiple tables

[vcharles09]

Hi,

Can someone please help fix the following codes,I'm using VS2008: The project can be found at:

https://download.yousendit.com/Z01QZm1lZDVsamZIRGc9PQ

Code1:

Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\AOP2.mdb"
Dim objConnection As New OleDb.OleDbConnection(ConnectionString)
Dim objDataAdapter5 As New OleDb.OleDbDataAdapter("SELECT LINK_TABLE.LINK_ID, LINK_TABLE.NATION_ID, LINK_TABLE.PROP_ID, LINK_TABLE.NMN_ID, " _
& "NATIONS.NATION, PROP.DESCRIPTION, NMN.DESCRIPTION FROM ((LINK_TABLE INNER JOIN NATIONS ON " _
& "LINK_TABLE.NATION_ID = NATIONS.NATION_ID) INNER JOIN PROP ON LINK_TABLE.PROP_ID = " _
& "PROP.PROP_ID) INNER JOIN NMN ON LINK_TABLE.NMN_ID = NMN.NMN_ID WHERE LINK_TABLE.NATION_ID like " & "'%" & C1TrueDBGrid13.Columns(0).Value & "%'" & "", objConnection) 'where country in(SELECT COUNTRY FROM AOP5 WHERE SN like " & "'" & result & "'" & ")order by country", objConnection)
'dataset object
Dim objDataSet As New DataSet

objDataAdapter5.Fill(objDataSet, "Na")*** Error Line
'set dgv
C1TrueDBGrid2.DataSource = objDataSet
C1TrueDBGrid2.DataMember = "Na"
C1TrueDBGrid2.Splits(0).HighLightRowStyle.BackColor = Drawing.Color.Blue
C1TrueDBGrid2.Splits(0).HighLightRowStyle.ForeColor = Drawing.Color.White
C1TrueDBGrid2.AlternatingRows = True
C1TrueDBGrid2.CaptionStyle.BackColor = Color.Blue
C1TrueDBGrid2.CaptionStyle.ForeColor = Color.White
Me.C1TrueDBGrid2.Splits(0).DisplayColumns(0).Width = Me.C1TrueDBGrid13.Splits(0).DisplayColumns(0).Width - 100
Me.C1TrueDBGrid2.Splits(0).DisplayColumns(1).Width = Me.C1TrueDBGrid13.Splits(0).DisplayColumns(1).Width - 55


Error:

Syntax error (missing operator) in query expression 'N.NATION_ID = L.NATION_ID INNER JOIN PROP AS P ON P.PROP_ID = L.PROP_ID NNER JOIN NMN AS M ON M.NMN_ID = L.NMN_ID'.



Code2:


Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\AOP2.mdb"
Dim objConnection As New OleDb.OleDbConnection(ConnectionString)
Dim objDataAdapter As New Data.OleDb.OleDbDataAdapter("SELECT L.LINK_ID, N.NATION, P.DESCRIPTION, M.DESCRIPTION " & _
" FROM LINL_TABLE AS L " & _
" INNER JOIN NATIONS AS N" & _
" ON N.NATION_ID = L.NATION_ID " & _
" INNER JOIN PROP AS P " & _
" ON P.PROP_ID = L.PROP_ID " & _
" NNER JOIN NMN AS M " & _
" ON M.NMN_ID = L.NMN_ID " & _
" WHERE L.NATION_ID LIKE " & "'%" & C1TrueDBGrid13.Columns(0).Value & "%'" & "", objConnection)
Dim objDataSet As New DataSet

'fill dataset
objDataAdapter.Fill(objDataSet, "Chapter") *** Error Line
C1TrueDBGrid2.DataSource = objDataSet
C1TrueDBGrid2.DataMember = "Chapter"
C1TrueDBGrid2.Splits(0).HighLightRowStyle.BackColor = Drawing.Color.Blue
C1TrueDBGrid2.Splits(0).HighLightRowStyle.ForeColor = Drawing.Color.White
C1TrueDBGrid2.AlternatingRows = True
Me.C1TrueDBGrid2.Splits(0).DisplayColumns(0).Width = Me.C1TrueDBGrid2.Splits(0).DisplayColumns(0).Width - 100


Error:
No value given for one or more required parameters.


The following code works for two tables, but I need it to work for multiple tables:


Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\AOP2.mdb"
Dim objConnection As New OleDb.OleDbConnection(ConnectionString)
Dim objDataAdapter As New OleDb.OleDbDataAdapter("SELECT LINK_TABLE.LINK_ID, NATIONS.DESCRIPTION,PROP.DESCRIPTION FROM (NATIONS INNER JOIN Link_Table ON NATIONS.Nation_ID = Link_Table.Nation_ID)INNER JOIN PROP ON Link_Table.PROP_ID = PROP.PROP_ID WHERE LINK_TABLE.NATION_ID like " & "'%" & C1TrueDBGrid13.Columns(0).Value & "%'" & "", objConnection)
Dim objDataSet As New DataSet
objDataAdapter.Fill(objDataSet, "Chapter")
C1TrueDBGrid2.DataSource = objDataSet
C1TrueDBGrid2.DataMember = "Chapter"
C1TrueDBGrid2.Splits(0).HighLightRowStyle.BackColor = Drawing.Color.Blue
C1TrueDBGrid2.Splits(0).HighLightRowStyle.ForeColor = Drawing.Color.White
C1TrueDBGrid2.AlternatingRows = True
Me.C1TrueDBGrid2.Splits(0).DisplayColumns(0).Width = Me.C1TrueDBGrid2.Splits(0).DisplayColumns(0).Width - 100


Thanks,

V.

[WolfShade]

Code 1, first thing to look at is:
Syntax error (missing operator) in query expression 'N.NATION_ID = L.NATION_ID INNER JOIN PROP AS P ON P.PROP_ID = L.PROP_ID NNER JOIN NMN AS M ON M.NMN_ID = L.NMN_ID'.

"INNER" is missing the "I" after "L.PROP_ID"

Code 2, same thing, but use an output to see if the variables are actually being filled.

Code 3, I'd remove the parenthesis from the following, and make sure spacing is proper (there appears to be no space between the ending paren and "INNER"):

"FROM (NATIONS INNER JOIN Link_Table ON NATIONS.Nation_ID = Link_Table.Nation_ID)INNER JOIN PROP"

Lemme know if this helps.

^_^

[vcharles09]

Hello,

Thanks for the suggestion, I made the changes but I'm still getting a syntax error, can you please download the sample project to have a better look at the issue.

https://download.yousendit.com/Z01QZm1lZDVsamZIRGc9PQ

Thanks,

Victor

[WolfShade]

Sorry.. was in a bit of a rush, earlier. You've got way too many parenthesis in the query, and TWO 'where' clauses. I've replaced the second 'where' with an 'AND', removed some of the parens, and abbreviated table names. I think this should help. Make sure your other queries aren't as badly formatted.


SELECT lt.LINK_ID, lt.NATION_ID, lt.PROP_ID, lt.NMN_ID,
n.NATION, p.DESCRIPTION, NMN.DESCRIPTION

FROM LINK_TABLE lt INNER JOIN NATIONS n ON lt.NATION_ID = n.NATION_ID
INNER JOIN PROP p ON lt.PROP_ID = p.PROP_ID
INNER JOIN NMN ON lt.NMN_ID = NMN.NMN_ID

WHERE lt.NATION_ID like '%" & [VARIABLE] & "%'
AND country in (SELECT COUNTRY FROM AOP5 WHERE SN like '" & [VARIABLE] & "')

order by country
Oh, yeah, and don't forget to make sure you put either a "table_name." or "abbv." for "country" in the WHERE clause and ORDER BY clause; I would have, but I don't know what table it's coming from.

^_^

[vcharles09]

I tried your suggestions and all other options I can think of, but it still doesn't work, can you please test the codes you suggested in the sample application.

Thanks,

V.

[vcharles09]

Hello,

It worked with the following syntax:


Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\AOP2.mdb"
Dim objConnection As New OleDb.OleDbConnection(ConnectionString)
Dim objDataAdapter5 As New OleDb.OleDbDataAdapter("SELECT LINK_TABLE.NATION_ID, LINK_TABLE.PROP_ID, LINK_TABLE.ROCK_ID, LINK_TABLE.NMN_ID, " _
& "NATIONS.DESCRIPTION, PROP.DESCRIPTION, NMN.DESCRIPTION, ROCK.DESCRIPTION FROM (((LINK_TABLE INNER JOIN NATIONS ON " _
& "LINK_TABLE.NATION_ID = NATIONS.NATION_ID) INNER JOIN PROP ON LINK_TABLE.PROP_ID = " _
& "PROP.PROP_ID) INNER JOIN ROCK ON LINK_TABLE.ROCK_ID = ROCK.ROCK_ID) INNER JOIN NMN ON LINK_TABLE.NMN_ID = NMN.NMN_ID WHERE LINK_TABLE.NATION_ID like " & "'%" & C1TrueDBGrid13.Columns(0).Value & "%'" & "", objConnection) 'where country in(SELECT COUNTRY FROM AOP5 WHERE SN like " & "'" & result & "'" & ")order by country", objConnection)
'dataset object
Dim objDataSet As New DataSet

objDataAdapter5.Fill(objDataSet, "Na")
'set dgv
C1TrueDBGrid2.DataSource = objDataSet
C1TrueDBGrid2.DataMember = "Na"
C1TrueDBGrid2.Splits(0).HighLightRowStyle.BackColor = Drawing.Color.Blue
C1TrueDBGrid2.Splits(0).HighLightRowStyle.ForeColor = Drawing.Color.White
C1TrueDBGrid2.AlternatingRows = True
C1TrueDBGrid2.CaptionStyle.BackColor = Color.Blue
C1TrueDBGrid2.CaptionStyle.ForeColor = Color.White
Me.C1TrueDBGrid2.Splits(0).DisplayColumns(0).Width = Me.C1TrueDBGrid13.Splits(0).DisplayColumns(0).Width - 100
Me.C1TrueDBGrid2.Splits(0).DisplayColumns(1).Width = Me.C1TrueDBGrid13.Splits(0).DisplayColumns(1).Width - 55


Thanks for all your Help.

Happy Holidays

V.