Need help on fixing an error on SQL select statment.

Question

0.00/5 (No votes)

See more:

I am trying to join 2 tables and display in a datagrid. I am using Access database and SQL statement. I get a "Syntax error in FROM clause". I can't figure out why.

Access database, 2 tables "tblMeter" and "tblOwner".

What I have tried:

This is what I have.

.CommandText = "SELECT tblMeter.MeterNo, tblMeter.MeterCusAcctNo, tblMeter.MeterOwnerID" &
                                  " FROM tblMeter" &
                                  " JOIN tblOwner" &
                                  " WHERE tblMeter.MeterOwnerID = tblOwner.OwnerID;"

It is probably simple but it alludes me. Thanks in advance.

Posted 30-Jan-20 3:24am

bgcwaterman

Updated 30-Jan-20 3:25am

Add a Solution

Comments

F-ES Sitecore 30-Jan-20 9:26am

Look up the syntax for "JOIN", try to specify the fields that match in the join clause rather than the where.

2 solutions

Solution 1

Try to replace WHERE with ON and see what happens :)
Access SQL: basic concepts, vocabulary, and syntax - Access[^]

Posted 30-Jan-20 3:27am

phil.o

Comments

Maciej Los 30-Jan-20 13:32pm

5!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2020-01-30T03:27:00

Solution 2

Use ON for the join conditions, not WHERE.

VB.NET

.CommandText = "SELECT tblMeter.MeterNo, tblMeter.MeterCusAcctNo, tblMeter.MeterOwnerID" &
               " FROM tblMeter" &
               " JOIN tblOwner" &
               " ON tblMeter.MeterOwnerID = tblOwner.OwnerID;"

Perform joins using Access SQL | Microsoft Docs[^]

Posted 30-Jan-20 3:27am

Richard Deeming

Comments

bgcwaterman 30-Jan-20 9:40am

Thank for all the help. I replaced the WHERE to ON. Still didn't work. I Read the article I noticed they were using INNER JOIN and not just JOIN. So I Replaced the JOIN and it got me past the Error. Now I will have to get it to the Datagrid.

Maciej Los 30-Jan-20 13:38pm

"Now I will have to get it to the Datagrid." - This is another story...
Please, accept Richard's solution (use green button), because it answers your question.
All you need to do now is to use OleDbDataReader Class[^] together with DataTable.Load Method[^]. Then:

DataGridView.DataSource = DataTable1

That's all!

Maciej Los 30-Jan-20 13:32pm

5ed!