How to link multiple tables? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

How to link multiple tables?

Author

Topic

vcharles
Starting Member

1 Post

Posted - 2009-12-22 : 19:20:59

Hello,

Can someone please help fix the following tow code:

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.

queenofcode
Starting Member

6 Posts

Posted - 2010-01-11 : 12:04:54

Hi -

If you have already figured out the problem, then you can ignore this :) Otherwise...

It seems to me that you only have one true error, and it created a snowball effect. Looking at your first 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'.

Notice that the second INNER JOIN is not spelled correctly. Thusly, your SQL query is never being executed, and your DataAdapter never gets the data it needs. Since the DA doesn't get the data, you can't fill the DataSet, hence your next error.

The SQL query in both coding examples has the incorrectly spelled INNER JOIN. Try correcting this issue, and let me know if that fixes it!

<geek> The Self-Proclaimed Queen of Code :) </geek>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources