Thanks for the added info. Yeah, I'd learned about dates, but didn't realize there other major differences. Dev and learn, eh? ;-)
Dian ~ View Hurricane Humane Updates: http://www.mousetrax.com/pets.html -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of David Smart Sent: Tuesday, October 04, 2005 4:27 PM To: [email protected] Subject: Re: [ASP] New error...Syntax on JOIN Didn't realise you didn't know. All the engines are different from each other to some extent. Biggest problem I've had with this was trying to develop under the small Sybase product to field in production under the main Sybase product (was in about 1995, so I don't remember the exact names of the products back then). It turned out to be easier to move from the small Sybase product to Oracle. :-( Main differences I found were in indexes (which were completely different), but joins were another nuisance. Dates were also a worry IIRC. SQL is definitely not standard and your Internet searches need to be specific on the engine you're using. Dave S ----- Original Message ----- From: "Dian D. Chapman" <[EMAIL PROTECTED]> To: <[email protected]> Sent: Wednesday, October 05, 2005 3:00 AM Subject: RE: [ASP] New error...Syntax on JOIN > 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 > > > > > > --------------------------------------------------------------------- > Home : http://groups.yahoo.com/group/active-server-pages > --------------------------------------------------------------------- > Post : [email protected] > Subscribe : [EMAIL PROTECTED] > Unsubscribe: [EMAIL PROTECTED] > --------------------------------------------------------------------- > Yahoo! Groups Links > > > > > > > --------------------------------------------------------------------- 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/118 - Release Date: 10/3/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 --------------------~--> Get Bzzzy! (real tools to help you find a job). Welcome to the Sweet Life. http://us.click.yahoo.com/A77XvD/vlQLAA/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/
