Why are null values fetched from ADO Recordset instead of correct ones depending on the order of retrieval? (The DB table has NTEXT values)

Question

I have a DB table with two datetime nullable columns that I need to read from an ASP page using VBScript.

This is the code I wrote:

Set cmd = Server.CreateObject("ADODB.Command")
With cmd
    .ActiveConnection = conn
    .CommandType = adCmdText
    .Prepared = True
    .CommandText = "SELECT * FROM storico_corsi WHERE stc_id = 5 "
    Set rs = .Execute
    Response.Write("stc_scadenza = {" & rs("stc_scadenza") & "}, ")
    Response.Write("stc_inizio = {" & rs("stc_inizio") & "} ")
    If IsEmpty(rs("stc_inizio")) Then
        Response.Write("- ERROR!")
    End If
End With

This code gives this output:

stc_scadenza = {19/04/2014}, stc_inizio = {} - ERROR!

If I swap the order of retrieval to

...
    Response.Write("stc_inizio = {" & rs("stc_inizio") & "}, ")
    Response.Write("stc_scadenza = {" & rs("stc_scadenza") & "} ")
...

this is the (correct) result I get:

stc_inizio = {19/02/2014}, stc_scadenza = {19/04/2014} 

Why is a small change in the order of retrieval of elements from an ADO Recordset delivering a completely different outcome?

Please notice that I'm using the Italian locale (dd/mm/yyyy) and that stc_inizio and stc_scadenza, while being often set to midnight timestamps, are of SQL type datetime.

---

UPDATE #1: I made the code simpler and clearer by reducing the code to the manipulation of just two fields, adding the emptiness check and removing JSON stuff altogether. Some comments below refer to previous, more complicated, versions.

---

UPDATE #2: If I replace the SQL query with

SELECT stc_inizio, stc_scadenza FROM storico_corsi WHERE stc_id = 5

or

SELECT stc_scadenza, stc_inizio FROM storico_corsi WHERE stc_id = 5

it works properly! But why? This is the DB table I'm using:

CREATE TABLE [dbo].[storico_corsi] (
    [stc_id] [bigint] IDENTITY(1,1) NOT NULL,
    [stc_id_ute] [bigint] NOT NULL,
    [stc_utente] [varchar](100) NULL,
    [stc_anagrafica] [ntext] NULL,
    [stc_id_can] [bigint] NULL,
    [stc_canale] [varchar](500) NULL,
    [stc_FE_id] [bigint] NULL,
    [stc_quest_finale] [ntext] NULL,
    [stc_quest_corretto] [ntext] NULL,
    [stc_reg_fad] [ntext] NULL,
    [stc_inizio] [datetime] NULL,
    [stc_scadenza] [datetime] NULL,
    [stc_terminato] [char](1) NULL
        CONSTRAINT [DF_storico_corsi_stc_terminato_1]  DEFAULT ('N'),
    [stc_fine] [datetime] NULL,
    [stc_tempo] [bigint] NULL 
        CONSTRAINT [DF_storico_corsi_stc_tempo]  DEFAULT ((0)),
    [stc_data_in] [datetime] NULL 
        CONSTRAINT [DF_storico_corsi_stc_data_in_1]  DEFAULT (getdate()),
    [stc_progressivo] [int] NULL,
    [stc_anno] [int] NULL,
CONSTRAINT [PK_storico_corsi] PRIMARY KEY CLUSTERED ([stc_id] ASC) 
WITH (
    PAD_INDEX = OFF, 
    STATISTICS_NORECOMPUTE = OFF, 
    IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) 
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

---

UPDATE #3: The issue can be reproduced anytime I include any of the 3 ntext columns in the query. If I don't SELECT them, nothing bad happens. Apparently, ntext values break the record, but they also do that in an unpredictable way that depends on the order of retrieval of other fields.

---

UPDATE #4: BLOBs/ntexts have to be fetched last with ADO. This statement sounds a bit crazy but this is what I found here: http://p2p.wrox.com/sql-server-2000/3211-cant-pull-data-ntext-field-into-recordset.html#post78234 It's consistent with my experience.

Answer 1

Given the results you're seeing as we've investigated further, it seems as though the problem stems from the NTEXT columns that you're selecting implicitly with your SELECT * ... (but not using.)

As you've noted, there are occasional hints on the web that there at least used to be problems retrieving large binary fields earlier than the end of the SELECT list when using ADO. (As soon as you linked to that article I did have a vague memory from the dim and distant past that I'd seen that advice before too.)

I'd suspect you may be using an older version of ADO, and that this problem may have been fixed in later releases.

Given that in this particular case you don't actually want to retrieve the values from the NTEXT columns, you should simply limit your SELECT list to the values you actually need, and everything should work fine.

Note that it's generally considered best practice to avoid the use of SELECT * except for quick, ad-hoc queries. Explicitly selecting the columns you need has several benefits. For example, in this case, even if your SELECT had worked perfectly, you'd still have been retrieving the potentially large amounts of data in your NTEXT fields (possibly across the network) from your database only to discard it without using it...

Note also that in KB article 317016, "How To Read and Write BLOBs Using GetChunk and AppendChunk", there's some "suggestions for using BLOBs with ADO", including:

• Select the BLOB columns last. Select individual fields, not "*".