Click here to Skip to main content
15,878,316 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Dear All,

I got error
Failed to convert parameter value from a String to a Int32.

this code is export Excel data to SqlServer. How to resolve the error.
Thank in advance.

public void insertdataintosql(string ye_cm_id, string ye_fy_id, string ye_month, string ye_year, string ye_basic, string ye_hra, string ye_cea, string ye_pa, string ye_spl_allow, string ye_other_allow, string ye_pda, string ye_cadre, string ye_medical, string ye_os_allow, string ye_attire_allow)
    {//inserting data into the Sql Server
        SqlConnection conn = new SqlConnection("Data Source=SUBRAMANYAM\\SQLEXPRESS;Initial Catalog=Incometax;Integrated Security=true;");
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = "insert into yearly_earnings(ye_cm_id,ye_fy_id,ye_month,ye_year,ye_basic,ye_hra,ye_cea,ye_pa, ye_spl_allow,ye_other_allow,ye_pda,ye_cadre,ye_medical,ye_os_allow,ye_attire_allow) values (@ye_cm_id,@ye_fy_id,@ye_month,@ye_year,@ye_basic,@ye_hra,@ye_cea,@ye_pa, @ye_spl_allow,@ye_other_allow,@ye_pda,@ye_cadre,@ye_medical,@ye_os_allow,@ye_attire_allow)";
        cmd.Parameters.Add("@ye_cm_id", SqlDbType.Int).Value = ye_cm_id;
        cmd.Parameters.Add("@ye_fy_id", SqlDbType.Int).Value = ye_fy_id;
        ////cmd.Parameters.Add("@ye_fy_id", SqlDbType.Int).Value = ye_fy_id;
        cmd.Parameters.Add("@ye_month", SqlDbType.Int).Value = ye_month;
        cmd.Parameters.Add("@ye_basic", SqlDbType.Int).Value = ye_basic;
        cmd.Parameters.Add("@ye_year", SqlDbType.Int).Value = ye_year;
        cmd.Parameters.Add("@ye_hra", SqlDbType.Int).Value = ye_hra;
        cmd.Parameters.Add("@ye_cea", SqlDbType.Int).Value = ye_cea;
        cmd.Parameters.Add("@ye_pa", SqlDbType.Int).Value = ye_pa;
        cmd.Parameters.Add("@ye_spl_allow", SqlDbType.Int).Value = ye_spl_allow;
        cmd.Parameters.Add("@ye_other_allow", SqlDbType.Int).Value = ye_other_allow;
        cmd.Parameters.Add("@ye_pda", SqlDbType.Int).Value = ye_pda;
        cmd.Parameters.Add("@ye_cadre", SqlDbType.Int).Value = ye_cadre;
        cmd.Parameters.Add("@ye_medical", SqlDbType.Int).Value = ye_medical;
        cmd.Parameters.Add("@ye_os_allow", SqlDbType.Int).Value = ye_os_allow;
        cmd.Parameters.Add("@ye_attire_allow", SqlDbType.Int).Value = ye_attire_allow;

        cmd.CommandType = CommandType.Text;
Updated 29-Apr-12 18:46pm

Failed to convert parameter value from a String to a Int32.
In above code, there are lots of places(command parameters) where you have defined variable to be of type int and assigning some value to it. Quite few of them are method parameters itself that are passed as strings. Later you assign those strings to parameters defined as int.

Looks like, alteast one of them is failing. See if all the values that has to be int is an integer. Also, pass integers that are integers to the method itself instead of passing them as strings.
Share this answer
If all the parameters are in integer format just change the data type to int or convert the string values to integer.

cmd.Parameters.Add("@ye_cm_id", SqlDbType.Int).Value = Convert.ToInt32(ye_cm_id);
Share this answer

It seems that, you have parameter mismatch. your parameters are not matching. Your input parameter is of type string and sqlcommand parameter is of type int. please correct it and try again.

Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900