|
 |
asp_databases thread: How to sum the return from a recordset.
Message #1 by <nickm@s...> on Wed, 31 Jan 2001 00:03:41 -0500
|
|
Hi:
I want to return the sum of a recordset derived from an Access database. I
used the following statement and I get an error:
strSQL = "Select sum(employee_time) FROM DevNotes WHERE projectID= " & id &
";"
employee_time is of the data type 'number'
Any suggestions?
Thanks,
Stefan
Message #2 by "Ken Schaefer" <ken@a...> on Wed, 31 Jan 2001 16:32:36 +1100
|
|
And what exactly was the error you got? (or do you want us to guess?)
Cheers
Ken
----- Original Message -----
From: <nickm@s...>
To: "ASP Databases" <asp_databases@p...>
Sent: Wednesday, January 31, 2001 4:03 PM
Subject: [asp_databases] How to sum the return from a recordset.
> Hi:
>
> I want to return the sum of a recordset derived from an Access database. I
> used the following statement and I get an error:
>
> strSQL = "Select sum(employee_time) FROM DevNotes WHERE projectID= " & id
&
> ";"
>
>
> employee_time is of the data type 'number'
>
> Any suggestions?
>
> Thanks,
>
> Stefan
Message #3 by "Dallas Martin" <dmartin@z...> on Wed, 31 Jan 2001 00:56:38 -0500
|
|
You must alias the SUM(), so that A COLUMN name is returned in your record
set.
SELECT sum(employee_time) AS "SUMTIME" FROM DevNotes WHERE projectID= " & id
& ";"
Dallas Martin
----- Original Message -----
From: <nickm@s...>
To: "ASP Databases" <asp_databases@p...>
Sent: Wednesday, January 31, 2001 12:03 AM
Subject: [asp_databases] How to sum the return from a recordset.
> Hi:
>
> I want to return the sum of a recordset derived from an Access database. I
> used the following statement and I get an error:
>
> strSQL = "Select sum(employee_time) FROM DevNotes WHERE projectID= " & id
&
> ";"
>
>
> employee_time is of the data type 'number'
>
> Any suggestions?
>
> Thanks,
>
> Stefan
>
>
>
Message #4 by "Ken Schaefer" <ken@a...> on Wed, 31 Jan 2001 17:37:37 +1100
|
|
You don't have to alias it...you can use ordinals instead of names...
<%
strSQL = "SELECT SUM(field1) "
strSQL = strSQL & "FROM table1"
Set objRS = objConn.execute(strSQL)
Response.Write(objRS(0))
%>
Cheers
Ken
----- Original Message -----
From: "Dallas Martin" <dmartin@z...>
To: "ASP Databases" <asp_databases@p...>
Sent: Wednesday, January 31, 2001 4:56 PM
Subject: [asp_databases] Re: How to sum the return from a recordset.
> You must alias the SUM(), so that A COLUMN name is returned in your record
> set.
>
> SELECT sum(employee_time) AS "SUMTIME" FROM DevNotes WHERE projectID= " &
id
> & ";"
>
> Dallas Martin
Message #5 by "Wally Burfine" <oopconsultant@h...> on Wed, 31 Jan 2001 14:10:03 -0000
|
|
what is the error?
>From: <nickm@s...>
>Reply-To: "ASP Databases" <asp_databases@p...>
>To: "ASP Databases" <asp_databases@p...>
>Subject: [asp_databases] How to sum the return from a recordset.
>Date: Wed, 31 Jan 2001 00:03:41 -0500
>
>Hi:
>
>I want to return the sum of a recordset derived from an Access database. I
>used the following statement and I get an error:
>
>strSQL = "Select sum(employee_time) FROM DevNotes WHERE projectID= " & id &
>";"
>
>
>employee_time is of the data type 'number'
>
>Any suggestions?
>
>Thanks,
>
>Stefan
>
>
>
Message #6 by <nickm@s...> on Wed, 31 Jan 2001 11:20:10 -0500
|
|
Hi:
This is the error and SQL string that I am using:
Select sum(employee_time) AS 'SUMTIME' FROM DevNotes WHERE employeeID
'Santo';
ADODB.Fields error '800a0cc1'
ADO could not find the object in the collection corresponding to the name or
ordinal reference requested by the application.
Thanks,
Stefan
Message #7 by <nickm@s...> on Wed, 31 Jan 2001 11:29:28 -0500
|
|
Hi:
Following is the code for the select statement:
1. strSQL = "Select sum(employee_time)FROM DevNotes WHERE employeeID= '" &
id & "';"
2. response.write strSQL
3. 'response.end
4. oConn.open "DSN=Studioweb"
5. oRS.open strSQL, oConn
6. oRS.MoveFirst
7. Do while NOT oRS.EOF
8. response.write "<br><b>" & oRS("employee_time") & "</b><br>"
I numbered the lines to make it easier, the code works up unitl line 8. So
to me that tells me that the SQL (executed on line 5) is OKA, so what is the
problem? Am I not referring to the returned recordset properly?
Thanks,
Stefan
Message #8 by <nickm@s...> on Wed, 31 Jan 2001 11:43:05 -0500
|
|
Hi:
Thanks guys, I used the AS COLUMNNAME and it works fine.
Stef
----- Original Message -----
From: <nickm@s...>
To: "ASP Databases" <asp_databases@p...>
Sent: Wednesday, January 31, 2001 11:20 AM
Subject: [asp_databases] Re: How to sum the return from a recordset.
> Hi:
>
> This is the error and SQL string that I am using:
>
> Select sum(employee_time) AS 'SUMTIME' FROM DevNotes WHERE employeeID
> 'Santo';
> ADODB.Fields error '800a0cc1'
>
> ADO could not find the object in the collection corresponding to the name
or
> ordinal reference requested by the application.
>
>
>
> Thanks,
>
> Stefan
>
>
Message #9 by "Wally Burfine" <oopconsultant@h...> on Wed, 31 Jan 2001 17:06:55 -0000
|
|
Take the single quotes off of 'SUMTIME' and make it:
Select sum(employee_time) AS SUMTIME FROM DevNotes WHERE employeeID
'Santo';
>From: <nickm@s...>
>Reply-To: "ASP Databases" <asp_databases@p...>
>To: "ASP Databases" <asp_databases@p...>
>Subject: [asp_databases] Re: How to sum the return from a recordset.
>Date: Wed, 31 Jan 2001 11:20:10 -0500
>
>Hi:
>
>This is the error and SQL string that I am using:
>
>Select sum(employee_time) AS 'SUMTIME' FROM DevNotes WHERE employeeID
>'Santo';
>ADODB.Fields error '800a0cc1'
>
>ADO could not find the object in the collection corresponding to the name
>or
>ordinal reference requested by the application.
>
>
>
>Thanks,
>
>Stefan
>
Message #10 by "Wally Burfine" <oopconsultant@h...> on Wed, 31 Jan 2001 17:09:41 -0000
|
|
("employee_time") is not a field in the recordset. You should use the
ordinal position (0) to get the value
>From: <nickm@s...>
>Reply-To: "ASP Databases" <asp_databases@p...>
>To: "ASP Databases" <asp_databases@p...>
>Subject: [asp_databases] Re: How to sum the return from a recordset.
>Date: Wed, 31 Jan 2001 11:29:28 -0500
>
>Hi:
>
>Following is the code for the select statement:
>
>1. strSQL = "Select sum(employee_time)FROM DevNotes WHERE employeeID= '" &
>id & "';"
>2. response.write strSQL
>3. 'response.end
>4. oConn.open "DSN=Studioweb"
>5. oRS.open strSQL, oConn
>6. oRS.MoveFirst
>7. Do while NOT oRS.EOF
>8. response.write "<br><b>" & oRS("employee_time") & "</b><br>"
>
>I numbered the lines to make it easier, the code works up unitl line 8. So
>to me that tells me that the SQL (executed on line 5) is OKA, so what is
>the
>problem? Am I not referring to the returned recordset properly?
>
>Thanks,
>Stefan
>
|
|
|
View Cart
