如何解决这个错误？ “将数据类型nvarchar转换为数字时出错”。 [英] how to solve this error ? "Error converting data type nvarchar to numeric."

问题描述

我正在制作一个注册表格...我想根据用户选择的选项进入数据表...

但是当我点击提交按钮时。它向我显示这样的错误...

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 

im使用存储过程...

和商店程序如下。

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数字（18,0），

@hand ler2_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

@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

所有数据类型是与上面的sqltable相同...





这是我的aspx.cs代码...

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帮我解决这个问题......

非常感谢... ...）

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

推荐答案

您正在尝试将varchar转换为数字。

例如尝试将'ABC'存储在数字字段中会产生错误。



您必须在尝试插入数据库之前验证数据。

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.

根据Exception，以下是参数，您在其中传递 Varchar 值而不是 Numeric 。

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是代码。我想你在多个地方都有这堆代码。所以，纠正一切。带下划线的代码是 Varchar 。

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

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

这篇关于如何解决这个错误？ “将数据类型nvarchar转换为数字时出错”。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！