ADO (sql server) recordset field has a value, but IsNull evaluates to TRUE and Len is zero???

[Isaac]

How is this physically possible? I'm debugging my code, and rsMaster.fields(lngRecordsetfield).value = "Acknowledged"

Yet, Isnull() evaluates to True, and Len("" & rsMaster.Fields(lngRecordsetField.value) = 0

I am using VBA and ADO to connect to a sql server recordset

Look at these conflicting values in immediate window.

Showing all code will be tough, it's from my job/work computer.

Any ideas just with this information?

 

[theDBguy]

What is the value of the variable lngrecordsetfield?

 

[Isaac]

52, and I tested its .Name property, it's the right one

 

[theDBguy]

52, and I tested its .Name property, it's the right one

So, are you saying you have a field called "52," or you have at least 53 fields in the table? If so, what is its data type?

 

[Isaac]

after waiting 5-10 minutes, I asked the immediate window the same question, and now it says the value is Null. Mind you, the code hasn't executed since I posted this question. So it seems to possibly be some timing issue....very frustrating. this sql-to-excel stuff via ADO is like gold to me, only a gold that is glitchy as hell. (like when trying to get multiple recordssets off a stored procedure, which I gave up on as the documented behavior doesn't seem to work half the time)

 

[Isaac]

So, are you saying you have a field called "52," or you have at least 53 fields in the table? If so, what is its data type?

no 52 is the numeric index of the field in the fields collection. it's coming from a sql statement
isnull(varcharfield1,varcharfield2)

and at least 50% of all the fields i grab from sql are varchar, into excel

 

[theDBguy]

no 52 is the numeric index of the field in the fields collection. it's coming from a sql statement
isnull(varcharfield1,varcharfield2)

and at least 50% of all the fields i grab from sql are varchar, into excel

Okay, do you know if the 53rd column has any empty cells in it?

 

[Isaac]

just for f un i'm going to try changing the SPROC to use coalesce instead, see if the ado recordset gets less confused by that

 

[Isaac]

Okay, do you know if the 53rd column has any empty cells in it?

Yes, approximately every other row truly IS null

 

[Isaac]

ADO Recordset returning blank fields *RESOLVED*-VBForums

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)

www.vbforums.com

that thread's OP seems to have failed to find a definite answer

 

[Isaac]

figured it out! and worse yet, it's something i'd figured out months ago then promptly forgot.

you CANNOT have varchar(max)'s coming back into the ado recordset into excel. it will get confused and say all kinds of crazy things. even if the value is only the 10 characters long, if it's typed as max it will bug out.

changed it to varchar(8000) and promptly issue is resolved. Now I must tie a string around my finger for the next 5 years

 

[theDBguy]

figured it out! and worse yet, it's something i'd figured out months ago then promptly forgot.

you CANNOT have varchar(max)'s coming back into the ado recordset into excel. it will get confused and say all kinds of crazy things. even if the value is only the 10 characters long, if it's typed as max it will bug out.

changed it to varchar(8000) and promptly issue is resolved. Now I must tie a string around my finger for the next 5 years

Glad to hear you remembered that trick!

 

[Isaac]

Glad to hear you remembered that trick!

Yes, but man these things give me gray hairs during the time when I wonder "will I be able to solve this or must I announce the death of my created Gadget?"