Guys, I ran in to the same problem with VBScript and ADO - not in ASP, just standalong VBScript, but the same problem. Here is what I find that triggers it, and I'm convinced it's something within ADO. The scenario is complicated, but I've had this happen to me in more than one instance.
Consider the following SQL that is being executed:
The order of the column names is important, as you will see later.
select name, address_1, city, state, zip, country, my_flag from tablename
In looping through the resulting recordset, I am looping through the Fields collection of each row in the recordset (i'm building a CSV file if it helps you to know the purpose here);
However, one of the fields I need, I need to pull explicitly, BEFORE the For Each...Next loop; you can see it above the Set oFields line in the code above (and note the trailing comment that speaks to this problem); I've extracted it below for clarity:
Do While not oRs.Eof
vReportLine = ""
iRowNum = iRowNum + 1
' *** loop through fields in this row to build detail line
vMyFlag = cStr(oRs("my_flag")) ' need to do this outside of loop or it screws up the position in the loop...
Set oFields = oRs.Fields
For Each oField In oFields ' loop through all fields in the row
vFieldValue = oField.Value
Next ' For each oField in oFields
Loop ' Do While not oRs.Eof
. vMyFlag = cStr(oRs("my_flag")) ' need to do this outside of loop or it screws up the position in the loop...
When I run my script, I find that what happens is the field (aka column) preceding the column that I explicitly retrieve by name (my_flag) returns blank when being retrieved from the collection in the For...Each loop. In this example, the column named 'country' is returned as blank in vFieldValue when it gets to that item in the collection.
My solution was to re-order the columns in the select, to put the column that I had to retrieve explicitly, first in the list:
there being no column/field before the first one, everything worked out.
select my_flag, name, address_1, city, state, zip, country from tablename
I am not sure why this is, but I have seen this in other scenarios besides this implementation on other systems, with different versions of SQL and ADO.
I hope this helps, or that someone can comment on this.