Results 1 to 5 of 5

Thread: [Resolved] How Do I Create Multiple Recordsets

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Nov 2003
    Location
    California
    Posts
    79

    Question [Resolved] How Do I Create Multiple Recordsets

    Is there any Way to Create Multiple Recordsets with a Single Trip to an ADO Recordset. I Have a Table that has a Week Column. The Week Numbers Range From 1-26. I need to Count How Many 1's, 2's,3's etc are contained in the Week Column for a Report.
    I dread the thought of having to create 26 seperate Connections and Recordsets. Someone told me I could do it with a Parameter But I'm not familar with how to do that since I'm New to Database Programming. I just want to finish this Project So I can Move on to Visual Studio.net

    Any Source Code Examples Would Help.
    Last edited by crmfghtr; Sep 5th, 2004 at 03:57 PM.
    Robert

  2. #2
    Fanatic Member vb_dba's Avatar
    Join Date
    Jun 2001
    Location
    Somewhere aloft between the real world and insanity
    Posts
    1,016
    If I understand correctly, couldn't you do this in one recordset:
    Code:
    Select week_column
         , Count(*)
    From yourTable
    Group By Count(*)
    Order By week_column
    That would give you a count by week.
    Chris

    Master Of My Domain
    Got A Question? Look Here First

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Nov 2003
    Location
    California
    Posts
    79
    Here is What I Tried But it does Not Work I have a Syntax Error But I don't Know where it is:

    Dim cn as ADODB.Connection, rs as ADODB.Recordset
    Dim sSqlStr as String, cnstr as String
    Dim Dtmd as Date

    'Update the Database Connection String from the Registry
    UpdateCon
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    cnstr = Conn
    'Read the Date for the Students For the Current Month and Year
    Dtmd = fMain.DtTime.Month & "/" & "1" & "/" & fMain.DtTime.Year
    'Select only the Students in the Current Month and Year.

    cn.Open cnstr
    sSqlStr = "Select week Count(*)FROM Students WHERE DateSeen >= " & "#" & Dtmd4 & "# Group By Count(*) Order By week"
    rs.CursorLocation = adUseClient
    rs.Open sSqlStr, cn, , , adCmdText

    If Week = 1 Then
    'Count the Number in Week 1
    FrmCard.fpSpread1.Sheet = 2
    FrmCard.fpSpread1.SetText 2, 20, rs(count)

    ElseIf Week = 2 Then
    'Count the Number in Week 2
    FrmCard.fpSpread1.Sheet = 2
    FrmCard.fpSpread1.SetText 2, 21, rs(count)
    end if

    set rs = nothing
    Robert

  4. #4
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758
    Which line causes the syntax error? Probably the query.

    Try this

    Code:
    sSqlStr = "Select [week], Count(*) FROM Students WHERE DateSeen >= " & "#" & Dtmd4 & "# Group By [Week] Order By [Week]"
    I assumed Week was your column name and since its a reserved/keyword, it must be surrounded with square brackets.

  5. #5
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,882

    Re: How Do I Create Multiple Recordsets

    Originally posted by crmfghtr
    Is there any Way to Create Multiple Recordsets with a Single Trip to an ADO Recordset. I Have a Table that has a Week Column. The Week Numbers Range From 1-26. I need to Count How Many 1's, 2's,3's etc are contained in the Week Column for a Report.
    I dread the thought of having to create 26 seperate Connections and Recordsets. Someone told me I could do it with a Parameter But I'm not familar with how to do that since I'm New to Database Programming. I just want to finish this Project So I can Move on to Visual Studio.net

    Any Source Code Examples Would Help.
    Is this MS SQL SERVER? Then learn to use SPROCS and PARAMETERS and then move on to .Net...

Posting Permissions

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



Click Here to Expand Forum to Full Width