Failed to convert parameter value from a String to a Decimal

Question

Im importing a csv to my sql server table using the following code

SqlCommand nonqueryCommand = myConnection.CreateCommand();
nonqueryCommand.CommandText = 
                 "INSERT INTO MYTABLE VALUES(@num1, @num2,@num3,@num4)";

nonqueryCommand.Parameters.Add("@num1",SqlDbType.Decimal);
nonqueryCommand.Parameters.Add("@num2", SqlDbType.Decimal);
nonqueryCommand.Parameters.Add("@num3", SqlDbType.Decimal);
nonqueryCommand.Parameters.Add("@num4", SqlDbType.Decimal);

nonqueryCommand.Parameters["@num1"].Value = crntRecord[0];
nonqueryCommand.Parameters["@num2"].Value = crntRecord[1];
nonqueryCommand.Parameters["@num3"].Value =crntRecord[3];
nonqueryCommand.Parameters["@num4"].Value = crntRecord[4];

nonqueryCommand.ExecuteNonQuery();

where the parameter 3 and 4 are of type decimal(9,6) in the DDL when i execute the code at ExecuteNonQuery i get the following exception

Failed to convert parameter value from a String to a Decimal.

please help me find out the problem tnx.

EDIT

the value in the crntRecord[3] looks like

enter image description here

Don't know which type `crntRecord` is. If it is a `DataRow`, `crntRecord[0]` would be of type `object` which very likely might be a `string`. — Uwe Keim, Aug 20 '12 at 18:02
Could you provide a sample of what the values in the `crntRecord` array are? — Ian Dallas, Aug 20 '12 at 18:03
Have you debugged your program and checked the values of `crntRecord` at indices 0, 1, 3, and 4? I'd guess that at least one of those non-numeric. — Jon Senchyna, Aug 20 '12 at 18:03
I would venture to guess that the type returned by `crntRecord[3]` and `crntRecord[4]` are `String`. You can confirm this by adding: `string test = crntRecord[3]` and checking to see if it compiles. — CodingGorilla, Aug 20 '12 at 18:03
@CodingGorilla @Kundan Singh Chouhan yes they are the string type values but when i convert them to decimal i get the error `input string was not in the correct format — John x, Aug 20 '12 at 18:18
can you paste here what the string value is..? so that we can see — MethodMan, Aug 20 '12 at 18:21
@John I'm going to go out on a limb and say that they're not actually decimal values... =) — CodingGorilla, Aug 20 '12 at 18:23
are those "\" being passed..? you need to strip out the return characters if this is the case.. you can't convert a return char + "\43.445\" into a decimal ..of course it will fail ..try using string.replace() on each string or do a Check on the string if it contains "\" use String.Replace() else Convert the String into a Decimal — MethodMan, Aug 20 '12 at 18:29
@DJKRAZE It's not a "\", it's a quotation mark. The \ is an escape character. — Daniel Mann, Aug 20 '12 at 18:29
The values are surrounded by double quotes ("), that's why you get the error with `decimal.Parse()` — CodingGorilla, Aug 20 '12 at 18:31

score 3 · Accepted Answer · answered Aug 20 '12 at 18:03

3

Assuming that crntRecord is an array of strings, you need to parse the strings to a decimal first.

Ex:

nonqueryCommand.Parameters["@num3"].Value = decimal.Parse(crntRecord[3].ToString());

Note that this will throw an exception if crntRecord[3] is not parseable to a decimal; if that's a situation that could occur, look into decimal.TryParse() instead.

answered Aug 20 '12 at 18:03

Daniel Mann

57,011
13
100
120

i get the exception `Input string was not in a correct format.` – John x Aug 20 '12 at 18:23
@John Then it's not a value parseable to a decimal. What *is* the value? – Daniel Mann Aug 20 '12 at 18:28
you are right it does not look like a string can you please have a look at the edit i made to the original post – John x Aug 20 '12 at 18:29
Your value is `"-71.013202"`, with quotation marks. Try `decimal.Parse(crntRecord[3].ToString().Replace("\"", string.Empty));` – Daniel Mann Aug 20 '12 at 18:31

D Stanley · Answer 2 · 2012-08-20T18:43:37.337

3

Edited to use safer parsing methods

Your strings have surrounding quotes that you need to strip off. Try

decimal num3;
bool isDecimal = decimal.TryParse(crntRecord[3].Trim(new []{'\"'}), out num3);
if(isDecimal)
    nonqueryCommand.Parameters["@num3"].Value = num3;

I would recommend using this method for all of your decimals, which would mean putting this logic in a reusable function would be a rise refactoring.

edited Aug 20 '12 at 18:43

answered Aug 20 '12 at 18:31

D Stanley

149,601
11
178
240

The caveat to this answer is that obviously whatever it is that's providing the values to crntRecord is not restrained to providing valid decimal values. So I would strongly suggest that instead of doing the Convert call in-line that you instead use `Decimal.TryParse()` and handle the case where the value is not really a decimal value. – CodingGorilla Aug 20 '12 at 18:35

score 1 · Answer 3 · answered Aug 20 '12 at 18:03

try with

nonqueryCommand.Parameters["@num1"].Value = Convert.ToDecimal(crntRecord[0]));
nonqueryCommand.Parameters["@num2"].Value = Convert.ToDecimal(crntRecord[1]);
nonqueryCommand.Parameters["@num3"].Value =Convert.ToDecimal(crntRecord[3]);
nonqueryCommand.Parameters["@num4"].Value = Convert.ToDecimal(crntRecord[4]);

score -1 · Answer 4 · answered Aug 20 '12 at 18:04

-1

Use

nonqueryCommand.Parameters.AddWithValue("@num1", crntRecord[0]);

answered Aug 20 '12 at 18:04

Jaime Yule

981
1
11
20

notice that the expected Param is a Decimal there needs to be a conversion done or use the internal .Parse functionality.. – MethodMan Aug 20 '12 at 18:06
AddWithValue can convert many objects for us acepting that c# variables are way diferent from any DB variables. there was no need to give my answer a down vote. – Jaime Yule Aug 20 '12 at 18:09
1

`AddWithValue` will definitely create a parameter of an appropriate `SqlDbType` for the .NET type that `crntRecord[0]` is. The problem you have is that the OP specifically needs a **`SqlDbType.Decimal`** parameter. Suppose `crntRecord[0]` was a string with a value of `"ThisIsAString"`. Calling `AddWithValue` will result in `SqlDbType.SqlString` parameter, which will then fail when the query hits the DB. – Jon Senchyna Aug 20 '12 at 18:15
`AddWithValue` is great when your objects match up to the appropriate data types that your query is expecting. It does not work well if you need to convert your object first (as is the case when you have a string representation of a numeric type). – Jon Senchyna Aug 20 '12 at 18:19
MalGani in order to prove if it's right or wrong try testing it yourself and seeing if your theory holds if so I am sure someone will change your downvote to an upvote – MethodMan Aug 20 '12 at 18:22

Failed to convert parameter value from a String to a Decimal

4 Answers4