INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Using variable as a field name

Using variable as a field name

(OP)
Hi

I am using VB6 and an MS SQL Express database. I have an SQL statement that has a variable in the Select section.

sql_str = "SELECT DISTINCT " & dist_filt & " from sessions WHERE " & filt(level)

The dist_filt variable holds the field name i am interested in and the filt(x) holds a string.

The SQl statement works fine and the recordset opens up. My problem is in the next part, i am trying to do the following to fill an array.

With recordset
    .movefirst
    arr(x)=! & dist_filt
etc

My problem is with the syntax for the ! & dist_filt. Normally of course this would be arr(x)=!field but I have been struggling with the syntax to build the ! into this without it being enclosed in quotes.

Any help wouldbe gratefully recieved.

RE: Using variable as a field name

Since there is only 1 column returned, you could use the ordinal value to get the data from the recordset.  like this...

arr(x) = .Fields(0).Value

-George

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

RE: Using variable as a field name

(OP)
gmmastros

Many thanks, that works perfectly in this situation.

I would still like to know about the syntax using the ! for future reference.

Thanks again!!

Regards

Alan Edwards

RE: Using variable as a field name

Would you mind explaining why you want to use the !

-George

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

RE: Using variable as a field name

(OP)
gmmatros

Good question! It is the way I was shown when taking over a project some years ago and have stuck with it eversince.

If there is a better way to reach different fields in a Selected recordset, I am up for it.

Regards

Alan Edwards

RE: Using variable as a field name

Well... I'm not saying there is anything wrong with your method, but there is a better way.  One that is faster.

Things are a little weird when there is a single column, because you could argue that this method is NOT faster.  Usually, what I like to do is to create a user defined type, and then an array of that type.

Something like this...

CODE

Private Type Blah
    Id As Long
    Name As String
    EyeColor As String
End Type

Private Blahs() As Blah

Public Sub InitializeBlah()
    
    Dim RS As ADODB.Recordset
    Dim fldId As ADODB.Field
    Dim fldName As ADODB.Field
    Dim fldEyeColor As ADODB.Field
    Dim i As Long
    
    Set RS = GetRecordset("Select Id, Name, EyeColor From SomeTable")
    Set fldId = RS.Fields.Item("ID")
    Set fldName = RS.Fields.Item("Name")
    Set fldEyeColor = RS.Fields.Item("EyeColor")
    
    ReDim Blahs(RS.RecordCount - 1)
    
    i = 0
    While Not RS.EOF
        With Blahs(i)
            .Id = fldId.Value
            .Name = fldName.Value
            .EyeColor = fldEyeColor.Value
        End With
        i = i + 1
        RS.MoveNext
    Wend
    
    RS.Close
    Set RS = Nothing
    
End Sub

As you probably realize, using a with block will speed up the execution of code.  When you are trying to populate an array (of user defined types), it would be ideal to use several with blocks, but you can't.  By using the FIELD object, you get the benefit of a with block (faster execution) without actually using a with block, which allows you to use the with block for the array.

Make sense?

Of course, this method requires you to write a lot more code, so even I don't always use this method.  Truth is, most recordsets are relatively small (at least they should be).  So, you will not really notice a speed improvement unless you are dealing with 100's or rows or more.

If I'm not too concerned about performance (because it's already fast), then I'll just do this....


With RS.Fields
  Debug.Print .Item("Column1").Value
  Debug.Pring .Item("Column2").Value
End With


-George

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

RE: Using variable as a field name

Don't know if it'll be of any use (to either Alan or you George) but this thread has a lot of information/comparissons in it about speeds with Recordsets.

How fast can you loop an ADO recordset?

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

RE: Using variable as a field name

In this case, since there is a single column returned and you are filling an array with the data, the fastest (probably) method would be....


arr = Split(RS.GetString, vbCr)

This one statement will populate the array with all the data from the recordset with just one statement.  This only works because there is a single column returned from the query.


-George

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close