Using variable as a field name 1

[plcman]

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.

 

[gmmastros]

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

 

[plcman]

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

 

[gmmastros]

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

 

[plcman]

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

 

[gmmastros]

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...

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....

[tt][blue]
With RS.Fields
Debug.Print .Item("Column1").Value
Debug.Pring .Item("Column2").Value
End With[/blue][/tt]



-George

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

 

[HarleyQuinn]

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 before posting.

 

[gmmastros]

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....


[tt][blue]arr = Split(RS.GetString, vbCr)[/blue][/tt]

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