using a variable as a part of a fieldname

Hi All:

This will be probably considered a newbie question, but then I am actually
rather green in this subject.....

After opening a connection to the database (named "Conn"), I would like to
be able to refer to the fields of the database in that recordset by way of a
previously declared valiable. My first approach has been something on the
lines of this:

<%
dim = specificfieldname, sqltest
specificfieldname = "lastname"
sqltest = "SELECT " & specificfieldname & " FROM customers"
Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open sqltest, Conn, 1,3
Do while not RS.EOF
response.write RS("(specificfieldname)") & "
"
RS.MoveNext
Loop

RS.Close
Conn.Close
Set RS = Nothing
Set Conn = Nothing
%>

However that produces an ADODB collection error. The error must be related
to the way I incorporate the variable because if I make a direct reference
to the fieldname:

response.write RS("lastname")

then everything works OK.

Any suggestions would be greatly appreciated.

Thanks

TB

Re: using a variable as a part of a fieldname

TB wrote:
> Hi All:
>
> This will be probably considered a newbie question, but then I am
> actually rather green in this subject.....
>
> After opening a connection to the database (named "Conn"),

You forgot to tell us the type and version of the database. Please do not
leave this information out of future posts.

> I would
> like to be able to refer to the fields of the database in that
> recordset by way of a previously declared valiable. My first approach
> has been something on the lines of this:
>
> <%
> dim = specificfieldname, sqltest
> specificfieldname = "lastname"
> sqltest = "SELECT " & specificfieldname & " FROM customers"
> Set RS = Server.CreateObject("ADODB.Recordset")
> RS.Open sqltest, Conn, 1,3
> Do while not RS.EOF
> response.write RS("(specificfieldname)") & "
"
> RS.MoveNext
> Loop
>
> RS.Close
> Conn.Close
> Set RS = Nothing
> Set Conn = Nothing
> %>
>
> However that produces an ADODB collection error. The error must be
> related to the way I incorporate the variable because if I make a
> direct reference to the fieldname:
>
> response.write RS("lastname")
>
> then everything works OK.
>
> Any suggestions would be greatly appreciated.
>
> Thanks
>
> TB

Literal strings need to be delimited by quotes. Variables containing strings
do not need delimiters.

response.write RS(specificfieldname)

will work.

In this case (actually in any case, since it is good practice to avoid
"select *", listing all the fields to be returned by your query instead -
http://www.aspfaq.com/show.asp?id=2096), it is more efficient to use the
ordinal position of the field when referring to it:

response.write RS(0)

The index of the Fields collection is zero-based, so this statement refers
to the first field in the Fields collection. Using te ordinal position will
always be more efficient since it does not force a bunch of string
comparisons to be made, which are inefficient.

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"

Re: using a variable as a part of a fieldname

A very elegant solution - thanks a lot. It will simplify a my coding
significantly. thanks again.

TB

"Bob Barrows [MVP]" wrote in message
news:%23H4LQHY9EHA.2180@TK2MSFTNGP10.phx.gbl...
> TB wrote:
>> Hi All:
>>
>> This will be probably considered a newbie question, but then I am
>> actually rather green in this subject.....
>>
>> After opening a connection to the database (named "Conn"),
>
> You forgot to tell us the type and version of the database. Please do not
> leave this information out of future posts.
>
>> I would
>> like to be able to refer to the fields of the database in that
>> recordset by way of a previously declared valiable. My first approach
>> has been something on the lines of this:
>>
>> <%
>> dim = specificfieldname, sqltest
>> specificfieldname = "lastname"
>> sqltest = "SELECT " & specificfieldname & " FROM customers"
>> Set RS = Server.CreateObject("ADODB.Recordset")
>> RS.Open sqltest, Conn, 1,3
>> Do while not RS.EOF
>> response.write RS("(specificfieldname)") & "
"
>> RS.MoveNext
>> Loop
>>
>> RS.Close
>> Conn.Close
>> Set RS = Nothing
>> Set Conn = Nothing
>> %>
>>
>> However that produces an ADODB collection error. The error must be
>> related to the way I incorporate the variable because if I make a
>> direct reference to the fieldname:
>>
>> response.write RS("lastname")
>>
>> then everything works OK.
>>
>> Any suggestions would be greatly appreciated.
>>
>> Thanks
>>
>> TB
>
> Literal strings need to be delimited by quotes. Variables containing
> strings do not need delimiters.
>
> response.write RS(specificfieldname)
>
> will work.
>
> In this case (actually in any case, since it is good practice to avoid
> "select *", listing all the fields to be returned by your query instead -
> http://www.aspfaq.com/show.asp?id=2096), it is more efficient to use the
> ordinal position of the field when referring to it:
>
> response.write RS(0)
>
> The index of the Fields collection is zero-based, so this statement refers
> to the first field in the Fields collection. Using te ordinal position
> will always be more efficient since it does not force a bunch of string
> comparisons to be made, which are inefficient.
>
> 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"
>

Re: using a variable as a part of a fieldname

Keep in mind that if you use ordinal numbers to reference columns you need
to be very carefull when modifying the associated SQL statement.

--
--Mark Schupp
Head of Development
Integrity eLearning
www.ielearning.com

"TB" wrote in message
news:%2398ZXkZ9EHA.1296@TK2MSFTNGP10.phx.gbl...
>A very elegant solution - thanks a lot. It will simplify a my coding
>significantly. thanks again.
>
> TB
>
> "Bob Barrows [MVP]" wrote in message
> news:%23H4LQHY9EHA.2180@TK2MSFTNGP10.phx.gbl...
>> TB wrote:
>>> Hi All:
>>>
>>> This will be probably considered a newbie question, but then I am
>>> actually rather green in this subject.....
>>>
>>> After opening a connection to the database (named "Conn"),
>>
>> You forgot to tell us the type and version of the database. Please do not
>> leave this information out of future posts.
>>
>>> I would
>>> like to be able to refer to the fields of the database in that
>>> recordset by way of a previously declared valiable. My first approach
>>> has been something on the lines of this:
>>>
>>> <%
>>> dim = specificfieldname, sqltest
>>> specificfieldname = "lastname"
>>> sqltest = "SELECT " & specificfieldname & " FROM customers"
>>> Set RS = Server.CreateObject("ADODB.Recordset")
>>> RS.Open sqltest, Conn, 1,3
>>> Do while not RS.EOF
>>> response.write RS("(specificfieldname)") & "
"
>>> RS.MoveNext
>>> Loop
>>>
>>> RS.Close
>>> Conn.Close
>>> Set RS = Nothing
>>> Set Conn = Nothing
>>> %>
>>>
>>> However that produces an ADODB collection error. The error must be
>>> related to the way I incorporate the variable because if I make a
>>> direct reference to the fieldname:
>>>
>>> response.write RS("lastname")
>>>
>>> then everything works OK.
>>>
>>> Any suggestions would be greatly appreciated.
>>>
>>> Thanks
>>>
>>> TB
>>
>> Literal strings need to be delimited by quotes. Variables containing
>> strings do not need delimiters.
>>
>> response.write RS(specificfieldname)
>>
>> will work.
>>
>> In this case (actually in any case, since it is good practice to avoid
>> "select *", listing all the fields to be returned by your query instead -
>> http://www.aspfaq.com/show.asp?id=2096), it is more efficient to use the
>> ordinal position of the field when referring to it:
>>
>> response.write RS(0)
>>
>> The index of the Fields collection is zero-based, so this statement
>> refers to the first field in the Fields collection. Using te ordinal
>> position will always be more efficient since it does not force a bunch of
>> string comparisons to be made, which are inefficient.
>>
>> 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"
>>
>
>

Re: using a variable as a part of a fieldname

True, but the beauty of this method which I am really grateful for learning,
is that you can create different record sets using the same SQL statement,
by way of inserting variables.

For example:

<%
dim field1, field2, table1
field1 = whatever
field2 = whatever2
table1 = whatever3
sqlstatement = "SELECT " & field1 & ", " & field2 & " FROM " & table
Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open sqlstatement, Conn, 1,3
Do While Not RS.EOF
Response.write RS(0) & "
"
Response.write RS(1) & "
"
RS.MoveNext
Loop
RS.Close
Set RS = Nothing
%>

It seems like a rather efficient way to get things done.

TB


"Mark Schupp" wrote in message
news:OQnAwca9EHA.1204@TK2MSFTNGP10.phx.gbl...
> Keep in mind that if you use ordinal numbers to reference columns you need
> to be very carefull when modifying the associated SQL statement.
>
> --
> --Mark Schupp
> Head of Development
> Integrity eLearning
> www.ielearning.com
>
> "TB" wrote in message
> news:%2398ZXkZ9EHA.1296@TK2MSFTNGP10.phx.gbl...
>>A very elegant solution - thanks a lot. It will simplify a my coding
>>significantly. thanks again.
>>
>> TB
>>
>> "Bob Barrows [MVP]" wrote in message
>> news:%23H4LQHY9EHA.2180@TK2MSFTNGP10.phx.gbl...
>>> TB wrote:
>>>> Hi All:
>>>>
>>>> This will be probably considered a newbie question, but then I am
>>>> actually rather green in this subject.....
>>>>
>>>> After opening a connection to the database (named "Conn"),
>>>
>>> You forgot to tell us the type and version of the database. Please do
>>> not leave this information out of future posts.
>>>
>>>> I would
>>>> like to be able to refer to the fields of the database in that
>>>> recordset by way of a previously declared valiable. My first approach
>>>> has been something on the lines of this:
>>>>
>>>> <%
>>>> dim = specificfieldname, sqltest
>>>> specificfieldname = "lastname"
>>>> sqltest = "SELECT " & specificfieldname & " FROM customers"
>>>> Set RS = Server.CreateObject("ADODB.Recordset")
>>>> RS.Open sqltest, Conn, 1,3
>>>> Do while not RS.EOF
>>>> response.write RS("(specificfieldname)") & "
"
>>>> RS.MoveNext
>>>> Loop
>>>>
>>>> RS.Close
>>>> Conn.Close
>>>> Set RS = Nothing
>>>> Set Conn = Nothing
>>>> %>
>>>>
>>>> However that produces an ADODB collection error. The error must be
>>>> related to the way I incorporate the variable because if I make a
>>>> direct reference to the fieldname:
>>>>
>>>> response.write RS("lastname")
>>>>
>>>> then everything works OK.
>>>>
>>>> Any suggestions would be greatly appreciated.
>>>>
>>>> Thanks
>>>>
>>>> TB
>>>
>>> Literal strings need to be delimited by quotes. Variables containing
>>> strings do not need delimiters.
>>>
>>> response.write RS(specificfieldname)
>>>
>>> will work.
>>>
>>> In this case (actually in any case, since it is good practice to avoid
>>> "select *", listing all the fields to be returned by your query
>>> instead - http://www.aspfaq.com/show.asp?id=2096), it is more efficient
>>> to use the ordinal position of the field when referring to it:
>>>
>>> response.write RS(0)
>>>
>>> The index of the Fields collection is zero-based, so this statement
>>> refers to the first field in the Fields collection. Using te ordinal
>>> position will always be more efficient since it does not force a bunch
>>> of string comparisons to be made, which are inefficient.
>>>
>>> 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"
>>>
>>
>>
>
>

Re: using a variable as a part of a fieldname

TB wrote:
>
> It seems like a rather efficient way to get things done.
>
Wait till you find out about GetString and GetRows ... :-)

http://www.aspfaq.com/show.asp?id=2467

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"

Re: using a variable as a part of a fieldname

Checked your link, which leads to a very interesting article. While
GetString and GetRows undoubtly offers maximum efficiency, the Recordset
approach offers a lot of "built-in" features. So it really comes down to the
size of the tables you have to search and the amount of data you intend to
extract.

But since one of my upcoming projects is a web with potentially of lot of
traffic, I will seriously consider these aproaches.

TB

"Bob Barrows [MVP]" wrote in message
news:O226r329EHA.4028@TK2MSFTNGP15.phx.gbl...
> TB wrote:
>>
>> It seems like a rather efficient way to get things done.
>>
> Wait till you find out about GetString and GetRows ... :-)
>
> http://www.aspfaq.com/show.asp?id=2467
>
> 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"
>