[thelist] ASP / SQL .. so close ;(

Michele Foster michele at wordpro.on.ca
Mon Feb 17 20:53:01 CST 2003


I've almost got this working .. but not. ;(

Here's the bad ass nasty SQL .. sorry .. I used Access SQL view to create it
cos I couldn't figure out which way my joins needed to go.

sql = "SELECT Address.AddressID, Address.AddressContactID,
Address.AddressType, Categories.ID, Categories.Options, Address.Street,
Address.City, Address.Province, Categories_1.ID, Categories_1.Options,
Address.OtherProvince, Address.PostalCode, Address.Country, Categories_2.ID,
Categories_2.Options, Address.HomePhone, Address.WorkPhone,
Address.Extension, Address.MobilePhone, Address.FaxNumber,
Address.EmailAddress " &_

"FROM Categories AS Categories_2 RIGHT JOIN (Categories AS Categories_1
RIGHT JOIN (Categories RIGHT JOIN Address ON Categories.ID =
Address.AddressType) ON Categories_1.ID = Address.Province) ON
Categories_2.ID = Address.Country " &_

"WHERE (((Address.AddressContactID) = " & var_ContactID & "))"


It's working fine .. it's returning the correct number of results (both in
Access and in ASP) .. where the problem lies is this ..

I want to print out the Categories.Options ..

like such

<%=Address_RS("Categories.Options")%>

and it says
Item cannot be found in the collection corresponding to the requested name
or ordinal.
/recruitment/masters/address.asp, line 75

Now, I just went and checked something .. how do I reference the
Address.AddressType .. i.e. if I have two columns of the same name but from
different tables .. it's not finding it if I include the table name.

So, with the above example, if I remove the "categories." and just reference
Options, it finds it .. just not the correct one .. last one in the list I
believe.

This shouldn't be so difficult .. ;(

Mich





More information about the thelist mailing list