Discovered this strange problem on one of my Applications. Some Subscribers were getting a cryptic error while attempting to save their Data “Error converting data type nvarchar to numeric”! I would try to replicate the error to find the source and came up a blank! Eventually found the source of the error after 3 frustrating days! The error was only when a specific kind of data was being entered, which is how it sneaked into Production without being caught.
The Application, like many others was built on my DataGroups architecture. Which meant that Data Structure for each Segment (Screen) varied. The API code was thus written to deal with various data types dynamically at run time. Depending on the Table structure, the Parameters collection was being generated at Runtime. When this was developed, My programmers had used a combination of (Parameters).AddWithValue() or the Parameters.Add() function without specifying the data type explicitly. I thought it was a clever solution at the time because we would no longer have to write tedious code for every single Data Entry screen – we had over 300!
foreach (FormField field in formData)
{
:
:
cmd.Parameters.AddWithValue("@" + field.Name, field.Value);
:
:
}
Now heres the thing. We made an assumption that the brilliant minds at Microsoft had written their Architecture correctly and when we used AddWithValue(), it would correctly infer the Target datatype for our query parameter depending on the value. That is how we learnt that it was supposed to work! So That is what we taught our programmers too! Well, I can confirm that it DOES NOT work that way at all! That Microsoft Library is not perfect. It does not always do this translation correctly.
As I discovered, There are specific scenarios where it can fail to do so correctly. I think it happens when some of the secret information that ADO library uses to determine the Data Type is missing or vague. For example, it throws an error when trying to pass a Date value to the underlying DateTime Value. Surprised, this was not caught earlier! A trickier one is when trying to pass a varchar value with special characters – the French or Thai language versions of our Application seemed to have this problem reported more than the others and I am sure this was the reason. Converting varchar to nvarchar is tricky, converting nvarchar to varchar is even trickier and could -in some cases – lead to loss of data. THAT was causing the error. The lengths dont match up sometimes – esp when we have large Memo type fields (2500 characters).
The thing is, When using this kind of coding, we cannot predict in advance where the ADO library we are using will fail! AddWithValue() works in MOST cases, but it clearly does not work in ALL cases! I added a function to explicitly read the DataGroup and build the Parameters collection for the Query by specifying the DataTypes explicitly while creating each Parameter and used this collection in the original Function. It worked! The problem was gone!
foreach (FormField field in formData)
{
switch(field.ControlType)
{
//Determine SQL Data Type
}
:
:
cmd.Parameters.Add("@" + field.Name, sqlType).Value = field.Value;
:
:
}
There is another reason to not use AddWithValue() in the future – PERFORMANCE. On a hunch, I decided to test the performance implications of the changed code using a bit of code in the Integration module for pulling Data from GreatPlains into our system. On a Benchmark test with 3000 large data items, The code using AddWithValue() took 3x the amount of time as compared to the code where the Data Type was explicitly specified. I believe that is significant.
Another discovery, the variant of our API designed for using MySQL does NOT seem to have this problem. I could not replicate the error on the API using the MySql.Data libraries and it worked fine. The performance issues were similar though. I will need to spend some time looking at this in detail.
In summary, I would conclude that using AddWithValue() is fraught with risk because, contrary to what we have been told, it does not work correctly in all situations. There is also a significant performance penalty – i am guessing because of conversion could be affecting the indexing. We should immediately stop teaching our students to use this and also instruct our programmers to stop using this method. Correcting this error in all past applications is going to be a headache now! Even the thought of having to do this gives me migraine! Must schedule it in for the next few months! 🙁
Please wait...