The strange case of ADO Multiple-step OLE DB operation generated errors

As I already have written, one of the pleasures of my job is maintaining a rather old VB6 app.

It uses ADO on MS SQL 2000 and 2005 servers. A few weeks ago, a couple of sites where the applications is installe started reporting the apparently random runtime exception "Multiple-stesp OLE DB operation generated errors". Most of the time this error means that ADO is trying to write a NULL in a field where NULLs are not allowed, or that it's trying to write a varchar value longer than the maximum field length allows. I rewrote the module signaling the exception including a loggin feature to write in a text file log the precise code line and the precise recordset contents involved. Nothing strange: the error seems to happen on a simple ADODB.Recordset update method, when adding or updating a record. The exception is raised when writing to a decimal field: but the values used are always perfectly legal.

E.g.

Dim rs as New ADODB.Recordst

rs.Open "SELECT * from table where id = 17",connection, adOpenDynamic,adOptimistic

rs!TextValue = "yup"

rs!DecimalValue = 3.14 ' exception is raised

rs!OtherValue = "lebarbaz"

rs.Update

Apparently under the same initial conditions sometimes the error appears, sometimes not. Sometimes days would pass with no problems, and then a hail of errors for hours and hours.

The only thing the different sites showing the error have in common is the presence of many table with text fields fulled to the max: for instance the company name field is say 70 char max, and many company names used are 70 charachters long.

I found zero-nada-nilch on the Internet about this error: other than buying something like Soft-ICE and trying to debug ADO o the SQL provider I've tried to simply circumvent the error.

This, at least for the last weeks seems to work:

Dim rs as New ADODB.Recordst

rs.Open "SELECT * from table where id = 17",connection, adOpenDynamic,adOptimistic

rs.CursorsLocation = adUseClient

rs!TextValue = "yup"

rs!DecimalValue = Cdec(3.14) 'Cdec is now needed, simply because I changed the cursorlocation

rs!OtherValue = "lebarbaz"

rs.Update

That's it, a vulcanian logic solution. Probably changing the cursor location avoid some strange memory allocation problem with ADO and/or the MS Sql provider I'm using. Still not as amazing as the bugs and missing features I found in the ADOX library trying to manage some mdb access files years ago, but much more disconcerting.