ADODB.RecordSet object give me an empty value when asked for some fields.

[ScagliaSP]

Hi
i'm having some issues with an ADODB.RecordSet object. With Classic ASP and VBScript, i'm trying to recover data from this particular object. it is connected to and local SQL Server Express 2008 R2, and it was openned with an statement like this:

strSQL1 ="SELECT * from ((manutint mi LEFT OUTER JOIN manutint_codDoc md ON  mi.mi_documento=md.doc_cod) LEFT OUTER JOIN cliente cl ON mi.mi_cliente=cl.cli_cod) LEFT OUTER JOIN fornecedores fr on mi.mi_encaminhadoa=fr.frn_cod and mi.mi_encaminhadoa = fr.frn_cod WHERE mi.mi_status<>4 and "
&_ " mi.mi_clientecc LIKE '"&cc&"' AND mi.mi_entrada LIKE '"&keyword22&"' order by mi.mi_cod"
Set objRS1 = Server.CreateObject("ADODB.RecordSet")
objRS1.Open strSQL1, objConn

where cc and keyword22 are used for search purposes only. they both are simple "%" in my tests. objConn as you guess, are an ADODB.Connection object that should be working just fine, since it's used for all pages of the site and the problem is only in one page.
The page code is awful big, so it would be no good to post it all. the bit that should work but dont is:

if Not objRS1.EOF Then 
	objRS1.MoveFirst
	Do While Not objRS1.EOF
                Response.write "<tr>"
                Response.Write "<td>" &  objRS1("cli_nome")& "</td>"
		Response.Write "<td>" &  objRS1("mi_dataentrada")& "</td>"
		Response.Write "<td >" &  objRS1("frn_nome")& "</td>"	
		Response.Write "<td >" &  objRS1("mi_equipamento")& "</td>"	
		Response.Write "<td >" &  objRS1("mi_ns")& "</td>"
		Response.Write "<td >" & objRS1("doc_desc") &"</td>"
		Response.Write "<td >" & objRS1("mi_numdocumento") &"</td>"
                Response.Write "</tr>"
		objRS1.MoveNext
	Loop
End if

This code is used to fill an table with the data of the objRS1, which is our ADODB.RecordSet object.
this code has no erros and should work with no problem, but exactly the fields "doc_desc" and "mi_numdocumento" are showed as "", empty values. Of course I looked into the database tables and executed the exactly same SQL query in MS SQL Management Studio to check if there was data into the database in the first place, but it was ok. Also tried to search some kind of difference between the fields that work and those that don't, to no avail. also tried to do some IsNull() and other checks with the values given by objRS1, and they returned false. it as if the field's content was an "", but it is not.
Did someone has ever seen something like this? is there some kind of test that i could do to track down the problem that i still didn't?
any help or suggestion is apreciated. even an small guess. already lost 4 hours into this.

[DaveMaxwell]

Did you response.write strSQL1 to ensure what you're executing is what you think you're executing? Or put an else on the if statement to see if that's the condition being thrown?

What debug steps have you taken so far?

[ScagliaSP]

Hi, DaveMaxwell, thanks for replying.
Yes, i did response.write strSQL1, and also tried using it in SQL Management studio in order to se if there was an error with it, but it was alright in both cases.

No, i din't put an else so far, but i don't think that the code is flowing through the if statement because all the other fields are been showed, and the table has exactly the number of rows it should, but only the doc_desc and the mi_numdocumento columns are empty.

this code is big and i was not the programer who wrote it, so i can't be totally sure there is nothing somewhere else meddling with this, but i already tried response.write these two fields in an code apart, and got exactly the same results, all fields ok except for these two. the next step i'm planing to take is to go thought all the fields of the table to if there is more fields who doesn't show as well, in order to look for an pattern. since my work time ends now, i'll be doing so tomorrow. Thanks again.

[DaveMaxwell]

Did the generated query (i.e. copied/pasted) return values for those fields in the studio run? If it does there, then there's an issue - perhaps you've got multiple fields with the same name? You're using the dreaded select * instead of specifying which fields are to be selected.

[ScagliaSP]

Hi.
When i execute the generated query in studio, the values for the fields are there. also tried to find fields with the same name,but each field's name follow the "tablename_fieldname" pattern, so there aren´t any.
As for the tests i did with all the fields, there are a few more fields that doesn't show up, but some are nvchar(255) type, other are int, allow nulls/not allow nulls type, and after comparing the properties of columns that show and those who don't side-by-side, i still didn't manage to find any kind of pattern.
Since the query worked on studio, i had already eliminated the query from the possibilities list. but i didn't know about the field's name issue.
i'll try to use an diferent query in an separated file, to se how it behaves. thank's again, DaveMaxwell.

[DaveMaxwell]

You could also try running something like this to see what fields are actually in your returned recordset. Just run it once (put it before the loop...)

<%
   For i = 0 To objRS1 .Fields.Count -1
     Response.Write "Field Name: " & objRS1.Fields(i).Name & "<br>"
     Response.Write "Field Value: " & objRS1(i) & "<br>"
   Next
%>

[ScagliaSP]

Hi, DaveMaxwell, thanks again.
i used the code you gave me both in the code apart i said i whould wrote and in the page that has the bug.
in the code apart, the fields showed well, both in your code and in mine. in mine code i only looped through the objRS1 and and printed two fields, one that worked in the page and one that don't, and both was ok. the code apart is this one:

Response.write("TESTING<br/>")

Set objRS1 = Server.CreateObject("ADODB.RecordSet")

strSQLtest="SELECT * from ((manutint mi LEFT OUTER JOIN manutint_codDoc md ON  mi.mi_documento=md.doc_cod) LEFT OUTER JOIN cliente cl ON mi.mi_cliente=cl.cli_cod) LEFT OUTER JOIN fornecedores fr on mi.mi_encaminhadoa=fr.frn_cod and mi.mi_encaminhadoa = fr.frn_cod WHERE mi.mi_status<>4 and mi.mi_clientecc LIKE '%' AND mi.mi_entrada LIKE '%' order by mi.mi_cod"
objRS1.Open strSQLtest, objConn
Response.write "ObjRS1 using query "&strSQLtest&"<br/>"
For i = 0 To objRS1.Fields.Count -1
	Response.Write "Field Name: " & objRS1.Fields(i).Name & "<br>"
	Response.Write "Field Value: " & objRS1(i) & "<br>"
Next
objRS1.MoveFirst
Do while not objRS1.EOF
	response.write "cliente num: "&objRS1("mi_cliente")&"<br/>"
	response.write "doc num: "&objRS1("mi_documento")&"<br/>"
	objRS1.MoveNext
loop
response.write "End of file<br/>"
objRS1.Close
objConn.Close

so the culprit is not the strSQL1.

As for the tests in the page itself, i got some funny results.
i pasted your code before the loop, and it worked just fine. the damn two fields and all the others were there. name and value.
then i pasted it after the loop (with an objRS1.MoveFirst, of course). same thing, worked fine, all fields there.
and inside the table, the strangest one: the two field's columns was all epmty again, except for the first row which is the row that was
printed-out in your code. if i remove your code, the first row goes back to been empty.
tied to put an objRS1.MoveNext before your code, to see if the second row values where going to show up now, but they doesn't.
What the hell is happening with this code?
My time at work is ended today. i 'll be continuing tomorow. thans again, DaveMaxwell.
see you tomorow.

[ScagliaSP]

Hi, it's me again.
there are already some time since i last posted here, and the topic seens prety dead by now.
which isn't an surprise, since nothing makes sense in this code. i'm posting just to report that i've found
an solution to the problem, although i still don't know why this code don't work the way it is.
the solution is as follow:

with the results from the tests i made with DaveMaxwell's help, i could be sure that strSQL1 and objRS1 were both ok.
At least, before the line inside the loop that generated the table, were i called objRS1("doc_desc") and it returned an empty space.
then, i started to put and

temp=objRS1("doc_desc")

in random parts of the code, and instead of responsewriting objRS1("doc_desc"), responsewrited temp.
As i said many times before, the code is big. even the while loop i first posted is an very simplified version, since the values of these
fields are used to make an "conditional formating" of the table. the real loop has aproximated 130 lines. most are copy/paste due
to the last programer didn't liking to write functions instead. anyway, i managed to track down the line where objRS1 stoped to return
correctly, which was just after the fields from cliente table begin to been used. i couldn't figure why this happens, so i decided to leave
the code with the temp variable, in order to finish this page already. thank's again by your help DaveMaxwell.

[DaveMaxwell]

Well, that doesn't make sense at all, but I'm glad you found a work around.

One question, though (I should have thought of this earlier)....Are the fields you're having problems with memo/text fields? If so, that might explain the problem. I recall a problem with memo/text fields that would have problems if they weren't the last fields in the selection criteria.

If they are memo/text fields, the solution SHOULD be to change the select * to select the fields you're actually going to use, and put the text/memo fields last.

So in other words, your selection criteria (based on your earlier post) would be:

SELECT cli_nome
         , frn_nome
         , mi_dataentrada
         , mi_equipamento
         , mi_ns
         , doc_desc
         , mi_numdocumento

It's a more efficient process anyway unless you're using each and every field from all of those tables.

[ScagliaSP]

Hi again, daveMaxwell.
No, one field is an int, and the other is listed as nvchar(30) in management studio. not sure if nvchar is an memo (i first learned to use databases in Paradox 7, lot could've changed since then), but i think it's not. As for the Query method, i dont know about change it, because:
1- the strSQL1 is definied in an page external to the page i'm currently working on, and although i'm sure it's not used anywere else,
because of the first programer habit of copy/paste instead of writing functions, i would need to change a few more lines here and there,
so it would take much more time.
2- Although the page don't use all the fields from these tables, it uses the bigger part of they. all the left joins are because the main table has only
the id of records from other tables, but in order to make it readable by humans, its better to show names instead of numbers. but each of these tables
with the actual data of the records has 4, 5 fields, and the page uses 2 or 3.
3- the page doesn't take even 2 sec to load, so i think it's not that critical anyway.

i've cleaned the code a bit (it had a lot of code lines "commented", you know, to make tests without erasing the lines),
so i'll post the whole loop, only in case someone has curiosity in it. As you probably already guessed, english is not my native
language, and so the comments and names aren't in english, but i think it's still readable.

			if Not objRS1.EOF Then 
				ObjRS1.MoveFirst
				Do While Not objRS1.EOF
					Select Case objRS1("mi_status")
						Case 1
							Response.Write "<tr bgcolor='#ccccff'>"
						Case 2
							Response.Write "<tr bgcolor='#f5BEd3'>"
						Case 3
							Response.Write "<tr bgcolor='#ccffcc'>"
						Case 4
							Response.Write "<tr bgcolor='#ccffcc'>"
						Case 5
							Response.Write "<tr bgcolor='#ccffcc'>"
						Case 6
							Response.Write "<tr bgcolor='#ff9999'>" 
						Case 8
							Response.Write "<tr bgcolor='#E0FFFF'>"
					end Select    
					Response.Write "<td >" &cont& "</td>"
					
					'n縊 confunda os dois ifs abaixo. eles sao if de uma intrucao somente, nao ifs compostos. isso causou uma pequena confusao
					'entao nao faca isso denovo.
					if Session("Acesso") >= 8000  then	Response.Write "<td class='col1' align='center'><A href='excluir_osint.asp?mi_cod="&objRS1("mi_cod")&"'><img border='0' src='../imagens/lixeira.gif'></a></td>"
					if Session("Acesso") >= 5000  then	Response.Write "<td class='col1' align='center'><A href='alterar_osint.asp?mi_cod="&objRS1("mi_cod")&"'><img border='0' src='../imagens/bteditar.gif'></a></td>"
					Response.Write "<td class='col1' align='center'><A href='ospreview.asp?mi_cod="&objRS1("mi_cod")&"'><img border='0' src='../imagens/lupa.gif'></a></td>"
					
					'A LINHA ABAIXO PROVAVELEMENTE ESTAVA CAUSANDO A ANOMALIA NA TABELA. ELA ﾉ IDENTICA A UMA LINHA ACIMA
					'if Session("Acesso") >= 8000  then	Response.Write "<td class='col1' align='center'><A href='alterar_osint.asp?mi_cod="&objRS1("mi_cod")&"'><img border='0' src='../imagens/bteditar.gif'></a></td>"
					Response.Write "<td>" &  objRS1("mi_cod")& "</td>"
					Select Case objRS1("mi_status")
						Case 1
							Response.Write "<td>Em manutencao</td>"
						Case 2
							Response.Write "<td>Aguardando Aprovacao de Orcamento</td>"
						Case 3
							Response.Write "<td>Manutencao Concluida</td>"
						Case 4
							Response.Write "<td>Devolvido ao Cliente</td>"	
						Case 5
							Response.Write "<td>Orcamento Aprovado</td>"
						Case 6
							Response.Write "<td>Aguardando Pecas</td>"
						Case 8
							Response.Write "<td>Manutencao Concluida Sem Conserto</td>"
					end Select					
					
					'O uso desta variavel temporaria nao deveria ser necessario...
					'mas e. por algum motivo, depois do bloco abaixo, estes dois campos
					'comecam a retornar valores vazios. se eu uso uma variavel para armazenar
					'o valor correto antes que o bug ocorra, consigo usar o valor como quero.
                                        
                                        'here the temporary variables. until this point, objRS1 and the two fields work OK.
					Dim mi_doc,mi_numdoc
					mi_doc=CINT(objRS1("mi_documento"))
					Select Case mi_doc
						Case 1
							mi_doc="Carta"
						Case 2
							mi_doc="Nota Fiscal"
						Case 3
							mi_doc="Nenhum"
						Case Else
							if(bolTeste)then mi_doc="Erro, Valor Invalido: ["&mi_doc&"]"
					end Select
					'objRS1 retorna um monte de espacos em branco quando o campo esta vazio.
					'a funcao trim serve para remover os espacos em branco de uma string. 
					'assim, se usada no retorno de objRS1, esse valor se torna um "".
					if Trim(CStr(objRS1("mi_numdocumento")))="" then 
						mi_numdoc="N/A"
					else 
						mi_numdoc=objRS1("mi_numdocumento")
					end if
					
					'apartir daqui, objRS1("mi_documento") retorna sempre "". nao pergunte porque.
					Response.Write "<td>" & objRS1("cli_nome")& "</td>" 
                                        'But After this line, they dont work anymore. don't ask why.
					Response.Write "<td>" & objRS1("mi_dataentrada")& "</td>"
					Response.Write "<td>" & objRS1("frn_nome")& "</td>"	
					Response.Write "<td>" & objRS1("mi_equipamento")& "</td>"	
					Response.Write "<td>" & objRS1("mi_ns")& "</td>"
					Response.Write "<td>" & mi_doc & "</td>"'Variaveis declaradas acima
					Response.Write "<td>" & mi_numdoc & "</td>"'leia o comentario para detalhes
					
					'Status = 1 - Equipameno entrou em manutencao
					if objRS1("mi_status") =1 then
						Response.Write "<td><select size='1' name='mi_mudastatus'  id='mi_mudastatus' class='selectm' tabindex='1' onchange='window.location.href = this.value;'>"
						response.write "<option value='' selected>-----</option>"
						response.write "<option value='http://www.wps-sa.com.br/asstec/site/addevtos.asp?mi_cod="&objRS1("mi_cod")&"&evt=2' >AGUARDAR APROVACAO DE ORCAMENTO</option>"
						response.write "<option value='http://www.wps-sa.com.br/asstec/site/addevtos.asp?mi_cod="&objRS1("mi_cod")&"&evt=6' >SOLICITAR PECAS</option>"
						response.write "<option value='http://www.wps-sa.com.br/asstec/site/addevtos.asp?mi_cod="&objRS1("mi_cod")&"&evt=3' >FINALIZAR MANUTENCAO</option>"
						response.write "<option value='http://www.wps-sa.com.br/asstec/site/addevtos.asp?mi_cod="&objRS1("mi_cod")&"&evt=8' >SEM CONSERTO</option>"
						response.write "<option value='http://www.wps-sa.com.br/asstec/site/addevtos.asp?mi_cod="&objRS1("mi_cod")&"&evt=4' >DEVOLVER AO CLIENTE</option></select></td>"
					END IF
					'Statius = 2 - Enviado Orcamento ao Cliente
					If objRS1("mi_status") =2 then
						Response.Write "<td><select size='1' name='mi_mudastatus' class='selectm' id='mi_mudastatus' tabindex='1' onchange='window.location.href = this.value;'>"
						response.write "<option value='' selected>-----</option>"
						response.write "<option value='http://www.wps-sa.com.br/asstec/site/addevtos.asp?mi_cod="&objRS1("mi_cod")&"&evt=6' >SOLICITAR PECAS</option>"
						response.write "<option value='http://www.wps-sa.com.br/asstec/site/addevtos.asp?mi_cod="&objRS1("mi_cod")&"&evt=5' >RECEBER ORCAMENTO APROV</option>"
						response.write "<option value='http://www.wps-sa.com.br/asstec/site/addevtos.asp?mi_cod="&objRS1("mi_cod")&"&evt=3' >FINALIZAR MANUTENCAO</option>"
						response.write "<option value='http://www.wps-sa.com.br/asstec/site/addevtos.asp?mi_cod="&objRS1("mi_cod")&"&evt=8' >SEM CONSERTO</option>"
						response.write "<option value='http://www.wps-sa.com.br/asstec/site/addevtos.asp?mi_cod="&objRS1("mi_cod")&"&evt=4' >DEVOLVER AO CLIENTE</option></select></td>"
					END IF
					'Status = 5 - Orcamento Aprovado
					If objRS1("mi_status") =5 then
						Response.Write "<td><select size='1' name='mi_mudastatus' class='selectm' tabindex='1' id='mi_mudastatus' onchange='window.location.href = this.value;'>"
						response.write "<option value='' selected>-----</option>"
						response.write "<option value='http://www.wps-sa.com.br/asstec/site/addevtos.asp?mi_cod="&objRS1("mi_cod")&"&evt=6' >SOLICITAR PECAS</option>"
						response.write "<option value='http://www.wps-sa.com.br/asstec/site/addevtos.asp?mi_cod="&objRS1("mi_cod")&"&evt=3' >FINALIZAR MANUTENCAO</option>"
						response.write "<option value='http://www.wps-sa.com.br/asstec/site/addevtos.asp?mi_cod="&objRS1("mi_cod")&"&evt=8' >SEM CONSERTO</option>"
						response.write "<option value='http://www.wps-sa.com.br/asstec/site/addevtos.asp?mi_cod="&objRS1("mi_cod")&"&evt=4' >DEVOLVER AO CLIENTE</option></select></td>"
					END IF
					'Status =3 - Manutencao Concluida
					if objRS1("mi_status") =3 then
						Response.Write "<td><select size='1' name='mi_mudastatus' class='selectm' tabindex='1' id='mi_mudastatus' onchange='window.location.href = this.value;'>"
						response.write "<option value='' selected>-----</option>"
						response.write "<option value='http://www.wps-sa.com.br/asstec/site/addevtos.asp?mi_cod="&objRS1("mi_cod")&"&evt=6' >SOLICITAR PECAS</option>"
						response.write "<option value='http://www.wps-sa.com.br/asstec/site/addevtos.asp?mi_cod="&objRS1("mi_cod")&"&evt=2' >AGUARDAR APROVACAO DE ORCAMENTO</option>"
						response.write "<option value='http://www.wps-sa.com.br/asstec/site/addevtos.asp?mi_cod="&objRS1("mi_cod")&"&evt=4' >DEVOLVER AO CLIENTE</option></select></td>"
					END IF
					'Status =8 - Manutencao Concluida Sem conserto
					if objRS1("mi_status") =8 then
						Response.Write "<td><select size='1' name='mi_mudastatus' class='selectm' tabindex='1' id='mi_mudastatus' onchange='window.location.href = this.value;'>"
						response.write "<option value='' selected>-----</option>"
						response.write "<option value='http://www.wps-sa.com.br/asstec/site/addevtos.asp?mi_cod="&objRS1("mi_cod")&"&evt=6' >SOLICITAR PECAS</option>"
						response.write "<option value='http://www.wps-sa.com.br/asstec/site/addevtos.asp?mi_cod="&objRS1("mi_cod")&"&evt=2' >AGUARDAR APROVACAO DE ORCAMENTO</option>"
						response.write "<option value='http://www.wps-sa.com.br/asstec/site/addevtos.asp?mi_cod="&objRS1("mi_cod")&"&evt=4' >DEVOLVER AO CLIENTE</option></select></td>"
					END IF
					'Status =6 - Aguardando Pecas
					if objRS1("mi_status") =6 then
						Response.Write "<td><select size='1' name='mi_mudastatus' class='selectm' tabindex='1' id='mi_mudastatus' onchange='window.location.href = this.value;'>"
						response.write "<option value='' selected>-----</option>"
						response.write "<option value='http://www.wps-sa.com.br/asstec/site/addevtos.asp?mi_cod="&objRS1("mi_cod")&"&evt=6' >SOLICITAR PECAS</option>"
						response.write "<option value='http://www.wps-sa.com.br/asstec/site/addevtos.asp?mi_cod="&objRS1("mi_cod")&"&evt=7' >RECEBER PECAS</option></select></td>" 
					END IF					
					Response.Write "</tr>"
					cont = cont+1
					objRS1.MoveNext
				Loop
			End if

[hockeybum27]

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.

Scenario:
Consider the following SQL that is being executed:

 select name, address_1, city, state, zip, country, my_flag from tablename

The order of the column names is important, as you will see later.

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);

			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

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:

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

 select my_flag, name, address_1, city, state, zip, country from tablename

there being no column/field before the first one, everything worked out.

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.

[ftissot]

I have the same problem as well, also with classic ASP and ADO. This bug is really weird and it is hard to narrow down when it happens and how to protect yourself from it. I'm posting the behavior I have tested in case it can help someone in the future. In my case, I'm not really looping as the recordset I'm using returns a single record. However, the order in which I request the fields (columns) in the SQL statement together with the order in which I retrieve the fields from the current record impacts whether the bug manifests itself. In my case, 2 of the fields are NULL (which may or may not have an impact). Also I'm wondering if the types of the fields have an impact as well. Here's my example with code and some scenarios when the bug manifests itself and when it does not. I indicated the type of each field in its name. Field5 and Field6 are NULL in the database.

Test Case 1: order of fields In SQL and fields retrieval match. I tried moving most of the fields around but as long as i retrieve them in the exact order they appear in the SQL, i cannot reproduce the bug.

sSQL = " select intField1, nvarcharField2, nvarcharField3, smallintField4, varcharField5, intField6" &_
" from table 1, table 2 "&_
" where somewhereclause "

set rs = connection.Execute(sSQL)
do while not rs.eof
field1Value = rs("intField1") -- value is retrieved fine
field2Value = rs("nvarcharField2") -- value is retrieved fine
field3Value = rs("nvarcharField3") -- value is retrieved fine
field4Value = rs("smallintField4") -- value is retrieved fine
field5Value = rs("varcharField5") -- value is retrieved fine (NULL)
field6Value = rs("intField6") -- value is retrieved fine (NULL)

rs.movenext
loop
rs.close

Test Case 2: order of fields In SQL And fields retrieval do not match. Field5 Is retrieved first, the rest of the fields are retrieved in order. (note that retrieving field6 first instead of field5 leads to the same result)

sSQL = " select intField1, nvarcharField2, nvarcharField3, smallintField4, varcharField5, intField6" &_
" from table 1, table 2 "&_
" where somewhereclause "

set rs = connection.Execute(sSQL)
do while not rs.eof
field5Value = rs("varcharField5") -- value is retrieved fine (NULL)
field1Value = rs("intField1") -- value is retrieved fine
field2Value = rs("nvarcharField2") -- value is retrieved fine
field3Value = rs("nvarcharField3") -- value is empty
field4Value = rs("smallintField4") -- value is empty
field6Value = rs("intField6") -- value is retrieved fine (NULL)

rs.movenext
loop
rs.close

Test Case 3: order of fields In SQL And fields retrieval do match. Field4 Is retrieved first, the rest of the fields are retrieved in order.

sSQL = " select intField1, nvarcharField2, nvarcharField3, smallintField4, varcharField5, intField6" &_
" from table 1, table 2 "&_
" where somewhereclause "

set rs = connection.Execute(sSQL)
do while not rs.eof
field4Value = rs("smallintField4") -- value is retrieved fine
field1Value = rs("intField1") -- value is retrieved fine
field2Value = rs("nvarcharField2") -- value is retrieved fine
field3Value = rs("nvarcharField3") -- value is empty
field5Value = rs("varcharField5") -- value is retrieved fine (NULL)
field6Value = rs("intField6") -- value is retrieved fine (NULL)

rs.movenext
loop
rs.close

Test Case 4: order of fields In SQL And fields retrieval do match. Field3 Is retrieved first, the rest of the fields are retrieved in order.

sSQL = " select intField1, nvarcharField2, nvarcharField3, smallintField4, varcharField5, intField6" &_
" from table 1, table 2 "&_
" where somewhereclause "

set rs = connection.Execute(sSQL)
do while not rs.eof
field3Value = rs("nvarcharField3") -- value is retrieved fine
field1Value = rs("intField1") -- value is retrieved fine
field2Value = rs("nvarcharField2") -- value is retrieved fine
field4Value = rs("smallintField4") -- value is retrieved fine
field5Value = rs("varcharField5") -- value is retrieved fine (NULL)
field6Value = rs("intField6") -- value is retrieved fine (NULL)

rs.movenext
loop
rs.close

A few other notes for folks out there trying to debug this:

1. you cannot be watching the recordset variable in VS while stepping through code as this will skew your results. Even having the "locals" window displaying while stepping through code will invalidate the test. This is because VS iterates through the fields in the right order if you are watching the RS or have the locals window active. Because it does that in the correct order, you will never see the bug.

2. The safest way to avoid the bug seems to be to retrieve the fields in the exact order they appear in the SQL (and hence in the fields array in the recordset). I hope this holds true.