We help IT Professionals succeed at work.

problem with my sql? error: "SQL command not properly ended"

djdidge
djdidge asked
on
I built a query in Access2000 and have been trying to get it to work in VB. I recieve an oracle driver error error: "SQL command not properly ended". Can anyone help?... All relevant code is below:-


Set connFOL = CreateObject("ADODB.Connection")
Set rsFOL = CreateObject("ADODB.Recordset")
FOL = "Provider=MSDASQL.1;Persist Security Info=False;User ID=XXXX;Password=XXXX;Data Source=FOL;Mode=Read"
connFOL.Open FOL

searchSQL = "SELECT STUDY_BLOCK_ASSOCIATIONS.SBA_SB_REF, STUDY_BLOCK_ASSOCIATIONS.SBA_MAIN_ACTIVITY, " _
          & "STUDY_BLOCK_ASSOCIATIONS.SBA_SA_USER_TEXT_3, STUDY_BLOCK_ASSOCIATIONS.SBA_SA_USER_TEXT_4, " _
          & "STUDY_BLOCK_ASSOCIATIONS.SBA_TYPE, STUDY_BLOCK_ASSOCIATIONS.SBA_CALP_PERIOD_CODE FROM STUDY_BLOCK_ASSOCIATIONS INNER JOIN STUDENTS ON STUDY_BLOCK_ASSOCIATIONS.SBA_S_REF = STUDENTS.S_REF WHERE (((STUDY_BLOCK_ASSOCIATIONS.SBA_SB_REF)='GNVQ2A&D') AND ((STUDENTS.S_REF)='99000756'));"


rsFOL.ActiveConnection = connFOL
rsFOL.Source = searchSQL
rsFOL.CursorType = adOpenStatic
rsFOL.CursorLocation = adUseClient
rsFOL.LockType = 3
rsFOL.Open
Comment
Watch Question

TimCotteeHead of Software Services
BRONZE EXPERT

Commented:
Lose the ; at then end :- ));" should be ))"

Author

Commented:
No change :o(

i'll post the SQL again coz its a bit messed up above

searchSQL = "SELECT STUDY_BLOCK_ASSOCIATIONS.SBA_SB_REF, STUDY_BLOCK_ASSOCIATIONS.SBA_MAIN_ACTIVITY, STUDY_BLOCK_ASSOCIATIONS.SBA_SA_USER_TEXT_3, STUDY_BLOCK_ASSOCIATIONS.SBA_SA_USER_TEXT_4, STUDY_BLOCK_ASSOCIATIONS.SBA_TYPE,
STUDY_BLOCK_ASSOCIATIONS.SBA_CALP_PERIOD_CODE FROM STUDY_BLOCK_ASSOCIATIONS INNER JOIN STUDENTS ON STUDY_BLOCK_ASSOCIATIONS.SBA_S_REF = STUDENTS.S_REF WHERE (((STUDY_BLOCK_ASSOCIATIONS.SBA_SB_REF)='GNVQ2A&D') AND ((STUDENTS.S_REF)='99000756'))"
Nitin SontakkeDeveloper
BRONZE EXPERT

Commented:
Oracle is a black-hole to me.

However, is "&" a reserved character in Oracle?

If so, make it && instead of &.
Nitin SontakkeDeveloper
BRONZE EXPERT

Commented:
I also believe that the semicolon (;) is required at the end, if it is a Oracle query.

Author

Commented:
i've not used & in my query...that was line breaking in VB...sorry for the confusion
Have you forget a the ')' at the end of the statement ?!
-->
WHERE (
(
(STUDY_BLOCK_ASSOCIATIONS.SBA_SB_REF)='GNVQ2A&D'
)
AND (
(STUDENTS.S_REF)='99000756')
);

should be:
WHERE (
(
(STUDY_BLOCK_ASSOCIATIONS.SBA_SB_REF)='GNVQ2A&D'
)
AND (
(STUDENTS.S_REF)='99000756')
));


therefore your line is:
...
INNER JOIN STUDENTS ON STUDY_BLOCK_ASSOCIATIONS.SBA_S_REF = STUDENTS.S_REF WHERE (((STUDY_BLOCK_ASSOCIATIONS.SBA_SB_REF)='GNVQ2A&D')
AND ((STUDENTS.S_REF)='99000756')));"

Best regards
;<)WoK





Nitin SontakkeDeveloper
BRONZE EXPERT

Commented:
Forgive me for this but there is a & in 'GNVQ2A&D'.

To the best of my knowledge, Oracle does look into your values too for the reservered characters and creates problems if they are there.
Change this line in UR code
FOL = "Provider=MSDASQL.1;Persist Security Info=False;User ID=XXXX;Password=XXXX;Data Source=FOL;Mode=Read"

to

FOL = "Provider=MSDASQL.1;Persist Security Info=False;UID=XXXX;PWD=XXXX;Data Source=FOL;Mode=Read"


Author

Commented:
:o( no joy
Guy Hengel [angelIII / a3]Billing Engineer
GOLD EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
hi

The problem is that your SQL statement is on a single line. With the single line i mean that the SQL String is 455 chars long with no crlf caracter in it...

Change your VB code like this:

searchSQL = "SELECT STUDY_BLOCK_ASSOCIATIONS.SBA_SB_REF," _
     & " STUDY_BLOCK_ASSOCIATIONS.SBA_MAIN_ACTIVITY, " _
     & " STUDY_BLOCK_ASSOCIATIONS.SBA_SA_USER_TEXT_3, " _
     & " STUDY_BLOCK_ASSOCIATIONS.SBA_SA_USER_TEXT_4, " _
     & " STUDY_BLOCK_ASSOCIATIONS.SBA_TYPE, " _
     & " STUDY_BLOCK_ASSOCIATIONS.SBA_CALP_PERIOD_CODE " _
     & vbcrlf _
     & " FROM STUDY_BLOCK_ASSOCIATIONS INNER JOIN STUDENTS " _
     & " ON STUDY_BLOCK_ASSOCIATIONS.SBA_S_REF = STUDENTS.S_REF " _
     & " WHERE (((STUDY_BLOCK_ASSOCIATIONS.SBA_SB_REF)='GNVQ2AD') " _
     & " AND ((STUDENTS.S_REF)='99000756'))"

The number of ( and ) is correct...

Cheers

Guy Hengel [angelIII / a3]Billing Engineer
GOLD EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
This makes it also more readable :-)

Author

Commented:
:o( no joy

even when I reduce the complexity of the code (to get some sort of result) the error persits.

searchSQL = "SELECT STUDY_BLOCK_ASSOCIATIONS.SBA_SB_REF," _
    & " STUDY_BLOCK_ASSOCIATIONS.SBA_MAIN_ACTIVITY, " _
    & " STUDY_BLOCK_ASSOCIATIONS.SBA_SA_USER_TEXT_3, " _
    & " STUDY_BLOCK_ASSOCIATIONS.SBA_SA_USER_TEXT_4, " _
    & " STUDY_BLOCK_ASSOCIATIONS.SBA_TYPE, " _
    & " STUDY_BLOCK_ASSOCIATIONS.SBA_CALP_PERIOD_CODE " _
    & vbcrlf _
    & " FROM STUDY_BLOCK_ASSOCIATIONS INNER JOIN STUDENTS " _
    & " ON STUDY_BLOCK_ASSOCIATIONS.SBA_S_REF = STUDENTS.S_REF;"

Author

Commented:
the full error:

Runtime Error '-2147467259 (80004005)':
[Oracle][ODBC][Ora]ORA-00933: SQL command not properly ended
Guy Hengel [angelIII / a3]Billing Engineer
GOLD EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
and without the ;  and maybe another crlf after 3 columns...
Nitin SontakkeDeveloper
BRONZE EXPERT

Commented:
01. I know there will be a different type of error because of this mistake, but could you please modify your following code (if you don't mind) from:

rsFOL.ActiveConnection = connFOL

To

Set rsFOL.ActiveConnection = connFOL

02. Have you tried running the resulted query string (searchSQL) in Oracle environment directly?

This will tell you if there is really an issue with query itself or something else.

Éric MoreauSenior .Net Consultant
BRONZE EXPERT
Top Expert 2016

Commented:
Does Oracle supports this syntax of INNER JOIN ? You should try:

& " FROM STUDY_BLOCK_ASSOCIATIONS, STUDENTS " _
& "WHERE STUDY_BLOCK_ASSOCIATIONS.SBA_S_REF = STUDENTS.S_REF;"

Author

Commented:
NitinSontakke there was no different error when i changed

rsFOL.ActiveConnection = connFOL

To

Set rsFOL.ActiveConnection = connFOL

..........
Emoreau - just checking!

Author

Commented:
this is what i have at the moment, I think that the inner join idea may well be correct. But it doesn't run yet

searchSQL = "SELECT STUDY_BLOCK_ASSOCIATIONS.SBA_SB_REF," _
    & " STUDY_BLOCK_ASSOCIATIONS.SBA_MAIN_ACTIVITY, " _
    & " STUDY_BLOCK_ASSOCIATIONS.SBA_SA_USER_TEXT_3, " _
    & " STUDY_BLOCK_ASSOCIATIONS.SBA_SA_USER_TEXT_4, " _
    & " STUDY_BLOCK_ASSOCIATIONS.SBA_TYPE, " _
    & " STUDY_BLOCK_ASSOCIATIONS.SBA_CALP_PERIOD_CODE " _
    & vbCrLf _
    & " FROM STUDY_BLOCK_ASSOCIATIONS, STUDENTS " _
    & " WHERE STUDY_BLOCK_ASSOCIATIONS.SBA_S_REF = STUDENTS.S_REF " _
    & " AND (((STUDY_BLOCK_ASSOCIATIONS.SBA_SB_REF)='GNVQ2AD') " _
    & " AND ((STUDENTS.S_REF)='99000756')));"
Developer
BRONZE EXPERT
Commented:
That was almost as expected.

Now:

Have you tried running the resulted query string (searchSQL) in Oracle environment directly? Not thru ASP but directly in ANY interface that Oracle might be giving to run the query.
Nitin SontakkeDeveloper
BRONZE EXPERT

Commented:
Please read ASP as your client application which could be VB desktop app, ASP, or any such thing.
BRONZE EXPERT
Top Expert 2012

Commented:
As emoreau has pointed out Oracle does not support the Inner Join syntax.

Anthony

Commented:
Your last version (comment of 6:41 AM PST) _does_ have a ')' to much. Remove the last one, before the ';'

Author

Commented:
ok...now don't I feel like an idiot!.. I don't know the database that well so i was following some querys written by co-workers to formulate this problem query...however I have taken a better look at the structure of the db and now believe that i don't need the join... ie I only need 1 table.

The only question is who deserves the points?... (can't wait for the new point distribution facility from EE!)

Thanks all.

Discuss!
Nitin SontakkeDeveloper
BRONZE EXPERT

Commented:
This also confirms what i keep telling myself again and again.

"If it is going wrong, don't assume ANYTHING. Check EVERYTHING that is involved."

I am interested in knowing only one thing, is your problem solved?

If Yes, i can peacefully, "Unsubscribe" to this question.

Author

Commented:
Thanks for your help NitinSontakke, I have learned something from your posts.

Author

Commented:
My problem is solved thanks!

Author

Commented:
I'm giving you the points NitinSontakke, after reviewing the comments you seemed to off the most suggestions
Nitin SontakkeDeveloper
BRONZE EXPERT

Commented:
Thanks "djdidge"!

That was totally unexpected, especially with my knowledge of Oracle.

As said above, i had unsubscribed to this thread already and was surprised to see the mail in my inbox with the same subject.

Thanks again.

Author

Commented:
it was more of a computing fundamental that your expressed onto me....look hard and deep before asking people!