Naiya55 Naiya55 - 3 months ago 11
SQL Question

Classic ASP How to refer to a field I altered with LEFT

I'm trying to get the left 7 characters of a column, and the entirety of a second column from a table in my database. The code I am using to do this looks like this (minus the connection string, don't want to give that away).

Set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "myConnectionString"
Set rs=Server.CreateObject("ADODB.Recordset")

rs.Open "SELECT DISTINCT LEFT(Location,7), StopID FROM LocStop", conn

do until rs.eof
Response.Write(rs("Location"))
Response.Write(rs("StopID"))
rs.movenext
loop


This gives the error "ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal." on the line
Response.Write(rs("Location"))
. Using
rs.fields("Location")
fails in the same way

When I run this code without the LEFT call it works fine. My guess is that the column name Location is either not taken or is changed when I use LEFT on it. I am at a loss as to how I am now supposed to get this information now. Does anyone know if my guess was correct, and how get the information I need?

Answer

You just need to give it an alias:

SELECT DISTINCT LEFT(Location,7) as Location, StopID 
FROM LocStop

Or you could reference it by it's index:

Response.Write(rs(0))