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"
