CODEWITHSUNDEEP
c#.netsql-server-2008sqlconnectionsqlcommand
John x
John x

Reputation: 4031

Failed to convert parameter value from a String to a Decimal

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

Upvotes: 4

Views: 16315

Answers (4)

D Stanley
D Stanley

Reputation: 152596

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.

Upvotes: 3

Jaime Yule
Jaime Yule

Reputation: 1081

Use 

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

Upvotes: -1

Daniel Mann
Daniel Mann

Reputation: 59037

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.

Upvotes: 3

Aghilas Yakoub
Aghilas Yakoub

Reputation: 28990

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]);

Upvotes: 1

Related Questions