Loop Through Recordset and save results 3

[Palmyra]

I need to open a recorset, select the first 22 names and name them so that I can output them to a text file. I created variables Name1, Name2, Name3, etc.

How can I set the value of the first 22 names to my variable names.

Select Number, Name from Table

Hope this is clear, any help appreciated.

 

[gmmastros]

If you want the first 22, then you should include an Order By in your query. Otherwise, you will not be guaranteed to get the 'top' 22. Also, I suggest you use a TOP clause in your query. Imagine there are 1,000,000 rows in your table. Your query would return all 1,000,000. You would use the first 22 rows and discard the rest. This is terribly inefficient. Instead....

Select Top 22 Number, Name From Table Order By Number

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Disferente]

Do you know how to open the recordset?

Something like, if so, you could do something like this:

dim a as integer
dim Names() as string
redim names(21)
for a=0 to 21
names(a)=recordset!Name
recordset.movenext
next a

 

[Palmyra]

Disferente, that seems perfect. However, what if I don't have 22? How do I exit the loop if I have less than 22?

Thanks.

 

[Disferente]

Just how do you know how many to save? If you have it in a variable you could replace 21 with the variable-1.

 

[ArtieChoke]

Test the recordset eof condition and exit if that's set.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[SBerthold]

Here is one method, without having to use a loop:

    If Not (rs.EOF And rs.BOF) Then
        Dim aRecords() As Variant, vElement As Variant
        rs.MoveFirst
        
        aRecords = rs.GetRows(22, 1, 0)
        For Each vElement In aRecords
            Debug.Print vElement
        Next vElement
    End If

In this example, rs.GetRows(22, 1, 0) will return an array of up to 22 records, starting at the first record, returning the value of the first field (0) (thus: 22,1,0)

 

[Palmyra]

Just how do you know how many to save? If you have it in a variable you could replace 21 with the variable-1. "

Disferente,

I'm populating data on a label and know that the maximum rows that will fit is 22. If there are fewer than 22 rows, I get an error 'no current row'

Thanks for the response, but I'm not sure what you mean.

 

[Disferente]

If you are using what I wrote, you could just add
If recordset.eof then exit for
after
for a=0 to 21

 

[Palmyra]

Thank you Disferente, that worked perfectly.

 

[ashthud]

Quick note that may (or may not) help... to find the size of an array you could use Ubound() function:

dim a(2) as string
dim i as integer
for i = 0 to ubound(a,1)
'or simply ubound(a) as the second parameter is the dimension you are refering to, and 1 is assumed
next i

 

[Disferente]

This is about recordsets not arrays, so to see the number of records your would use recordset.count-1 (-1 because you start at 0)

 

[ashthud]

I was just refering to your:

dim a as integer
dim Names() as string
redim names(21)
for a=0 to 21
names(a)=recordset!Name
recordset.movenext
next a

where you say "for a=0 to 21"
you could have said "for a=0 to Ubound(names)"

I did warn you it might not help, which it didn't =P

 

[Palmyra]

If I want each of my 21 rows in this recordset to have a unique name, how can I accomplish that? I need to output to a text file:

name1
name2
name3 etc.

dim a as integer
dim Names() as string
redim names(21)
for a=0 to 21
names(a)=recordset!Name
recordset.movenext
next a

 

[DjangMan]

Some options:
Use a Dictionary object to hold what you've already added to your array. If it isn't in the dictionary then add it to the array and then to the dictionary.

If your recordset is sorted simply use a variable to track the last name added to the list. If the current record in the recordset has a different value than what your variable is holding then add the record to your array, update the last name variable and move on.

 

[Palmyra]

Thanks for the response, but I don't understand what a dictionary object is.

What I need is 21 unique values to output to a text file. But the variable name will keep changing, right?

 

[DjangMan]

Something like this:

LastName = '@@@@@'
NameCount = 0
while not(recordset.eof) and NameCount < 21
  if recordset!name <> LastName then
    NameCount = NameCount + 1
    names(NameCount) = recordset!Name
    LastName = recordset!Name
  end if
  recordset.movenext
wend

Now you can dump your array to the text file. Or you could dump the names within the loop and skip the array altogether.

 

[Palmyra]

Thanks, DjangMan.

I hate being dense, but
-In LastName = '@@@@@' what does '@@@@@' represent ?
-What is names in 'names(NameCount) = recordset!Name'. Do I just dim that as a string?
-When you say dump the names within the loop and skip the array, do you mean I should create 22 variables? I'm not clear on that.

 

[gmmastros]

I'm confused. If you had used my suggestion (the first reply to this thread), many of your other problems would have been simplified. Specifically, if you select the [!]TOP 22[/!] rows, then you can simply dump all the results you get to a file. There's no need for an array or a dictionary object, or anything else for that matter.

Later, you added that the names must be unique. Again, you can let the database do the 'heavy' lifting for you.

Here's an example.

    Dim iFile As Integer
    
    [green]' Get a free file number.[/green]
    iFile = FreeFile

    [green]' Get the data you want from the DB.[/green]
    Call RS.Open("Select [!]Distinct Top 22[/!] Number, Name from Table", DB)
    
    [green]' Open the file[/green]
    Open "C:\PalmyraTest.txt" For Output As #iFile

    [green]' Loop through the recordset[/green]
    While Not RS.EOF
        [green]' Write each data item to a file[/green]
        Print #iFile, RS.Fields.Item("Name").Value
        RS.MoveNext
    Wend

    [green]' Close the file.[/green]
    Close #iFile

By letting the database do most of the work, the code is simplified, and will likely run faster.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DjangMan]

Dang - I tried that but had Distinct on the wrong side of Top - hence my work around. Your method is clearly the way to go.