inner join syntax error

[ice_531]

hey all.

i've gone over various threads with the error and most end up being placement of brackets, etc.

I can't seem to figure this out, and i'm sure its really simple.

First off, this is going to an Access Database that has linked tables tied to an ORACLE DB.


So here is my SQL string that is throwing the error.

strSQL = "SELECT SYSTEM.EMAIL" & _
"FROM CUSTOMER INNER JOIN SYSTEM" & _
"ON CUSTOMER.ID = SYSTEM.ID" & _
"WHERE (((CUSTOMER.STATE) = '" & mySt & "')) "

It;s basically pulling an email address based on the state in the customer table matching mySt variable. using ID's as the comparison keys, or primary key if you will.

I tried the following also, with no success.

FROM CUSTOMER INNER JOIN (SYSTEM ON CUSTOMER.ID = SYSTEM.ID)

not really sure why the error...since i pulled the syntax directly from SQL view in the access database, after creating a query in design view. only difference is that i added in a variable mySt , instead of picking one myself. i've also viewed the mySt variable just before the sqlStr gets ran into a record set, and its contents matches fine.


any ideas?

[jggtz]

IMO 'SYSTEM' is a problem name or a reserved word, try using brackets around it []
Also, visit --> http://allenbrowne.com/AppIssueBadWord.html#S

JG

[ice_531]

Still having the same problem, even if changing the table name for system. e.g. (SYSTEM_ROLE)

It's odd, since the exact syntax is working fine inside Access itself. Yet from my VB6 program, it is throwing the inner join syntax error.

[Venus]

Put a space before each double-quote. I don't know if you put it on multiple lines for demonstration purposes on this board or it's all on one line in code but the way you have it written, would translate into
SELECT SYSTEM.EMAILFROM CUSTOMER INNER JOIN SYSTEMON CUSTOMER.ID = SYSTEM.IDWHERE (((CUSTOMER.STATE) = '" & mySt & "')) " the ones in bold are missing spaces.

Try

strSQL = "SELECT SYSTEM.EMAIL " & _
"FROM CUSTOMER INNER JOIN SYSTEM " & _
"ON CUSTOMER.ID = SYSTEM.ID " & _
"WHERE (((CUSTOMER.STATE) = '" & mySt & "')) "

[Hack]

I would take it one step further, and "stretch" out the string to multiple lines

strSQL = "SELECT SYSTEM.EMAIL "
strSQL = strSQL & "FROM CUSTOMER "
strSQL = strSQL & "INNER JOIN SYSTEM ON CUSTOMER.ID = SYSTEM.ID " 
strSQL = strSQL & "WHERE (((CUSTOMER.STATE) = '" & mySt & "')) "

Now it is a bit easier to read. Those spaces mentioned are EXTREMELY important.

Now I'll take it two steps further and say you really shouldn't be using Inline SQL anyway. Look into parametized queries.

[ice_531]

I would take it one step further, and "stretch" out the string to multiple lines

strSQL = "SELECT SYSTEM.EMAIL "
strSQL = strSQL & "FROM CUSTOMER "
strSQL = strSQL & "INNER JOIN SYSTEM ON CUSTOMER.ID = SYSTEM.ID " 
strSQL = strSQL & "WHERE (((CUSTOMER.STATE) = '" & mySt & "')) "

Now it is a bit easier to read. Those spaces mentioned are EXTREMELY important.

Now I'll take it two steps further and say you really shouldn't be using Inline SQL anyway. Look into parametized queries.

This was the problem.

mySt variable needs to contain double quotes around it inside the sql string. Code above ends up sending the following in the string:

WHERE (((CUSTOMER.STATE) = 'GA' ))

I'm now reading up on the parametized queries from threads on the forums here.

Still curious though, how would I include the double quotes in that statement using my current format. '"" mySt ""' and " ' " turns it into a comment.

I definitely see what using parameter based queries would be helpful though!

[Hack]

You wouldn't use quotes.

You would use the @ sign and variable or control name

[ice_531]

You wouldn't use quotes.

You would use the @ sign and variable or control name

Thanks.

Are there any other good resources to read here on the parameter queries.

I've found a few with great examples.

http://www.vbforums.com/showthread.php?t=548787

http://www.vbforums.com/showthread.php?t=381391

Always picky on outside sources found via google.