Image of Navigational Map linked to Home / Contents / Search Updating SQL Server... or not!

by Stephan Grieger - Independent Developer
Image of Line Break

There appears to be a bug in ADO 1.5 and 2.0. OK, I guess that probably hasn't exactly floored everyone. But this one just shouldn't have made it out the door.

Consider the following simple update :

ADOCDR!AgentId = CDRRecord.AgentId
ADOCDR!BillType = CDRRecord.BillType
ADOCDR!TermCity = CDRRecord.TermCity

CDRRecord is a structure. Each field has been defined as such...

AgentId As Space * x

Assume that BillType is of string 10 and has not been filled in by the program. In this case, of course, it will be filled with nulls.

When you check SQL Server after the update, you will notice that AgentId is filled in but TermCity is not. However, if you place a value into BillType, then all three fields appear with the correct values.

After some digging and the loss of still more of that fast-diminishing resource, my hair, it appears that any field you try to fill in, after you have tried to add a null, will be omitted during the update.

The Debugger will show them as filled, but 'update' will not update any fields folowing a Null. Now isn't that just a wonderful feature? Doesn't it warm your heart to think that this one got through the extensive testing / quality assurance program up there at Quality Central in Seattle?

Just to make the process even more confusing, um challenging, no error is generated.

I suppose that this is somehow related to old gotcha of having to update BLOBs last, but it truly does seem crazy!

Are we having fun yet?

Written by: Stephan Grieger
August '98

Image of Arrow linked to Previous Article
Image of Line Break