2009-02-12

I don't like all those decimals anymore

So I'm working on a project, and I'm using the SQL Server CE engine as my database. The database engine recently had an upgrade to 3.5.1, so when my client got a new computer and needed to install my program on it, the upgraded database engine was what was downloaded from Microsoft's web site. I didn't think much of it, as I had downloaded it as well, and I hadn't had any issues. However, when he started using the program with real-world data, he got an error:

SqlCeException: 'A parameter is missing. [ Parameter ordinal = 1 ]'

Ok, maybe I goofed. Perhaps in one of my updates, which added a column to one of the tables, I left off a parameter. So I tested the code on my machine, and it of course performed flawlessly.

I went to the client's site and tested it there. Sure enough, there was the error. I copied his database and ran my code against it, in debug mode, and… yes, I finally duplicated the error. Well, at least that told me the error was real.

Then commenced the tedious task of analyzing the SQL command being constructed; ensuring that all parameters were accounted for, all columns were spelled correctly, and all parameter names in the statement matched the names of the parameter objects added to the command; comparing his database to mine to make sure all table columns were accounted for and of the correct types. Everything matched. There were no parameters missing, misspelled, or misplaced.

After I fully convinced myself that everything was in order, I finally went to Google with the full text of the error, and I was fortunate to stumble across a bug reported on Microsoft's Connect site that indicated a problem with decimal values in SQL CE 3.5.1 under "certain conditions". Fortunately, the workaround listed helped me identify those conditions and what to do about them: it has to do with the number of decimal places one attempts to pass to SQL CE.

Fortunately, when building parameters, I pass every value through a function that checks for null and converts to DBNull (I have yet to hear a convincing reason why these must be different), and checks for bool types and converts true/false to 1/0. All I needed to do to fix this was to add code that checks for the decimal type and return Math.Round(value, 4) (although it took a little experimentation to determine that "4" was the correct number of decimals).

All because Microsoft decided they didn't like seeing all those decimal places anymore; and instead of rounding or ignoring them like they did in a previous revision, suddenly they were going to throw a completely incorrect error message (no parameter was missing, and the one it suddenly found "questionable" was not in position 1).

1 comment:

Gleb said...

Thank you. Right now I'm having fun with this problem. Also found your bug report on MS. Looks like they don't check dashboard at all..