Error with SQL Statement

[victor64]

Hello,

I'm getting a "missing expression" error message from the code below, any ideas what is wrong with the syntax?

Dim objDataAdapter As New OleDb.OleDbDataAdapter("SELECT NATIONS.DESCRIPTION, PROP.DESCRIPTION FROM NATIONS LEFT OUTER JOIN LINK_TABLE on NATIONS.NATION_ID = LINK_TABLE.NATION_ID LEFT OUTER JOIN LINK_TABLE on PROP.PROP_ID = LINK_TABLE.PROP_ID", objConnection)

Thanks

Victor

 

[InertiaM]

You have forgotten to put in the relation to table PROP.

 

[victor64]

Hello,

Below is more detail exolanation of my issue.

I have a Link Table containing the ID field of several tables, how do I load the grid from the Link_Table based on the ID field of several tables loaded to the grid? the goal is to update the Link_Table.

For Example the following 2 tables contain the following data:
Table 1
NATION_ID NATION
1 BEL
2 FRA
3 CAN
4 GBR
Table 2
PROP_ID PROP
1 AAAA
2 BBBBB
3 CCCCCC
4 DDDDDD
Link_Table
NATION_ID PROP_ID
1 1
2 2
4 3
3 4
The grid should be displayed as follows:
NATION PROP
BEL AAAA
FRA BBBBB
CAN CCCCC
GBR DDDDD
If the user deletes the first row, the first row (BEL, AAAAA) from the grid, the first row of the Link table should also be deleted.
Below is my code to view data from table1 linked with the Link_Table, how do I modify the SQL to include multiple tables linked with the Link_Table?
Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\aop.mdb;Persist Security Info=True;Jet OLEDBatabase Password=testaopupdate"

Dim objConnection As New OleDb.OleDbConnection(ConnectionString)

'data adapter
Dim objDataAdapter As New OleDb.OleDbDataAdapter("Select Table1.First from table1 left outer join Link_Table on Table1.First_ID = Link_Table.First_ID", objConnection”)

Dim objDataAdapter As New OleDb.OleDbDataAdapter("SELECT TABLE1.NATION,TABLE2.PROP FROM TABLE1, TABLE2 WHERE TABLE1.NATION_ID = (Select NATION_ID from LINK_TABLE WHERE NATION_ID = TABLE1.NATION_ID) AND TABLE2.PROP_ID = (Select PROP_ID from LINK_TABLE WHERE PROP_ID = TABLE2.PROP_ID)", objConnection)

'dataset object
Dim objDataSet As New DataSet

'fill dataset
objConnection.Open()
objDataAdapter.Fill(objDataSet, "SN") ERROR AT THIS LINE: “ most one record can be returned by this subquery”
objConnection.Close()

'set dgv
C1TrueDBGrid1.DataSource = objDataSet
C1TrueDBGrid1.DataMember = "SN"

What would be the syntax to delete the rows in the Link_Table from C1TrueDBGrid1?
For example deleting the a row of the grid (BEL, AAAAA) should automatically delete the related row in the Link_Table (1,1).
Thanks,
Victor

 

[InertiaM]

SELECT
  Table1.Nation,
  Table2.Prop
FROM
  (
    Table1 INNER JOIN Link_Table ON Table1.Nation_ID = Link_Table.Nation_ID
  )
  INNER JOIN Table2 ON Link_Table.Prop_ID = Table2.Prop_ID

 

[victor64]

Hello,

Thanks for the code, it works for two tables, but when I add a third table, I receive the following error:

Syntax error (missing operator) in query expression 'Link_Table.PROP_ID = table2.PROP_ID INNER JOIN table3 ON Link_Table.NMN_ID = table3.NMN_ID'.

I will be addidng multiple tables (Table4,5,6....) linked with the Link_Table. What is the proper syntax for adding more than two tables?

Code:

SELECT
Table1.Nation,
Table2.Prop,
Table3.Descript
FROM
(Table1 INNER JOIN Link_Table ON Table1.Nation_ID = Link_Table.Nation_ID)
INNER JOIN Table2 ON Link_Table.Prop_ID = Table2.Prop_ID
INNER JOIN Table3 ON Link_Table.NMN_ID = Table3.NMN_ID

Thanks,

Victor

 

[cjard]

SELECT
Table1.Nation,
Table2.Prop,
Table3.Descript
FROM
Link_Table  
INNER JOIN Table1 ON Link_Table.Nation_ID = Table1.Nation_ID
INNER JOIN Table2 ON Link_Table.Prop_ID = Table2.Prop_ID
INNER JOIN Table3 ON Link_Table.NMN_ID = Table3.NMN_ID

It doesnt have to be this way round (notice I altered the order), but it is neater. Keep a copy of this query separate in a tex tfile though, because if you use the dataset tableadapter wizard at any point, it will rewrite the query for you, into non ansi form and it makes a mess