Why is my RecordSet containing only 1 value and RecordCount = -1?

[c112994]

Why does my RecordSet only get the first value in the database? There are supposed to be 41 districts but only the first one is returned

Here is my code, help me please. Thank you

Sub Runner()
 
    Dim folderName As String
    Dim salesPeriod As String
    Dim Region As String
    Dim cn As ADODB.Connection
    Dim cms As ADODB.Command
    Dim rs As ADODB.Recordset
    Dim SQL As String
 
    'Make Output Folder
    folderName = CStr(Year(DateTime.Now) & "." _
                    & Month(DateTime.Now) & "." _
                    & Day(DateTime.Now) _
                    & " " & Hour(DateTime.Now) _
                    & "." & Minute(DateTime.Now) _
                    & "." & Second(DateTime.Now))
 
    'Next time, make the username a variable to be accessed on any computer
    ChDir "C:\Users\E0214206\Desktop"
    MkDir (folderName)
 
    'Getting the inputted salesPeriod
 
    salesPeriod = Range("E1").Value
 
    'Getting the inputted Region
    Region = Range("E2").Value
 
    Application.DisplayAlerts = False
    'Sheets("Inputs").Delete
    Application.DisplayAlerts = True
 
    Set cmd = New ADODB.Command
    Set cn = New ADODB.Connection
 
    SQL = "SELECT DistrictCode FROM dbo.Analytics_PerformanceData WHERE DistrictCode IS NOT NULL ORDER BY DistrictCode ASC"
 
    With cn
        'timeout
        .CommandTimeout = 0
        'set connection string
        .ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=SLP;Data Source=mnlsapp02;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MNLL1033;Use Encryption for Data=False;Tag with column collation when possible=False"
        'open connection
        .Open
    End With
 
    With cmd
        'set activeconnection to command object
        .ActiveConnection = cn
 
        'disable time limit
        .CommandTimeout = 0
 
        'set commandtype
        .CommandType = adCmdText
 
        'set commandtext
        .CommandText = SQL
 
        'execute query
        Set rs = .Execute
    End With
 
 
 
    'To loop through the districts
    Dim count As Integer
    For count = 0 To 5
        Dim w As Integer
        w = count
 
        Dim DistrictCode As String
        DistrictCode = rs.Fields(count)
 
        'Refresh Pivot Tables
        'Call Refresh.Refresh(salesPeriod, DistrictCode, Region)
 
        'Create Output Files
        Call CreateOutputFile.CreateOutputFile(salesPeriod, DistrictCode, Region, folderName)
    Next count
 
    rs.Close
    cn.Close
 
    Set cn = Nothing
    Set cmd = Nothing
    Set rs = Nothing
 
 
End Sub

[jimatqsi]

c,
Welcome to Bytes. Please use Code tags when posting code. Click the [Code/] button and paste your code between the links.

Where is the information about recordcount coming from? I don't see any reference to it in the code. Before querying recordcount you should advance to the last record in your recordset or it may not be accurate.

I would begin by adding error handling to your routine.

Jim

[Rabbit]

You never loop through your recordset. All you do is loop through 6 columns in the recordset.

[c112994]

Hi! Thanks for the help, I edited my code into this but it still gives me only the first value from the database?

Sub Runner()
 
    Dim folderName As String
    Dim salesPeriod As String
    Dim Region As String
    Dim cn As ADODB.Connection
    Dim cms As ADODB.Command
    Dim rs As ADODB.Recordset
    Dim SQL As String
 
    'Make Output Folder
    folderName = CStr(Year(DateTime.Now) & "." _
                    & Month(DateTime.Now) & "." _
                    & Day(DateTime.Now) _
                    & " " & Hour(DateTime.Now) _
                    & "." & Minute(DateTime.Now) _
                    & "." & Second(DateTime.Now))
 
    'Next time, make the username a variable to be accessed on any computer
    ChDir "C:\Users\E0214206\Desktop"
    MkDir (folderName)
 
    'Getting the inputted salesPeriod
 
    salesPeriod = Range("E1").Value
 
    'Getting the inputted Region
    Region = Range("E2").Value
 
    Application.DisplayAlerts = False
    'Sheets("Inputs").Delete
    Application.DisplayAlerts = True
 
    Set cmd = New ADODB.Command
    Set cn = New ADODB.Connection
 
    SQL = "SELECT DistrictCode FROM dbo.Analytics_PerformanceData WHERE DistrictCode IS NOT NULL ORDER BY DistrictCode ASC"
 
    With cn
        'timeout
        .CommandTimeout = 0
        'set connection string
        .ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=SLP;Data Source=mnlsapp02;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MNLL1033;Use Encryption for Data=False;Tag with column collation when possible=False"
        'open connection
        .Open
    End With
 
    With cmd
        'set activeconnection to command object
        .ActiveConnection = cn
 
        'disable time limit
        .CommandTimeout = 0
 
        'set commandtype
        .CommandType = adCmdText
 
        'set commandtext
        .CommandText = SQL
 
        'execute
        Set rs = .Execute
 
    End With
 
    'To loop through the districts
    If rs.EOF = False And rs.BOF = False Then
        Do While Not rs.EOF
            Dim i As Integer
            For i = 0 To (rs.Fields.count - 1)
                Dim DistrictCode As String
                DistrictCode = rs.Fields(i).Value
 
                'Refresh Pivot Tables
                'Call Refresh.Refresh(salesPeriod, DistrictCode, Region)
 
                'Create Output Files
                Call CreateOutputFile.CreateOutputFile(salesPeriod, DistrictCode, Region, folderName)
            Next i
            rs.MoveNext
        Loop
    End If
 
    rs.Close
    cn.Close
 
    Set cn = Nothing
    Set cmd = Nothing
    Set rs = Nothing
 
 
End Sub

[Rabbit]

It shouldn't even give you one value. Your line 67 should prevent any of the code from running. Since you haven't progressed through any of the recordset yet, the BOF will always be true.

[c112994]

Oh okay, so how should I fix this? Or how must I approach it to fix it?

[Rabbit]

I assume you're doing that check to see if there's at least one record, in which case, you need to change the BOF check from false to true because if there's at least one record, then it will be at the BOF but not at the EOF.

[c112994]

Okay, I already got it. Thanks!