Failed to convert parameter value from string to decimal

Question

Import CSV to 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 parameters 3 and 4 are of type decimal (9.6) in DDL, when I execute the code in ExecuteNonQuery , I get the following exception

Failed to convert parameter value from string to decimal.

please help me find out tnx problem.

EDIT

the value in crntRecord [3] looks like

Answer 1

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

Example:

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

Note that this will throw an exception if crntRecord[3] not decimal; if this can happen, look at decimal.TryParse() .

Answer 2

Edited to use safer parsing methods

Your lines have surrounding quotes that need to be removed. 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 decimal places, which would mean that this logic in the reusable function would be growth refactoring.

Answer 3

try

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

Answer 4

Use

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