I got it working! FINALLY.
Just as an FYI...I didn't realize that SQL can work differently in SQL
Server and Access...which was why I was going so nuts knowing that it DID
work before (when I was accessing Sybase through SQL Anywhere) and now this
same code wasn't working with Access. Various attempts that SQL users said
should work, was giving me "not allowed" errors that was making me crazy
until I learned that the two can work differently.
Once I understood that fact...I decided to go through Access and just use
the Wizard to pull what I needed and then grabbed the resulting SQL from
there. And THAT finally worked. Here's the working code should it possibly
help someone in the future.
gstrSQLQuery = _
"SELECT csdbo_CLIENT.clientid, csdbo_CLIENT.lastname,
csdbo_CLIENT.firstname, " _
& "csdbo_CLIENT.m_address1, csdbo_CLIENT.m_address2,
csdbo_CLIENT.m_city, " _
& "csdbo_CLIENT.m_state, csdbo_CLIENT.m_postalcode,
csdbo_PATIENT.patientid, " _
& "csdbo_PATIENT.classid, csdbo_PATIENT.name, csdbo_PATIENT.sexid,
csdbo_PATIENT.color, " _
& "csdbo_PATIENT.birthdate, csdbo_PATIENT.speciesid,
csdbo_PATIENT.breedid, " _
& "csdbo_OWNER.patientid, csdbo_OWNER.clientid " _
& "FROM csdbo_CLIENT INNER JOIN " _
& "(csdbo_PATIENT INNER JOIN csdbo_OWNER ON csdbo_PATIENT.patientid
= csdbo_OWNER.patientid) " _
& "ON csdbo_CLIENT.clientid = csdbo_OWNER.clientid " _
& "WHERE csdbo_PATIENT.patientid= " & Chr(39) & gstrPatientID &
Chr(39) _
& "AND csdbo_CLIENT.clientid= " & Chr(39) & gstrClientID & Chr(39) &
";"
Dian ~
View Hurricane Humane Updates: http://www.mousetrax.com/pets.html
-----Original Message-----
From: [email protected]
[mailto:[EMAIL PROTECTED] On Behalf Of Dian D. Chapman
Sent: Monday, October 03, 2005 10:27 PM
To: [email protected]
Subject: RE: [ASP] New error...Syntax on JOIN
Further...I ripped it apart and the problem appears to be here..
& "FROM csdbo_CLIENT, csdbo_PATIENT INNER JOIN csdbo_OWNER ON " _
It seems to not like the fact that I've added csdbo_PATIENT, but I think I
even got this code working from someone in here some months ago and it DID
work when it was pulled through the original server data. But now that the
IDs have changed to text, I've had to change these...but this part isn't
really related to that issue, so I don't get it.
I SHOULD be able to pull data from two tables that are joined through a
third, right?<sigh>
Dian ~
View Hurricane Humane Updates: http://www.mousetrax.com/pets.html
-----Original Message-----
From: [email protected]
[mailto:[EMAIL PROTECTED] On Behalf Of Dian D. Chapman
Sent: Monday, October 03, 2005 5:41 PM
To: Group - ASP
Subject: [ASP] New error...Syntax on JOIN
As it goes...I got one error fixed...which moved me on to the next.<sigh>
Any ideas on this one?
Exact error:
"Syntax error in JOIN operation."
Here's the query code:
gstrSQLQuery = _
"SELECT csdbo_CLIENT.lastname, csdbo_CLIENT.firstname, " _
& "csdbo_CLIENT.m_address1, csdbo_CLIENT.m_address2, csdbo_CLIENT.m_city,
" _
& "csdbo_CLIENT.m_state, csdbo_CLIENT.m_postalcode,
csdbo_PATIENT.patientid, " _
& "csdbo_PATIENT.name, csdbo_PATIENT.sexid, " _
& "csdbo_PATIENT.color, csdbo_PATIENT.birthdate, csdbo_PATIENT.speciesid,
" _
& "csdbo_PATIENT.breedid " _
& "FROM csdbo_CLIENT, csdbo_PATIENT INNER JOIN csdbo_OWNER ON " _
& "csdbo_CLIENT.clientid=csdbo_OWNER.clientid " _
& "WHERE csdbo_PATIENT.patientid=" & Chr(39) & gstrPatientID & Chr(39) _
& "AND csdbo_CLIENT.clientid=" _
& Chr(39) & gstrClientID & Chr(39)
Which results in this query string:
SELECT csdbo_CLIENT.lastname, csdbo_CLIENT.firstname,
csdbo_CLIENT.m_address1, csdbo_CLIENT.m_address2, csdbo_CLIENT.m_city,
csdbo_CLIENT.m_state, csdbo_CLIENT.m_postalcode, csdbo_PATIENT.patientid,
csdbo_PATIENT.name, csdbo_PATIENT.sexid, csdbo_PATIENT.color,
csdbo_PATIENT.birthdate, csdbo_PATIENT.speciesid, csdbo_PATIENT.breedid FROM
csdbo_CLIENT, csdbo_PATIENT INNER JOIN csdbo_OWNER ON
csdbo_CLIENT.clientid=csdbo_OWNER.clientid WHERE
csdbo_PATIENT.patientid='2815'AND csdbo_CLIENT.clientid='2830'
TIA for any help...
Dian ~
View Hurricane Humane Updates: http://www.mousetrax.com/pets.html
--
This email scanned and certified clean by AVG!
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.9/116 - Release Date: 9/30/2005
---------------------------------------------------------------------
Home : http://groups.yahoo.com/group/active-server-pages
---------------------------------------------------------------------
Post : [email protected]
Subscribe : [EMAIL PROTECTED]
Unsubscribe: [EMAIL PROTECTED]
---------------------------------------------------------------------
Yahoo! Groups Links
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.9/116 - Release Date: 9/30/2005
--
This email scanned and certified clean by AVG!
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.9/116 - Release Date: 9/30/2005
---------------------------------------------------------------------
Home : http://groups.yahoo.com/group/active-server-pages
---------------------------------------------------------------------
Post : [email protected]
Subscribe : [EMAIL PROTECTED]
Unsubscribe: [EMAIL PROTECTED]
---------------------------------------------------------------------
Yahoo! Groups Links
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.9/116 - Release Date: 9/30/2005
--
This email scanned and certified clean by AVG!
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.9/118 - Release Date: 10/3/2005
------------------------ Yahoo! Groups Sponsor --------------------~-->
Most low income households are not online. Help bridge the digital divide today!
http://us.click.yahoo.com/cd_AJB/QnQLAA/TtwFAA/17folB/TM
--------------------------------------------------------------------~->
---------------------------------------------------------------------
Home : http://groups.yahoo.com/group/active-server-pages
---------------------------------------------------------------------
Post : [email protected]
Subscribe : [EMAIL PROTECTED]
Unsubscribe: [EMAIL PROTECTED]
---------------------------------------------------------------------
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/active-server-pages/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
