10 Replies - 1278 Views - Last Post: 04 August 2014 - 05:27 AM Rate Topic: -----

#1 darkkingll   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 42
  • Joined: 04-August 14

Syntax error in inner join

Posted 04 August 2014 - 03:41 AM

Hello,
I'm currently trying to add 3 access db table's to my vb.net code. However, i get a syntax error in the Join statement. I tried googling it, but couldn't find the sollution. Maybe you guys can help?

The database excists out of 3 tables, tbl_voersoort and tbl_rantsoen, which are the main tables and there is tbl_rantsoenkoppel that connects both main tables for redundancy(a rantsoen(ransion?) has to exists out of multiple types of food with different weights for each food)

Private Sub Rantsoen_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\DISKSTATION\Gegevens\Gegevens\Marco\2014\Voerkar\voerkar.accdb")
        Dim cmdcommand As New OleDbCommand
        Dim daAdapter As New OleDbDataAdapter
        Dim dsSet As New DataSet
        Dim sql As String = "SELECT V.Voer, R.Rantsoen, R.Gewicht FROM tbl_voersoorten V " & _
                            "INNER JOIN tbl_rantsoenkoppel K ON R.RantsoenID = K.FKRantsoenID" 'INNER JOIN K ON V.VoerID       = K.FKvoerID "
        'WHERE R.Rantsoen = cbRantsoen.text AND V.Voer = cbVoer.text AND R.Gewicht = tbGewicht.text "

        Dim sqlCom As New System.Data.OleDb.OleDbCommand(Sql)


As you can see, i already tried minimizing it, but it stil gives me the error.



Also, if i'm correcct; i can use this code on formload and then let a dropdown box populate itself from the database? (So the user can select a field, which will then populate the texboxes)

Thanks!

Hmmm, no edit function?
I wanted to add the link to the topic that i used the most to find a answer.
http://www.dreaminco...bles-in-a-form/

Is This A Good Question/Topic? 0
  • +

Replies To: Syntax error in inner join

#2 andrewsw   User is offline

  • Stealth IT
  • member icon

Reputation: 6700
  • View blog
  • Posts: 27,542
  • Joined: 12-December 12

Re: Syntax error in inner join

Posted 04 August 2014 - 03:59 AM

What is the full error message?

This "tbl_voersoort" is not the table-name used in your statement. But if the two tables are joined by a third then this third should also be in the statement.
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw   User is offline

  • Stealth IT
  • member icon

Reputation: 6700
  • View blog
  • Posts: 27,542
  • Joined: 12-December 12

Re: Syntax error in inner join

Posted 04 August 2014 - 04:20 AM

You might also want to open Access and test sql-statements in SQL View first.
Was This Post Helpful? 0
  • +
  • -

#4 darkkingll   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 42
  • Joined: 04-August 14

Re: Syntax error in inner join

Posted 04 August 2014 - 04:22 AM

Hey,
Thanks for the quick response, It should be tbl_voersoorten as in the code.
Also, how do you mean, include the third? a third inner join with tbl_voersoorten and tbl_rantsoen? As is, i have 2 join's, 1 from tbl_voersoorten and tbl_rantsoenkoppel, and 1 tbl_rantsoen and tbl_rantsoenkoppel
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw   User is offline

  • Stealth IT
  • member icon

Reputation: 6700
  • View blog
  • Posts: 27,542
  • Joined: 12-December 12

Re: Syntax error in inner join

Posted 04 August 2014 - 04:31 AM

You only have one join, and you haven't defined what 'R' is an alias of.

Is 'AS' required in Access when creating a table-alias? I cannot recall so you might want to check. Added: No, it doesn't seem to be required, I prefer to include myself.

This post has been edited by andrewsw: 04 August 2014 - 04:33 AM

Was This Post Helpful? 0
  • +
  • -

#6 darkkingll   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 42
  • Joined: 04-August 14

Re: Syntax error in inner join

Posted 04 August 2014 - 04:38 AM

Hello, i had 2 inner joins, but put 1 into comments for troubleshooting ;)
I don't know about AS in Acces, but in Visual Basic 2010 it is needed.
I will work on defining R, but it's supposed to be the tbl tbl_rantsoen (just live K = tbl_rantsoenkoppel)
Was This Post Helpful? 0
  • +
  • -

#7 darkkingll   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 42
  • Joined: 04-August 14

Re: Syntax error in inner join

Posted 04 August 2014 - 04:53 AM

Using this code in Access self gives the follwing error:
SELECT V.Voer, R.Rantsoen, R.Gewicht FROM tbl_voersoorten V, tbl_rantsoen R, tbl_rantsoenkoppel K 
                         INNER JOIN K ON R.RantsoenID = K.FKRantsoenID INNER JOIN K ON V.VoerID = K.FKvoerID 


Error: Syntax error(Operator missing) in R.RantsoenID = k.FKRantsoenID INNER JOIN K ON V.VoerI.
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw   User is offline

  • Stealth IT
  • member icon

Reputation: 6700
  • View blog
  • Posts: 27,542
  • Joined: 12-December 12

Re: Syntax error in inner join

Posted 04 August 2014 - 05:03 AM

Here is an example of joining three tables using aliases:
SELECT A.FirstName, A.Surname, B.FirstDate, C.Department 
FROM tblDepts AS C INNER JOIN 
(tblStaff AS A INNER JOIN tblHolidays AS B ON A.StaffID = B.StaffID) 
ON C.DeptID = A.Department;

Was This Post Helpful? 0
  • +
  • -

#9 darkkingll   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 42
  • Joined: 04-August 14

Re: Syntax error in inner join

Posted 04 August 2014 - 05:16 AM

Ah, thank you!
I used the way displayed in the link in the first post, but will try your way to.
Was This Post Helpful? 0
  • +
  • -

#10 andrewsw   User is offline

  • Stealth IT
  • member icon

Reputation: 6700
  • View blog
  • Posts: 27,542
  • Joined: 12-December 12

Re: Syntax error in inner join

Posted 04 August 2014 - 05:24 AM

That link only attempts to link three tables towards the end, in code that the OP says wasn't working.

Borrowing code from forum questions can be very useful, but you need to read the whole question, and responses, to make sure that the code will be useful and relevant to you... and works.

This post has been edited by andrewsw: 04 August 2014 - 05:27 AM

Was This Post Helpful? 0
  • +
  • -

#11 darkkingll   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 42
  • Joined: 04-August 14

Re: Syntax error in inner join

Posted 04 August 2014 - 05:27 AM

(I still think a EDIT function would be usefull)
Right, In Access i'm not getting any errors using
SELECT V.Voer, R.Rantsoen, R.Gewicht 
            FROM tbl_voersoorten AS V INNER JOIN (tbl_Rantsoen AS R INNER JOIN tbl_Rantsoenkoppel AS K ON K.FKRantsoenID = R.RantsoenID) ON K.FKVoerID = V.VoerID


I think it's solved, will test it with some data but it looks ok now. Thanks :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1