Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

Syntax error on Join

Hi.

Can anyone see the syntax error here????

SQL = "SELECT Orders.ordName, Orders.ordAddress, Orders.ordAddress2, Orders.ordCity, Orders.ordState, Orders.ordZip,"&_
    "Orders.ordCountry,Orders.ordShipName, Orders.ordShipAddress, Orders.ordShipAddress2, Orders.ordShipCity, "&_
    "Orders.ordShipState, Orders.ordShipZip, Orders.ordShipCountry, Orders.ordPhone, Orders.ordEmail, Orders.ordID,"&_
    "Orders.ordDate, Orders.ordCNum,Orders.ChargeAcctExp, Orders.OrdTotal, cart.cartQuantity, cart.cartProdID " &_
    "FROM Orders INNER JOIN Orders ON Orders.OrderID = cart.cartOrderID"
0
ClassyLinks
Asked:
ClassyLinks
  • 9
  • 6
  • 6
  • +1
1 Solution
 
jitgangulyCommented:
I guess missing a space after comman on 3rd lien before double quote.

Should be

SQL = "SELECT Orders.ordName, Orders.ordAddress, Orders.ordAddress2, Orders.ordCity, Orders.ordState, Orders.ordZip, "&_
    "Orders.ordCountry,Orders.ordShipName, Orders.ordShipAddress, Orders.ordShipAddress2, Orders.ordShipCity,  "&_
    "Orders.ordShipState, Orders.ordShipZip, Orders.ordShipCountry, Orders.ordPhone, Orders.ordEmail, Orders.ordID, "&_
    "Orders.ordDate, Orders.ordCNum,Orders.ChargeAcctExp, Orders.OrdTotal, cart.cartQuantity, cart.cartProdID  " &_
    "FROM Orders INNER JOIN Orders ON Orders.OrderID = cart.cartOrderID"
0
 
ClassyLinksAuthor Commented:
Sorry, that didn't make a difference.

Do I need to tell it that there are two tables it is selecting FROM??

Orders & Cart??
0
 
jitgangulyCommented:
Ok, then go back to old way

SQL = "SELECT Orders.ordName, Orders.ordAddress, Orders.ordAddress2, Orders.ordCity, Orders.ordState, Orders.ordZip, "&_
    "Orders.ordCountry,Orders.ordShipName, Orders.ordShipAddress, Orders.ordShipAddress2, Orders.ordShipCity,  "&_
    "Orders.ordShipState, Orders.ordShipZip, Orders.ordShipCountry, Orders.ordPhone, Orders.ordEmail, Orders.ordID, "&_
    "Orders.ordDate, Orders.ordCNum,Orders.ChargeAcctExp, Orders.OrdTotal, cart.cartQuantity, cart.cartProdID  " &_
    "FROM Orders,cart where Orders.OrderID = cart.cartOrderID"

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
jitgangulyCommented:
Also I guess you need one space between ampersand and underscore

SQL = "SELECT Orders.ordName, Orders.ordAddress, Orders.ordAddress2, Orders.ordCity, Orders.ordState, Orders.ordZip, "& _
    "Orders.ordCountry,Orders.ordShipName, Orders.ordShipAddress, Orders.ordShipAddress2, Orders.ordShipCity,  "& _
    "Orders.ordShipState, Orders.ordShipZip, Orders.ordShipCountry, Orders.ordPhone, Orders.ordEmail, Orders.ordID, "& _
    "Orders.ordDate, Orders.ordCNum,Orders.ChargeAcctExp, Orders.OrdTotal, cart.cartQuantity, cart.cartProdID  " & _
    "FROM Orders INNER JOIN Orders ON Orders.OrderID = cart.cartOrderID"

0
 
ClassyLinksAuthor Commented:
sorry...same error.

if I add the "cart" in the From  (FROM Orders,Cart)

I get
Cannot repeat table name 'Orders' in FROM clause.
0
 
ETA-TECHNICIANSCommented:
I think you have your syntax mixed up.
You are trying to join incorrctly.  Change the INNER JOIN portion to:

INNER JOIN cart ON cart.cartOrderID = Orders.OrderID
0
 
jitgangulyCommented:
>>I get
Cannot repeat table name 'Orders' in FROM clause.

Could you post me the code. ALso I asked you to put spaces between ampersand and underscore
0
 
ClassyLinksAuthor Commented:
Current code is now:

SQL = "SELECT Orders.ordName, Orders.ordAddress, Orders.ordAddress2, Orders.ordCity, Orders.ordState, Orders.ordZip, "& _
    "Orders.ordCountry,Orders.ordShipName, Orders.ordShipAddress, Orders.ordShipAddress2, Orders.ordShipCity,  "& _
    "Orders.ordShipState, Orders.ordShipZip, Orders.ordShipCountry, Orders.ordPhone, Orders.ordEmail, Orders.ordID, "& _
    "Orders.ordDate, Orders.ordCNum,Orders.ChargeAcctExp, Orders.OrdTotal, cart.cartQuantity, cart.cartProdID " & _
    "FROM Orders INNER JOIN Cart ON  cart.cartOrderID = Orders.OrderID"


Error:
No value given for one or more required parameters.
0
 
jitgangulyCommented:
Check teh column names. Probably one or more does not exists
0
 
ETA-TECHNICIANSCommented:
What do you get when you try:

SQL = "SELECT * FROM Orders INNER JOIN Cart ON  cart.cartOrderID = Orders.OrderID"

If this works, then I would add the specific columns in a few at a time to try and isolate the issue.
0
 
ClassyLinksAuthor Commented:
That still gives a Syntax error in JOIN operation.
0
 
ETA-TECHNICIANSCommented:
What type of DB are you using?
0
 
jitgangulyCommented:
Are the columns names ok in the join ?
0
 
ETA-TECHNICIANSCommented:
I also noticed you name the Order ID in the order table to different things.  During the SELECT portion you call it Orders.ordID while in the JOIN you call it Orders.OrderID.  This discrepancy may be the root of your issue.
0
 
alorentzCommented:
There is no syntax error in the SQL you are posting...perhaps the error is on different code...
0
 
ClassyLinksAuthor Commented:
Ok...
Access Db.


Thanks ETA for the typo.  it is supposed to be: Orders.ordid

So...

The simple version of the query would be

    SQL = "SELECT * FROM Orders INNER JOIN Cart ON ┬ácart.cartOrderID = Orders.OrdID"

But that is still giving me the syntax error.
0
 
ETA-TECHNICIANSCommented:
If I remember correctly, Access doesn't like INNER JOINs - just use a JOIN
0
 
jitgangulyCommented:
I mentioned it to you before
>>Are the columns names ok in the join ?


Try with braces

 SQL = "SELECT * FROM [Orders] INNER JOIN [Cart] ON  [cart].cartOrderID = [Orders].OrdID"
0
 
ETA-TECHNICIANSCommented:
The other thing to be careful with is data types.  You can easily cast in Access by using Int(exp).
Make sure you are comparing the same type of data.
An easy way to test is to open the DB locally and write out the query and debug it that way.
Once you have it working, trasnfer the query into your code and test there.
0
 
alorentzCommented:
What version of Access?
0
 
ClassyLinksAuthor Commented:
:)

I'm not sure how it worked, but it did!!

I changed to:

SQL = "SELECT Orders.*, cart.cartQuantity, cart.cartProdID " & _
    "FROM [Orders] INNER JOIN [Cart] ON [cart].cartOrderID = [Orders].OrdID"

And now it works.

Thank you all for your help.

How do you feel about splitting points?
0
 
jitgangulyCommented:
Well you have used my last suggestion of using braces. I deserve full points here
0
 
jitgangulyCommented:
Thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 9
  • 6
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now