How do get the number of counted records?

Results 1 to 5 of 5

Thread: How do get the number of counted records?

  1. #1
    Ryan McBeth Guest

    Default How do get the number of counted records?

    I&#039;m porting a program from Perl/DBI/MySQL to ASP and MSSQL. One of the operations that I need to do is:<BR><BR>SELECT COUNT(*) FROM question_pull<BR><BR>This should return a number, but since I&#039;m not pulling from a specific table, I don&#039;t know how to pull that information out of the database.<BR><BR>With Perl DBI, it was easy, I just slapped everything into an array and I could reference the array location. However, when I run the SQL statement through the Query Analyzer, the Column name registers as "(No Column Name)".<BR><BR>I&#039;ve tried doing an:<BR><BR>Response.write rs("No Column Name")<BR><BR>But that gives me an error. Does anyone have any suggestions?<BR><BR>Ryan<BR>

  2. #2
    Jeremy_D Guest

    Default RE: How do get the number of counted records?

    Use a column alias, as in:<BR><BR>SELECT Count(*) AS TotalCount FROM question_pull<BR><BR>- or -<BR><BR>SELECT &#039;TotalCount&#039; = Count(*) FROM question_pull<BR><BR>You can then reference the column as "TotalCount".<BR><BR>A better practice even would be to wrap the statement in a stored procedure and use either the return value or an output parameter to pass the count to the client. The following procedure will do both:<BR><BR>CREATE PROCEDURE usp_Question_Pull_Count (@totalcount int OUTPUT) AS<BR> SET NOCOUNT ON<BR> SELECT @totalcount = Count(*) FROM question_pull<BR> RETURN @totalcount<BR>GO<BR><BR>Jeremy_D<BR>

  3. #3
    Ryan McBeth Guest

    Default RE: How do get the number of counted records?

    Hey, Very clever. I never though of that. Thank you.

  4. #4
    Join Date
    Dec 1969

    Default Use its position

    rs.fields(0).value<BR><BR>its an array starting at 0 to rs.fields.count - 1<BR><BR>the count starts at 1, and the array is offset at 0...<BR>so, if you wanted to print a list of values of a record...<BR>for i = 0 to rs.fields.count - 1<BR> response.write (rs.fields(i).value)<BR>next<BR><BR>-- Whol

  5. #5
    Jeremy_D Guest

    Default RE: Use its position

    Better use the name of the field. By using the ordinal position you are limiting the flexibility of your views and stored procedures. Once you start referencing by position you will not be able to change the select-list from the query anymore without having to review your client-side source code. Just use a column alias when you are selecting an expression instead of a field.<BR><BR>Just my 2 Eurocents,<BR>Jeremy_D<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts