 |
|
8000XXXX Errors Alerts ASP.NET 2.0 Classic ASP 1.0
Databases Access DB & ADO General SQL Server & Access Articles MySQL Other Articles Schema Tutorials Sql Server 2000 Sql Server 2005 General Concepts Search Engine Optimization (SEO)Search
ASP FAQ Tutorials :: Databases :: General SQL Server & Access Articles :: How do I access MIN, MAX, SUM, COUNT values from SQL statements?
How do I access MIN, MAX, SUM, COUNT values from SQL statements?
Many people write a SQL statement like this:
And are confused when they get this error:
This is because they weren't looking for "column" from the recordset, they were looking for an expression that represents the COUNT of "column" in the table. There are at least three ways to obtain values of aggregate functions from a recordset.
The first is to simply use ordinal numbers instead of the name of the column. This requires no modification to the existing SQL statement, and has the added bonus of being slightly faster (though for most users I recognize this will be negligible, and can make the ASP code harder to maintain if the SQL statement will change in the future).
The second two solutions involving adjusting the SQL statement to use an alias for the expression:
| <% set conn = CreateObject("ADODB.Connection") conn.open "<connection string>" set rs = conn.execute("SELECT COUNT(column) FROM table") response.write rs("column") %> |
And are confused when they get this error:
| ADODB.Recordset (0x800A0CC1) Item cannot be found in the collection corresponding to the requested name or ordinal. |
This is because they weren't looking for "column" from the recordset, they were looking for an expression that represents the COUNT of "column" in the table. There are at least three ways to obtain values of aggregate functions from a recordset.
The first is to simply use ordinal numbers instead of the name of the column. This requires no modification to the existing SQL statement, and has the added bonus of being slightly faster (though for most users I recognize this will be negligible, and can make the ASP code harder to maintain if the SQL statement will change in the future).
| <% set conn = CreateObject("ADODB.Connection") conn.open "<connection string>" set rs = conn.execute("SELECT COUNT(column) FROM table") response.write rs(0) %> |
The second two solutions involving adjusting the SQL statement to use an alias for the expression:
| <% set conn = CreateObject("ADODB.Connection") conn.open "<connection string>" set rs = conn.execute("SELECT columncount = COUNT(column) FROM table") response.write rs("columncount") set rs = conn.execute("SELECT COUNT(column) AS columncount FROM table") response.write rs("columncount") %> |
Related Articles
Can I fix this mm/dd/yyyy <-> dd/mm/yyyy confusion once and for all? Could I get some help with JOINs? How can I tell which version of MDAC I'm running? How do I change column order in a table structure? How do I change the order of columns in a table? How do I concatenate strings from a column into a single row? How do I convert columns of values into a single list? How do I determine if a database exists? How do I document / compare my SQL Server database(s)? How do I get the IDENTITY / AUTONUMBER value for the row I inserted? How do I solve 'ADO Could Not Find The Specified Provider'? Should I use BETWEEN in my database queries? Why can't I use the * wildcard in a database search? Why do I get 'Syntax Error in INSERT INTO Statement' with Access? Why do I get weird results when using both AND and OR in a query? Why do some SQL strings have an 'N' prefix? Why does AbsolutePosition return as -1? Why doesn't SQL Server allow me to separate DATE and TIME? Why is Query Analyzer only returning 255 characters? Why should I avoid NULLs in my database? How do I deal with an apostrophe (') in a SQL statement?