Naiya55 Naiya55 - 1 year ago 80
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.Open "myConnectionString"
Set rs=Server.CreateObject("ADODB.Recordset")

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

do until rs.eof

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
. Using
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 Source

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:

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download