ADO Recordset returning blank fields *RESOLVED*

[BenFinkel]

Hey everyone,

I've got the following code:

Set rsMyRS = cn.execute("SELECT * FROM INDEX")
Do until rsMyRS.EOF
    Debug.Print(rsMyRs.Fields(26).Value)

     rsMyRS.MoveNext
Loop

Where cn is a connection to an SQL Server running on my computer.

The problem is that this prints out a blank, even though I can go look at the table in Ent. Manager and see that the field has a value in it. After I play around with the recordset object in the Debug window, sometimes I can get it to display the real value, but not always.

Anyone know why it would return a blank? It's not even returning NULL, but just a blank string.

Thanks!

[RhinoBull]

If you're only looking for Fields(26).Value 9whatever that field is) then simply specify its name in the select instead:

Set rsMyRS = cn.execute("SELECT SomeField FROM INDEX")
Do until rsMyRS.EOF
    Debug.Print "" & rsMyRs!SomeField 'or use IsNull() to handle nulls
     rsMyRS.MoveNext
Loop

Note: personally, I never use syntax such Fields(Index) as it's too easy to be off and get wrong results.

[RobDog888]

Also, change the table name from INDEX to something else. Its
not good programming to name user defied objects the same
name as system objects.

[D12Bit]

maybe if you set the CursorLocation to "your side"

rsMyRS.CursorLocation = adUseClient 'Set the cursor to your side
 
Set rsMyRS = cn.execute("SELECT * FROM INDEX")
Do until rsMyRS.EOF
    Debug.Print(rsMyRs.Fields(26).Value)
 
     rsMyRS.MoveNext
Loop

[ahara]

Yeah I fixed this type of problem messing with the cursor location of the connection object as well - although in my case - switching to server side did the trick. Maybe play around with that a bit.

[BenFinkel]

Robdog, Rhinobull,

The example I gave was a trimmed down version of the code that explicitly showed the error I was receiving. My table isn't really named INDEX and I'm not really using the field index to reference the field. I was looking for some information that pertained specifically to the field returning blank data when I knew for a fact that there was data in it.


*RESOLUTION*

Turns out what I needed to do (and this may relate to the Cursor location) was use the Recordset.Open method instead of the Connection.Execute method and it worked find. It also runs much more quickly, so I'm assuming that the Open method somehow loads the data locally while the Execute method continually re-connects to the database.

--Ben

[ahara]

Indeed, the open method places the cursor location (server side I believe) differently than the execute method which if memory serves puts the cursor client side (might be the other way around - my dislexia aside!! )

[RhinoBull]

Despite the obvious difference - location of your cursor has nothing to do with reading data from field as long as field name (or index) is correct.