SQLTeam.com | Weblogs | Forums

Weird changes to record field values

Here is an impossible issue. First, everything works perfectly on my machine, running Win 10. This happens ONLY on my client running Windows Server.

Complex query with many tables, two of the fields, one date one Int are having their values change during reading of the recordset. I proved this by writing the records to a text file.

First, before the code actually uses the records, I open the query and dump the records field by field to a text file such as:

For Each FldObj In RSTBL.Fields

TxtObj.WriteLine "1-" & FldObj.Name & "=[" & FldObj.Value & "] Null=" & IsNull(FldObj.Value) & " Len=" & Len(FldObj.Value)

TxtObj.WriteLine "2-" & FldObj.Name & "=[" & FldObj.Value & "] Null=" & IsNull(FldObj.Value) & " Len=" & Len(FldObj.Value)


Notice I write the field values TWICE and everything is fine. Specifically this field:

1-NumTasks=[89] Null=False Len=2

2-NumTasks=[89] Null=False Len=2

and these fields

1-OtherPreds= Null=True Len=

2-OtherPreds= Null=True Len=

1-OtherPredsID= Null=True Len=

2-OtherPredsID= Null=True Len=

Both should be NULL as it shows.

Next lines of code reopen the query and I do the same thing, dump records into a test file of only the fields causing problems. Get a load of this:

--- NumTasks= Null=False Len=0

NOPE!!! It should contain the value 89

Then there is this weird happening.

--- OtherPredsID= Null=True Len=

--- OtherPredsID again= Null=False Len=0

----- OtherPredsID and again= Null=False Len=0

With every record read in when I check this field it is correct at first, NULL. But I immediately write the same field again to the text file and now it's not null!!! But zero length string!!

This is a read only recordset, values in fields CANNOT be changed. This works fine on my Win 10 PC, this only happens on their Win Server, and ONLY these fields in the recordset, the rest are fine.

Anyone experienced this?

I've been programming for 35 years, I know how to debug. I found the cause, by careful systematic debugging. The two fields causing the problem were Varchar(max). Windows Server handles them differently than Win10. I fixed it by using Varchar(200) instead.

1 Like

Nice Very Nice

Oh you have programming experience of 35 Years !!!

You must be USED to these sort of things a lot !!!

Lots., but not this problem