HELP, rs.fields(i).value is destructive

[QBDev]

HELP!!!
I think that I am going crazy.

1 program seems to get this problem intermittantly.

I can only read the data once. They it goes to null

ODBCRS.Close:Set ODBCRS = ODBCCon.Execute(SQLString, iRecordsAffected)
? ODBCRS.Fields(93).Value,ODBCRS.Fields(93).Value
Sergio Null

Even ? ODBCRS.Fields(94) will charge field 93 to nulls

It does NOT happen on all fields. Field 94 in this case works fine.

What am I missing?

Help!!!!!!!!!!!!!
Jack

[si_the_geek]

Welcome to VBForums

At the moment we haven't got enough information to be able to help - the kind of thing we need are the first 2 bullet points of Before you post..., the SQL statement you are running, and the data type of the field(s) that are returned by it (especially the one that is giving you problems).

[brucevde]

This will happen if the datatype of Fields(93) is a Blob field (memo, text, image). Check out this code

If Not IsNull(ODBCRS.Fields(93).Value) Then
    Debug.Print ODBCRS.Fields(93).Value 
End If

The Debug statement "could" print Null. This is because Blob fields are accessed in "Chunks" each time they are referenced. I can't remember the default chunk size but I think it is 4kb.

In the code above the If statement causes ADO to get the 1st chunk of data. The Debug statement gets the 2nd chunk but if all data was returned in the 1st chunk, it prints Null.

[edit] The above depends on which Provider you are using to connect to the database. If I remember correctly it only happens with the default provider (MSDASQL).