RS(fieldname) error when using column alias.

[Lee Farrant]

Could someone tell me why I cannot seem to reference a field in a
record set which I have given an alias to. I have given the field
c.firstname an alias of firstname but my RS function cannot find it
(it errors with undefines name etc). Is there something different
about referencing an alias to a field or something different when
joining table with a prefix eg: c.firstname. Code is below.

Thanks
Lee


Call OPEN_DB()

SQL = "SELECT p.profileid as profileid ,c.firstname as firstname
,c.lastname as lastname FROM customers c, profiles p WHERE p.profileid
= " & profileid & " and c.customerid = p.customerid;"

Set RS = MyConn.Execute(SQL)

firstname = RS(firstname)

RS.Close

[Bob Barrows]

Lee Farrant wrote:

> Could someone tell me why I cannot seem to reference a field in a
> record set which I have given an alias to. I have given the field
> c.firstname an alias of firstname but my RS function cannot find it
> (it errors with undefines name etc). Is there something different
> about referencing an alias to a field or something different when
> joining table with a prefix eg: c.firstname. Code is below.
>
> Thanks
> Lee
>
>
> Call OPEN_DB()
>
> SQL = "SELECT p.profileid as profileid ,c.firstname as firstname
> ,c.lastname as lastname FROM customers c, profiles p WHERE p.profileid
> = " & profileid & " and c.customerid = p.customerid;"
>
> Set RS = MyConn.Execute(SQL)
>
> firstname = RS(firstname)
>
> RS.Close

There are two ways to reference an item in the Fields collection:
1. By using its zero-based index, or by using a variable/constant containing
that index. This is the most efficient way of referring to an item in a
collection.
So, to get the second field object you can do this:
rs(1)

or use a constant like this (you could also Dim a variable instead of using
a constant, but using the constant is more efficient):
const secondfield=1
rs(secondfield)

2. By using the name of the field, or a variable containing the name of the
field. To get the value of the field named thisfield, you can either use a
literal string like this:
rs("thisfield")

or use a constant like this:
const fieldname="thisfield"
rs(fieldname)

Bottom line: put quotes around the word "firstname" so that vbscript does
not think you are using a variable/constant.

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Lee Farrant]

thanks,
Lee




*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!