By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,400 Members | 1,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,400 IT Pros & Developers. It's quick & easy.

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

P: 5
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

Expand|Select|Wrap|Line Numbers
  1. Sub Runner()
  2.  
  3.     Dim folderName As String
  4.     Dim salesPeriod As String
  5.     Dim Region As String
  6.     Dim cn As ADODB.Connection
  7.     Dim cms As ADODB.Command
  8.     Dim rs As ADODB.Recordset
  9.     Dim SQL As String
  10.  
  11.     'Make Output Folder
  12.     folderName = CStr(Year(DateTime.Now) & "." _
  13.                     & Month(DateTime.Now) & "." _
  14.                     & Day(DateTime.Now) _
  15.                     & " " & Hour(DateTime.Now) _
  16.                     & "." & Minute(DateTime.Now) _
  17.                     & "." & Second(DateTime.Now))
  18.  
  19.     'Next time, make the username a variable to be accessed on any computer
  20.     ChDir "C:\Users\E0214206\Desktop"
  21.     MkDir (folderName)
  22.  
  23.     'Getting the inputted salesPeriod
  24.  
  25.     salesPeriod = Range("E1").Value
  26.  
  27.     'Getting the inputted Region
  28.     Region = Range("E2").Value
  29.  
  30.     Application.DisplayAlerts = False
  31.     'Sheets("Inputs").Delete
  32.     Application.DisplayAlerts = True
  33.  
  34.     Set cmd = New ADODB.Command
  35.     Set cn = New ADODB.Connection
  36.  
  37.     SQL = "SELECT DistrictCode FROM dbo.Analytics_PerformanceData WHERE DistrictCode IS NOT NULL ORDER BY DistrictCode ASC"
  38.  
  39.     With cn
  40.         'timeout
  41.         .CommandTimeout = 0
  42.         'set connection string
  43.         .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"
  44.         'open connection
  45.         .Open
  46.     End With
  47.  
  48.     With cmd
  49.         'set activeconnection to command object
  50.         .ActiveConnection = cn
  51.  
  52.         'disable time limit
  53.         .CommandTimeout = 0
  54.  
  55.         'set commandtype
  56.         .CommandType = adCmdText
  57.  
  58.         'set commandtext
  59.         .CommandText = SQL
  60.  
  61.         'execute query
  62.         Set rs = .Execute
  63.     End With
  64.  
  65.  
  66.  
  67.     'To loop through the districts
  68.     Dim count As Integer
  69.     For count = 0 To 5
  70.         Dim w As Integer
  71.         w = count
  72.  
  73.         Dim DistrictCode As String
  74.         DistrictCode = rs.Fields(count)
  75.  
  76.         'Refresh Pivot Tables
  77.         'Call Refresh.Refresh(salesPeriod, DistrictCode, Region)
  78.  
  79.         'Create Output Files
  80.         Call CreateOutputFile.CreateOutputFile(salesPeriod, DistrictCode, Region, folderName)
  81.     Next count
  82.  
  83.     rs.Close
  84.     cn.Close
  85.  
  86.     Set cn = Nothing
  87.     Set cmd = Nothing
  88.     Set rs = Nothing
  89.  
  90.  
  91. End Sub
Apr 29 '14 #1
Share this Question
Share on Google+
7 Replies


Expert 100+
P: 1,240
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
Apr 29 '14 #2

Rabbit
Expert Mod 10K+
P: 12,430
You never loop through your recordset. All you do is loop through 6 columns in the recordset.
Apr 29 '14 #3

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

Expand|Select|Wrap|Line Numbers
  1. Sub Runner()
  2.  
  3.     Dim folderName As String
  4.     Dim salesPeriod As String
  5.     Dim Region As String
  6.     Dim cn As ADODB.Connection
  7.     Dim cms As ADODB.Command
  8.     Dim rs As ADODB.Recordset
  9.     Dim SQL As String
  10.  
  11.     'Make Output Folder
  12.     folderName = CStr(Year(DateTime.Now) & "." _
  13.                     & Month(DateTime.Now) & "." _
  14.                     & Day(DateTime.Now) _
  15.                     & " " & Hour(DateTime.Now) _
  16.                     & "." & Minute(DateTime.Now) _
  17.                     & "." & Second(DateTime.Now))
  18.  
  19.     'Next time, make the username a variable to be accessed on any computer
  20.     ChDir "C:\Users\E0214206\Desktop"
  21.     MkDir (folderName)
  22.  
  23.     'Getting the inputted salesPeriod
  24.  
  25.     salesPeriod = Range("E1").Value
  26.  
  27.     'Getting the inputted Region
  28.     Region = Range("E2").Value
  29.  
  30.     Application.DisplayAlerts = False
  31.     'Sheets("Inputs").Delete
  32.     Application.DisplayAlerts = True
  33.  
  34.     Set cmd = New ADODB.Command
  35.     Set cn = New ADODB.Connection
  36.  
  37.     SQL = "SELECT DistrictCode FROM dbo.Analytics_PerformanceData WHERE DistrictCode IS NOT NULL ORDER BY DistrictCode ASC"
  38.  
  39.     With cn
  40.         'timeout
  41.         .CommandTimeout = 0
  42.         'set connection string
  43.         .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"
  44.         'open connection
  45.         .Open
  46.     End With
  47.  
  48.     With cmd
  49.         'set activeconnection to command object
  50.         .ActiveConnection = cn
  51.  
  52.         'disable time limit
  53.         .CommandTimeout = 0
  54.  
  55.         'set commandtype
  56.         .CommandType = adCmdText
  57.  
  58.         'set commandtext
  59.         .CommandText = SQL
  60.  
  61.         'execute
  62.         Set rs = .Execute
  63.  
  64.     End With
  65.  
  66.     'To loop through the districts
  67.     If rs.EOF = False And rs.BOF = False Then
  68.         Do While Not rs.EOF
  69.             Dim i As Integer
  70.             For i = 0 To (rs.Fields.count - 1)
  71.                 Dim DistrictCode As String
  72.                 DistrictCode = rs.Fields(i).Value
  73.  
  74.                 'Refresh Pivot Tables
  75.                 'Call Refresh.Refresh(salesPeriod, DistrictCode, Region)
  76.  
  77.                 'Create Output Files
  78.                 Call CreateOutputFile.CreateOutputFile(salesPeriod, DistrictCode, Region, folderName)
  79.             Next i
  80.             rs.MoveNext
  81.         Loop
  82.     End If
  83.  
  84.     rs.Close
  85.     cn.Close
  86.  
  87.     Set cn = Nothing
  88.     Set cmd = Nothing
  89.     Set rs = Nothing
  90.  
  91.  
  92. End Sub
Apr 29 '14 #4

Rabbit
Expert Mod 10K+
P: 12,430
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.
Apr 30 '14 #5

P: 5
Oh okay, so how should I fix this? Or how must I approach it to fix it?
Apr 30 '14 #6

Rabbit
Expert Mod 10K+
P: 12,430
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.
Apr 30 '14 #7

P: 5
Okay, I already got it. Thanks!
Apr 30 '14 #8

Post your reply

Sign in to post your reply or Sign up for a free account.