Re: MORE sql Query

From: Bob Barrows (reb01501_at_NOyahoo.SPAMcom)
Date: 03/14/04 

Date: Sun, 14 Mar 2004 08:04:43 -0500

Jason Daly wrote:
> I am new to aggregate functions. I thought i was moving in the right
> direction when writing:
>
> <%
> sql_Sum_Tax="SELECT SUM(vtax) AS sql_Sum_Tax_RS_Var FROM orderstats
> WHERE vcompletedate BETWEEN "& startDate &" AND "& endDate & ";"
> Set sql_Sum_Tax_RS = Server.CreateObject("ADODB.Recordset")
> sql_Sum_Tax_RS.Open sql_Sum_Tax, conn1
> %>
>
> I never defined or declared sql_Sum_Tax_RS_Var. I thought I was just
> allowed to use the AS keyword that way, and then I could write the SUM
> with:
>
> <%
> response.write sql_Sum_Tax_RS("sql_Sum_Tax_RS_Var")
> %>
>
This should work correctly, although I can never understand why people want
to use such god-awfully long names when a simple name like SumVtax would do.
Practicing your typing skills? Exercising that finger that types the
underscore, maybe? :-)

Moreover, why would you want to use a variable name like sql_Sum_Tax_RS
when a simple rs would do? You did not bother ultra-defining the conn1
variable did you? Actually, the "1" in that name makes me a little nervous -
are you opening more than one connection to your database on this page?
There is never a good reason to do this. A single connection can be re-used
as many times as you want. There is no need to open a new connection for
each task ...

Anyways, back to referencing the field name: there are several ways to refer
to a recordset field:

1. The most efficient is by using the zero-based ordinal position number.
The first field in the recordset has an ordinal position of zero, and so on.
So you can use rs(0) to refer to it.
The value in the parentheses must either be a literal number (no quotes) or
a variable or constant containing a number:
i=1
rs(i) 'refers to second field in recordset

2. You can use the field's name, which will either contain the actual name
of the database column, or the column alias assigned to the field in the sql
query. Again, you can either use a literal string surrounded by quotes:
rs("fieldname")
or a variable containing a string:
s="fieldname"
rs(s)

It's a good idea to explicitly state the property you wish to access to
avoid errors. To get the value of a field, explicitly use the word "value"
in your statement:

var = rs(0).value

Back to this:
response.write sql_Sum_Tax_RS("sql_Sum_Tax_RS_Var")

Given that your sql statement contains this:
" ... AS sql_Sum_Tax_RS_Var ... "

and you have no typos (whose frequency will be increased by the complicated
names you are using <grin>), then this should work fine. Since it is not
working fine, you need to determine the actual names of the fields in your
recordset:

for i = 0 to sql_Sum_Tax_RS.Fields.Count
    response.write i & ": " & _
        sql_Sum_Tax_RS(i).Name & "<BR>"
next

Again, given the complicated names you are using for your variables and
aliases, I would not be at all surprised if your code contained a typo.

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"

Relevant Pages

  • Re: Waiting for a recordset to finish fetching
    ... I'm using the ADP's connection, but I don't think there's more than ... The form recordset is the one that's still fetching, ... However, within the loop, the state never changes. ... This email account is my spam trap so I ...
    (microsoft.public.data.ado)
  • Re: .Close, = Nothing - Real dumb question? with a real quick answer...
    ... > Okay I understand that if you open the ADO connection that you ... > rsBlockedCheck.Open strSQL, strCon ... > and the recordset need closed... ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.general)
  • Re: Passing recordset between pages
    ... Before closing the connection: ... Recordset Results and display the results ... Execute does not make the local variables in page 1 available in page 2, ... This email account is my spam trap so I ...
    (microsoft.public.scripting.vbscript)
  • Re: Updating Access data using SQL / refresh time question
    ... As a test today, for one poarticular recordset, I changed from DAO to ADO to ... > I forgot to mention that the expense of repeated connection open and close ... > dim oconn as new adodb.connection ... > 'Now load the listview by looping through each RS row ...
    (microsoft.public.vb.database)
  • Re: Updating Access data using SQL / refresh time question
    ... > I forgot to mention that the expense of repeated connection open and close ... > dim oconn as new adodb.connection ... > 'Now load the listview by looping through each RS row ... > ' Set the recordset object each time we load the listview ...
    (microsoft.public.vb.database)