Counting through a RecordSet with For Each

Results 1 to 5 of 5

Thread: Counting through a RecordSet with For Each

  1. #1
    Join Date
    Dec 1969
    Posts
    64

    Default Counting through a RecordSet with For Each

    I need to loop through a RecordSet focusing on a specific field and count the number of records in that RecordSet for each item related to that specific field. As an example, how would you count the number of people in your database who had blue eyes, brown eyes, green eyes, red eyes (from starring at the computer too long?), etc. Assume that there is a lookup table for all the eye colors, and you want to loop through them all.<BR><BR>Using this example, I tried using code similar to the following:<BR> For Each color in rset.Fields("EyeColor")<BR> Response.Write(color & " eye color: ")<BR> HowManyRecs = rset.RecordCount<BR> Response.Write(HowManyRecs & " people")<BR> Next<BR><BR>I got an error message that said "ADODB.Field error &#039 800a01b6&#039: Object doesn&#039t support this property or method" on the line with the "For Each."<BR><BR>Thanks in advance!

  2. #2
    Join Date
    Dec 1969
    Posts
    69

    Default RE: Counting through a RecordSet with For Each

    How did you open you recordset (rset.CursorType)? I haven&#039t tried myself but I guess adOpenKeyset should do. If it doesn&#039t, try with adOpenDynamic or adOpenStatic. If that doesn&#039t do, I will have to think...<BR><BR>Let me know if you have any problems with this.<BR>stevang@mail.com<BR>

  3. #3
    Join Date
    Dec 1969
    Posts
    64

    Default RE: Counting through a RecordSet with For Each

    I opened the RecordSet with AdOpenStatic.

  4. #4
    John Weeflaar Guest

    Default RE: Counting through a RecordSet with For Each

    Janet, For Each ... is used for an array or a colection. So, you<BR>can&#039t use it here.<BR>You&#039ll need to construct an SQL statement using GROUP BY<BR>to find out the number of people having a particular<BR>eye color in each group, for example.<BR><BR>Let us know if you&#039re stuck on this direction.<BR><BR>John


  5. #5
    Join Date
    Dec 1969
    Posts
    64

    Default RE: Counting through a RecordSet with For Each

    Thank you, John. I may try that or write a stored procedure and use SQL&#039s COUNT. I will not be able to get back to coding my metric&#039s report page until probably next week -- I have jury duty starting tomorrow (which could last 0-7 days). I will post a reply with my success or failure, so please check back ~daily. Thank you again! I appreciate your response.<BR><BR>--Janet

Posting Permissions

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