MORE sql Query

**Ken Schaefer** · Jul 19 '05, 11:29 AM

Re: MORE sql Query

http://www.adopenstatic.com/faq/800a0cc1.asp

You need to show us the code that produces the field
sql_Orders_Plac ed_Rs_Var

since the following line generates the error:
<%
sql_Orders_Plac ed_RS("sql_Orde rs_Placed_RS_Va r")
%>

Cheers
Ken

"Jason Daly" <daly_jason@yah oo.com> wrote in message
news:OzjPqrWCEH A.2560@TK2MSFTN GP12.phx.gbl...
: I can't write the sum of a certain colum in a table of a db.
:
: <%
: sql_Sum_Tax="SE LECT SUM(vtax) AS sql_Sum_Tax_RS_ Var FROM orderstats
: WHERE vcompletedate BETWEEN "& startDate &" AND "& endDate & ";"
: Set sql_Sum_Tax_RS = Server.CreateOb ject("ADODB.Rec ordset")
: sql_Sum_Tax_RS. Open sql_Sum_Tax, conn1
: %>
: <%=sql_Orders_P laced_RS("sql_O rders_Placed_RS _Var")%>
:
: That last line generates this error:
:
: ADODB.Recordset error '800a0cc1'
:
: Item cannot be found in the collection corresponding to the requested
: name or ordinal.
:
: Suggestions? Thanks
:
:
: *** Sent via Developersdex http://www.developersdex.com ***
: Don't just participate in USENET...get rewarded for it!

**Jason Daly** · Jul 19 '05, 11:29 AM

Re: MORE sql Query

I am new to aggregate functions. I thought i was moving in the right
direction when writing:

<%
sql_Sum_Tax="SE LECT SUM(vtax) AS sql_Sum_Tax_RS_ Var FROM orderstats
WHERE vcompletedate BETWEEN "& startDate &" AND "& endDate & ";"
Set sql_Sum_Tax_RS = Server.CreateOb ject("ADODB.Rec ordset")
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")
%>

How do I properly write a sql statement and response.write statement
that will allow me to write the SUM of a field?

**My original response.write statement was mispasted.**

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

**Bob Barrows** · Jul 19 '05, 11:29 AM

Re: MORE sql Query

Jason Daly wrote:[color=blue]
> I am new to aggregate functions. I thought i was moving in the right
> direction when writing:
>
> <%
> sql_Sum_Tax="SE LECT SUM(vtax) AS sql_Sum_Tax_RS_ Var FROM orderstats
> WHERE vcompletedate BETWEEN "& startDate &" AND "& endDate & ";"
> Set sql_Sum_Tax_RS = Server.CreateOb ject("ADODB.Rec ordset")
> 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")
> %>
>[/color]
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 & " "
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"

**Ken Schaefer** · Jul 19 '05, 11:29 AM

Re: MORE sql Query

That code should work...

Can you try doing this:
<%
response.write sql_Sum_Tax_RS( 0)
%>
and see if that also generates an error?

We can also try to verify what the recordset thinks are the names of the
fields by doing:

<%
sql_Sum_Tax_RS. Open <your stuff here>

Response.Write( "Outputt ing Field Names ")
For Each objField in sql_Sum_Tax_RS. Fields

Response.Write( objField.Name & " ")

Next
%>

I think it should output: sql_Sum_Tax_RS_ Var, but let's have a look and see.

Cheers
Ken

--
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~
"Jason Daly" <daly_jason@yah oo.com> wrote in message
news:efh7ldXCEH A.2600@TK2MSFTN GP09.phx.gbl...
: I am new to aggregate functions. I thought i was moving in the right
: direction when writing:
:
: <%
: sql_Sum_Tax="SE LECT SUM(vtax) AS sql_Sum_Tax_RS_ Var FROM orderstats
: WHERE vcompletedate BETWEEN "& startDate &" AND "& endDate & ";"
: Set sql_Sum_Tax_RS = Server.CreateOb ject("ADODB.Rec ordset")
: 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")
: %>
:
: How do I properly write a sql statement and response.write statement
: that will allow me to write the SUM of a field?
:
: **My original response.write statement was mispasted.**
:
: *** Sent via Developersdex http://www.developersdex.com ***
: Don't just participate in USENET...get rewarded for it!

**Jason Daly** · Jul 19 '05, 11:30 AM

Re: MORE sql Query

All set...for some reason (NOT a typo) my field names still were showing
null. RS(0) worked though. Thanks.

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

**Bob Barrows** · Jul 19 '05, 11:30 AM

Re: MORE sql Query

Jason Daly wrote:[color=blue]
> All set...for some reason (NOT a typo) my field names still were
> showing null. RS(0) worked though. Thanks.
>[/color]
If your sql statement had column aliases then this is simply not possible.
If you're still around, would you mind using the recordset's Save method,

rs.Save "filename", 1

to save your recordset to a file and post the text contained in the file (no
need to attach the file, just open it in notepad and copy the text) to your
reply?

Oh! What database are you using?

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"

MORE sql Query

MORE sql Query

Comment

Comment

Comment

Comment

Comment

Comment