SQL Statement works in Access Database but not in VB .. 
Author Message
 SQL Statement works in Access Database but not in VB ..

Hi, all,

I had a question, I had a sql statement which works in Access( I mean,
if I design a query in database, it will give me the correct result),
but if I execute this statement in VB program, I get the "join
experession not supported" error.

Can anyone give me some clue..

The sql statement looks like this:

sSQLStatement = "SELECT Parcel.Parcel, Parcel.[Address
Number],Parcel.Fraction,  Parcel.Direction, Parcel.Street, Parcel.[Zip
Code], Parcel.Fraction, Parcel.[Map Number] , Parcel.ID, [CAUV
Land].[Soil Type],  [CAUV Land].Acres, Ownership.[Mail Name],
Ownership.[Total Acres] FROM (Parcel LEFT JOIN [CAUV Land] ON
Parcel.ID=[CAUV Land].[Parent ID] AND [CAUV Land].[Soil Type]='HS')
INNER JOIN Ownership ON Parcel.[Ownership ID]=Ownership.ID"

Thanks,
Jianwei



Sat, 21 May 2005 06:55:12 GMT  
 SQL Statement works in Access Database but not in VB ..
Jianwei,

Not sure if you can use condition in joining part (AND [CAUV Land].[Soil
Type]='HS'). try to move it into WHERE clause.

 sSQLStatement = "SELECT Parcel.Parcel, Parcel.[Address
 Number],Parcel.Fraction,  Parcel.Direction, Parcel.Street, Parcel.[Zip
 Code], Parcel.Fraction, Parcel.[Map Number] , Parcel.ID, [CAUV
 Land].[Soil Type],  [CAUV Land].Acres, Ownership.[Mail Name],
 Ownership.[Total Acres] FROM (Parcel LEFT JOIN [CAUV Land] ON
 Parcel.ID=[CAUV Land].[Parent ID])
 INNER JOIN Ownership ON Parcel.[Ownership ID]=Ownership.ID
WHERE [CAUV Land].[Soil Type]='HS'

--
Val Mazur
Microsoft MVP


Quote:
> Hi, all,

> I had a question, I had a sql statement which works in Access( I mean,
> if I design a query in database, it will give me the correct result),
> but if I execute this statement in VB program, I get the "join
> experession not supported" error.

> Can anyone give me some clue..

> The sql statement looks like this:

> sSQLStatement = "SELECT Parcel.Parcel, Parcel.[Address
> Number],Parcel.Fraction,  Parcel.Direction, Parcel.Street, Parcel.[Zip
> Code], Parcel.Fraction, Parcel.[Map Number] , Parcel.ID, [CAUV
> Land].[Soil Type],  [CAUV Land].Acres, Ownership.[Mail Name],
> Ownership.[Total Acres] FROM (Parcel LEFT JOIN [CAUV Land] ON
> Parcel.ID=[CAUV Land].[Parent ID] AND [CAUV Land].[Soil Type]='HS')
> INNER JOIN Ownership ON Parcel.[Ownership ID]=Ownership.ID"

> Thanks,
> Jianwei



Sat, 21 May 2005 09:04:30 GMT  
 SQL Statement works in Access Database but not in VB ..

Access wraps parens around each join which doesn't always
translate well to T-SQL

the FROM clause is the issue - try:

SELECT p.Parcel, p.[Address Number],p.Fraction,  
p.Direction, p.Street, p.[Zip Code], p.Fraction, p.[Map
Number] , p.ID, c.[Soil Type],  c.Acres, o.[Mail Name],
o.[Total Acres]
FROM Parcel p JOIN OwnerShip o ON p.[Ownership ID] = o.ID
  LEFT JOIN [CAUV Land] c ON p.ID = c.[Parent ID] AND c.
[Soil Type] = 'HS'

ChrisB
MCDBA MCSE
www.MyDBA2000.com

Quote:
>-----Original Message-----
>Hi, all,

>I had a question, I had a sql statement which works in
Access( I mean,
>if I design a query in database, it will give me the
correct result),
>but if I execute this statement in VB program, I get
the "join
>experession not supported" error.

>Can anyone give me some clue..

>The sql statement looks like this:

>sSQLStatement = "SELECT Parcel.Parcel, Parcel.[Address
>Number],Parcel.Fraction,  Parcel.Direction,

Parcel.Street, Parcel.[Zip

- Show quoted text -

Quote:
>Code], Parcel.Fraction, Parcel.[Map Number] , Parcel.ID,
[CAUV
>Land].[Soil Type],  [CAUV Land].Acres, Ownership.[Mail
Name],
>Ownership.[Total Acres] FROM (Parcel LEFT JOIN [CAUV
Land] ON
>Parcel.ID=[CAUV Land].[Parent ID] AND [CAUV Land].[Soil
Type]='HS')
>INNER JOIN Ownership ON Parcel.[Ownership ID]
=Ownership.ID"

>Thanks,
>Jianwei
>.



Sat, 21 May 2005 12:43:40 GMT  
 SQL Statement works in Access Database but not in VB ..

Hi, Thank you for reply, and I do find where it is the error:

if I change my sql statement to :

sSQLStatement = "SELECT p.Parcel,  o.[Mail Name], c.[Soil Type]  FROM
(Parcel AS p INNER JOIN Ownership AS o ON p.[Ownership ID]=o.ID) LEFT
JOIN [CAUV Land] AS c ON (p.ID=c.[Parent ID] AND c.[Soil Type]='HS');"

It worked, so my error is that I didn't include the ( ) on
p.ID=c.[Parent ID] And c.[Soil Type]='HS' before.

Thanks for all the reply, it does help me to figure out..

Jianwei

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sat, 21 May 2005 15:08:56 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Pairwise Sql statement not working in Access ?

2. SQL string not works in VB and works in Access 2000

3. sql statement in vb using access database

4. select into sql statement not working - pls help

5. SQL statement not working

6. sql statements delete and insert not working

7. SQL statement not working!

8. My SQL Statement Just will NOT work

9. String Concatenation Not Working in ASP Building SQL Statement

10. SQL Statement not working.

11. SQL statement works sometimes but not others

12. DELETE SQL STATEMENT NOT WORKING

 

 
Powered by phpBB® Forum Software