asked on

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

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

TimCottee

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

djdidge

ASKER

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 Sontakke

Oracle is a black-hole to me.

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

If so, make it && instead of &.

Nitin Sontakke

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

djdidge

ASKER

i've not used & in my query...that was line breaking in VB...sorry for the confusion

WolfgangKoenig

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 Sontakke

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.

gondesiravi

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"

djdidge

ASKER

:o( no joy

Guy Hengel [angelIII / a3]

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]

This makes it also more readable :-)

djdidge

ASKER

: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;"

djdidge

ASKER

the full error:

Runtime Error '-2147467259 (80004005)':
[Oracle][ODBC][Ora]ORA-00933: SQL command not properly ended

Guy Hengel [angelIII / a3]

and without the ; and maybe another crlf after 3 columns...

Nitin Sontakke

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 Moreau

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;"

djdidge

ASKER

NitinSontakke there was no different error when i changed

rsFOL.ActiveConnection = connFOL

To

Set rsFOL.ActiveConnection = connFOL

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

djdidge

ASKER

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')));"

ASKER CERTIFIED SOLUTION

Nitin Sontakke

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nitin Sontakke

Please read ASP as your client application which could be VB desktop app, ASP, or any such thing.

Anthony Perkins

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

Anthony

Jeremy_D

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

djdidge

ASKER

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 Sontakke

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.

djdidge

ASKER

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

djdidge

ASKER

My problem is solved thanks!

djdidge

ASKER

I'm giving you the points NitinSontakke, after reviewing the comments you seemed to off the most suggestions

Nitin Sontakke

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.

djdidge

ASKER

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

problem with my sql? error: &quot;SQL command not properly ended&quot;

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