how to solve this error ? "Error converting data type nvarchar to numeric."

Question

2.00/5 (1 vote)

See more:

i m making a registration form... where i want the should go in data table according to choice selected by user...
but when i click the "submit" button. it shows me an error like this...

Server Error in '/project@water_billing_system' Application.

Error converting data type nvarchar to numeric.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: Error converting data type nvarchar to numeric.

Source Error: 


Line 521:            cmd.Parameters.Add(prm_ac_code_password);
Line 522:
Line 523:            cmd.ExecuteNonQuery();
Line 524:            conn.Close();
Line 525:        }

Source File: f:\water billing system\project@water_billing_system\online_redistration.aspx.cs    Line: 523

i m using stored procedure for this...
and the store procedure is like below.

Quote:
@registration_datetime datetime,
@ac_code_id varchar(8),
@ac_password varchar(max),
@owner_surname varchar(30),
@owner_first_name varchar(30),
@owner_middle_name varchar(30),
@owner_DOB date,
@add_of_c_industry varchar(MAX),
@handler1_name varchar(50),
@handler1_mobile numeric(14,0),
@handler1_landline numeric(18,0),
@handler1_email varchar(50),
@handler2_name varchar(50),
@handler2_mobile numeric(14,0),
@handler2_landline numeric(18,0),
@handler2_email varchar(50),
@handler3_name varchar(50),
@handler3_mobile numeric(14,0),
@handler3_landline numeric(18,0),
@handler3_email varchar(50),
@connection_type_id int,
@connection_size_id int,
@connection_plan_id int,
@connection_start_wish date

all the data type is same in sqltable as above...

and this is my "aspx.cs" code...

protected void btn_submit_Click(object sender, EventArgs e)
    {
        string owner_birthdate,wish_start_from,ac_code_id;
        owner_birthdate = ddl_owner_dob_mm.Text + '/' + ddl_owner_dob_dd.Text + '/' + ddl_owner_dob_yyyy.Text;
        wish_start_from=ddl_conn_wish_mm.Text+'/'+ddl_conn_wish_dd.Text+'/'+ddl_conn_wish_yyyy.Text;

        if (rbl_conn_for.SelectedValue == "1")
        {
            string hp="HP";

            conn.Open();
            SqlCommand cmd = new SqlCommand("insert_into_reg_housing_plot",conn);
            cmd.CommandType = CommandType.StoredProcedure;


            SqlParameter prm_datetime = new SqlParameter(lbl_date_time.Text, "@registration_datetime");
            cmd.Parameters.Add(prm_datetime);

            SqlParameter prm_owner_surname = new SqlParameter("@owner_surname", txt_owner_surname.Text);
            cmd.Parameters.Add(prm_owner_surname);

            SqlParameter prm_owner_first_name = new SqlParameter("@owner_first_name", txt_owner_first_name.Text);
            cmd.Parameters.Add(prm_owner_first_name);

            SqlParameter prm_owner_middle_name = new SqlParameter("@owner_middle_name", txt_owner_middle_name.Text);
            cmd.Parameters.Add(prm_owner_middle_name);

            SqlParameter prm_owner_DOB = new SqlParameter("@owner_DOB", owner_birthdate);
            cmd.Parameters.Add(prm_owner_DOB);

            SqlParameter prm_add_housing_plot = new SqlParameter("@add_of_housing_plot", txt_address_housing_plot.Text);
            cmd.Parameters.Add(prm_add_housing_plot);

            SqlParameter prm_handler1_name = new SqlParameter("@handler1_name", txt_handler1_name.Text);
            cmd.Parameters.Add(prm_handler1_name);

            SqlParameter prm_handler1_mobile = new SqlParameter("@handler1_mobile", txt_handler1_mobile.Text);
            cmd.Parameters.Add(prm_handler1_mobile);

            SqlParameter prm_handler1_landline = new SqlParameter("@handler1_landline", txt_handler1_landline.Text);
            cmd.Parameters.Add(prm_handler1_landline);

            SqlParameter prm_handler1_email = new SqlParameter("@handler1_email", txt_handler1_email.Text);
            cmd.Parameters.Add(prm_handler1_email);

            SqlParameter prm_handler2_name = new SqlParameter("@handler2_name", txt_handler2_name.Text);
            cmd.Parameters.Add(prm_handler2_name);

            SqlParameter prm_handler2_mobile = new SqlParameter("@handler2_mobile", txt_handler2_mobile.Text);
            cmd.Parameters.Add(prm_handler2_mobile);

            SqlParameter prm_handler2_landline = new SqlParameter("@handler2_landline", txt_handler2_landline.Text);
            cmd.Parameters.Add(prm_handler2_landline);

            SqlParameter prm_handler2_email = new SqlParameter("@handler2_email", txt_handler2_email.Text);
            cmd.Parameters.Add(prm_handler2_email);

            SqlParameter prm_handler3_name = new SqlParameter("@handler3_name", txt_handler3_name.Text);
            cmd.Parameters.Add(prm_handler3_name);

            SqlParameter prm_handler3_mobile = new SqlParameter("@handler3_mobile", txt_handler3_mobile.Text);
            cmd.Parameters.Add(prm_handler3_mobile);

            SqlParameter prm_handler3_landline = new SqlParameter("@handler3_landline", txt_handler3_landline.Text);
            cmd.Parameters.Add(prm_handler3_landline);

            SqlParameter prm_handler3_email = new SqlParameter("@handler3_email", txt_handler3_email.Text);
            cmd.Parameters.Add(prm_handler3_email);

            if (rbl_connection_type.SelectedValue == "3")
            {
                SqlParameter prm_yearly_conn_type = new SqlParameter("@connection_type_id",rbl_connection_type.SelectedValue);
                cmd.Parameters.Add(prm_yearly_conn_type);

                SqlParameter prm_fix_yearly_size = new SqlParameter("@connection_size_id",ddl_conn_size_selection_for_fixed_yearly_plan.SelectedValue);
                cmd.Parameters.Add(prm_fix_yearly_size);

                SqlParameter prm_fix_yearly_plan = new SqlParameter("@connection_plan_id", ddl_plan_selection_for_fixed_yearly_conn.SelectedValue);
                cmd.Parameters.Add(prm_fix_yearly_plan);
            }
            else if (rbl_connection_type.SelectedValue == "1")
            {
                SqlParameter prm_monthly_conn_type = new SqlParameter("@connection_type_id", rbl_connection_type.SelectedValue);
                cmd.Parameters.Add(prm_monthly_conn_type);

                SqlParameter prm_fix_monthly_size = new SqlParameter("@connection_size_id", ddl_conn_size_selection_for_fixed_plant.SelectedValue);
                cmd.Parameters.Add(prm_fix_monthly_size);

                SqlParameter prm_fix_monthly_plan = new SqlParameter("@connection_plan_id", ddl_plan_selection_for_fixed_conn.SelectedValue);
                cmd.Parameters.Add(prm_fix_monthly_plan);
            }
            else if (rbl_connection_type.SelectedValue == "2")
            {
                SqlParameter prm_monthly_conn_type = new SqlParameter("@connection_type_id", rbl_connection_type.SelectedValue);
                cmd.Parameters.Add(prm_monthly_conn_type);

                SqlParameter prm_fix_monthly_plan = new SqlParameter("@connection_plan_id", ddl_conn_size_for_meter_plan.SelectedValue);
                cmd.Parameters.Add(prm_fix_monthly_plan);
            }

            SqlParameter prm_start_wish = new SqlParameter("@connection_start_wish",wish_start_from);
            cmd.Parameters.Add(prm_start_wish);


            SqlCommand ac = new SqlCommand("select max(ac_code_int) from registration_for_housing_plot",conn);
            object a;
            a=ac.ExecuteScalar();
            if (a.ToString() == "")
        {
            a="1001";
             ac_code_id= hp + a;
        }
        else
        {
            Int64 v;
            v = (Int64.Parse(a.ToString())) + 1;
            ac_code_id = hp + v.ToString();
            
           
        }
        
        SqlParameter prm_ac_code_id = new SqlParameter("@ac_code_id", ac_code_id);
        cmd.Parameters.Add(prm_ac_code_id);

        ac.ExecuteNonQuery();

        SqlParameter prm_ac_code_password = new SqlParameter("@ac_password",txt_confirm_password.Text);
        cmd.Parameters.Add(prm_ac_code_password);

        cmd.ExecuteNonQuery();
            conn.Close();
        }
        else if (rbl_conn_for.SelectedValue == "2")
        {
            string ci = "CI";

            conn.Open();
            SqlCommand cmd = new SqlCommand("insert_into_reg_c_industry", conn);
            cmd.CommandType = CommandType.StoredProcedure;


            SqlParameter prm_datetime = new SqlParameter("@registration_datetime", lbl_date_time.Text);
            cmd.Parameters.Add(prm_datetime);

            SqlParameter prm_owner_surname = new SqlParameter("@owner_surname", txt_owner_surname.Text);
            cmd.Parameters.Add(prm_owner_surname);

            SqlParameter prm_owner_first_name = new SqlParameter("@owner_first_name", txt_owner_first_name.Text);
            cmd.Parameters.Add(prm_owner_first_name);

            SqlParameter prm_owner_middle_name = new SqlParameter("@owner_middle_name", txt_owner_middle_name.Text);
            cmd.Parameters.Add(prm_owner_middle_name);

            SqlParameter prm_owner_DOB = new SqlParameter("@owner_DOB", owner_birthdate);
            cmd.Parameters.Add(prm_owner_DOB);

            SqlParameter prm_add_c_industry = new SqlParameter("@add_of_c_industry", txt_address_commercial_industry.Text);
            cmd.Parameters.Add(prm_add_c_industry);

            SqlParameter prm_handler1_name = new SqlParameter("@handler1_name", txt_handler1_name.Text);
            cmd.Parameters.Add(prm_handler1_name);

            SqlParameter prm_handler1_mobile = new SqlParameter("@handler1_mobile", txt_handler1_mobile.Text);
            cmd.Parameters.Add(prm_handler1_mobile);

            SqlParameter prm_handler1_landline = new SqlParameter("@handler1_landline", txt_handler1_landline.Text);
            cmd.Parameters.Add(prm_handler1_landline);

            SqlParameter prm_handler1_email = new SqlParameter("@handler1_email", txt_handler1_email.Text);
            cmd.Parameters.Add(prm_handler1_email);

            SqlParameter prm_handler2_name = new SqlParameter("@handler2_name", txt_handler2_name.Text);
            cmd.Parameters.Add(prm_handler2_name);

            SqlParameter prm_handler2_mobile = new SqlParameter("@handler2_mobile", txt_handler2_mobile.Text);
            cmd.Parameters.Add(prm_handler2_mobile);

            SqlParameter prm_handler2_landline = new SqlParameter("@handler2_landline", txt_handler2_landline.Text);
            cmd.Parameters.Add(prm_handler2_landline);

            SqlParameter prm_handler2_email = new SqlParameter("@handler2_email", txt_handler2_email.Text);
            cmd.Parameters.Add(prm_handler2_email);

            SqlParameter prm_handler3_name = new SqlParameter("@handler3_name", txt_handler3_name.Text);
            cmd.Parameters.Add(prm_handler3_name);

            SqlParameter prm_handler3_mobile = new SqlParameter("@handler3_mobile", txt_handler3_mobile.Text);
            cmd.Parameters.Add(prm_handler3_mobile);

            SqlParameter prm_handler3_landline = new SqlParameter("@handler3_landline", txt_handler3_landline.Text);
            cmd.Parameters.Add(prm_handler3_landline);

            SqlParameter prm_handler3_email = new SqlParameter("@handler3_email", txt_handler3_email.Text);
            cmd.Parameters.Add(prm_handler3_email);

            if (rbl_connection_type.SelectedValue == "3")
            {
                SqlParameter prm_yearly_conn_type = new SqlParameter("@connection_type_id", rbl_connection_type.SelectedValue);
                cmd.Parameters.Add(prm_yearly_conn_type);

                SqlParameter prm_fix_yearly_size = new SqlParameter("@connection_size_id", ddl_conn_size_selection_for_fixed_yearly_plan.SelectedValue);
                cmd.Parameters.Add(prm_fix_yearly_size);

                SqlParameter prm_fix_yearly_plan = new SqlParameter("@connection_plan_id", ddl_plan_selection_for_fixed_yearly_conn.SelectedValue);
                cmd.Parameters.Add(prm_fix_yearly_plan);
            }
            else if (rbl_connection_type.SelectedValue == "1")
            {
                SqlParameter prm_monthly_conn_type = new SqlParameter("@connection_type_id", rbl_connection_type.SelectedValue);
                cmd.Parameters.Add(prm_monthly_conn_type);

                SqlParameter prm_fix_monthly_size = new SqlParameter("@connection_size_id", ddl_conn_size_selection_for_fixed_plant.SelectedValue);
                cmd.Parameters.Add(prm_fix_monthly_size);

                SqlParameter prm_fix_monthly_plan = new SqlParameter("@connection_plan_id", ddl_plan_selection_for_fixed_conn.SelectedValue);
                cmd.Parameters.Add(prm_fix_monthly_plan);
            }
            else if (rbl_connection_type.SelectedValue == "2")
            {
                SqlParameter prm_monthly_conn_type = new SqlParameter("@connection_type_id", rbl_connection_type.SelectedValue);
                cmd.Parameters.Add(prm_monthly_conn_type);

                SqlParameter prm_fix_monthly_plan = new SqlParameter("@connection_plan_id", ddl_conn_size_for_meter_plan.SelectedValue);
                cmd.Parameters.Add(prm_fix_monthly_plan);
            }

            SqlParameter prm_start_wish = new SqlParameter("@connection_start_wish", wish_start_from);
            cmd.Parameters.Add(prm_start_wish);


            SqlCommand ac = new SqlCommand("select max(ac_code_int) from registration_for_comercial_industry",conn);
            object a;
            a = ac.ExecuteScalar();
            if (a.ToString() == "")
            {
                a = "1001";
                ac_code_id = ci + a;
            }
            else
            {
                Int64 v;
                v = (Int64.Parse(a.ToString())) + 1;
                ac_code_id = ci + v.ToString();


            }
            
            SqlParameter prm_ac_code_id = new SqlParameter("@ac_code_id", ac_code_id);
            cmd.Parameters.Add(prm_ac_code_id);

            ac.ExecuteNonQuery();

            SqlParameter prm_ac_code_password = new SqlParameter("@ac_password", txt_confirm_password.Text);
            cmd.Parameters.Add(prm_ac_code_password);

            cmd.ExecuteNonQuery();
            conn.Close();
        }
    }

plzz help me solve this...
thanks a lot in advance... :)

Posted 7-Jul-14 3:04am

Member 10858110

Add a Solution

Comments

Hemant Singh Rautela 7-Jul-14 9:38am

And what are your's Input data..??
As your's input comes from textbox (txt_handler1_mobile.Text ,txt_handler1_landline.Text, and many more... ) , without any validation for numeric data...!
And you have various numeric field's and int also...
So you should have validate all numeric field's before inserting directly....

(Even the blank value may cause the error in that textboxe's)...

Sergey Alexandrovich Kryukov 7-Jul-14 11:22am

Why, in first place, using varchar type where you need numeric?
—SA

2 solutions

Solution 1

You are trying to cast a varchar into numeric.
For e.g. trying to store 'ABC' in a number field will throw an error.

You have to validate data before trying to insert into the database.

Posted 7-Jul-14 3:10am

Abhinav S

Comments

Member 10858110 7-Jul-14 9:29am

with respect, sir/mam...
i know that, i m having error b'coz of storing varchar data to numbers...
even i make it debuge also... but i cant find any solution...

thats y , i enterd whole code here... that, if they find any solution , they can tell me...

so, sir plz see the code and,
can u tell me, what is wrong in it ???

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Accepted Answer · 2014-07-07T03:33:00

According to the Exception, following are the parameters, in which you are passing Varchar value instead of Numeric.

@handler1_mobile numeric(14,0),
@handler1_landline numeric(18,0),

@handler2_mobile numeric(14,0),
@handler2_landline numeric(18,0),

@handler3_mobile numeric(14,0),
@handler3_landline numeric(18,0),

Follwoing is the code. I guess you have this bunch of codes at multiple place. So, rectify all. Underlined codes are Varchar.

C#

SqlParameter prm_handler1_mobile = new SqlParameter("@handler1_mobile", txt_handler1_mobile.Text);
cmd.Parameters.Add(prm_handler1_mobile);

SqlParameter prm_handler1_landline = new SqlParameter("@handler1_landline", txt_handler1_landline.Text);
cmd.Parameters.Add(prm_handler1_landline);

SqlParameter prm_handler2_mobile = new SqlParameter("@handler2_mobile", txt_handler2_mobile.Text);
cmd.Parameters.Add(prm_handler2_mobile);

SqlParameter prm_handler2_landline = new SqlParameter("@handler2_landline", txt_handler2_landline.Text);
cmd.Parameters.Add(prm_handler2_landline);

SqlParameter prm_handler3_mobile = new SqlParameter("@handler3_mobile", txt_handler3_mobile.Text);
cmd.Parameters.Add(prm_handler3_mobile);

SqlParameter prm_handler3_landline = new SqlParameter("@handler3_landline", txt_handler3_landline.Text);
cmd.Parameters.Add(prm_handler3_landline);