将varchar值转换为int时转换失败 [英] Conversion failed when converting the varchar value to int

问题描述

Hi
转换错误将varchar值转换为int时转换失败



注意：Age（txtAge）列是整数。



我尝试了什么：

Hi There is a conversion error "Conversion failed when converting the varchar value to int"

Note: Age(txtAge) column is integer.

What I have tried:

<pre>public void Bind_ddlState()
        {


            SqlCommand cmd = new SqlCommand("SELECT [StateID] ,[StateName]   FROM [StateDetails] where [StateID]='" + DDlCountry.SelectedValue + "'", cn);
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            Ddlstate.DataSource = dt;
            Ddlstate.DataTextField = "StateName";
            Ddlstate.DataValueField = "StateID";
            Ddlstate.DataBind();

            cn.Close();
        }



        protected void Button1_Click1(object sender, EventArgs e)
        {
            {
                cn.Open();
                SqlCommand cmd = new SqlCommand("INSERT INTO [RegisDetails] (Name, Department,Salry,DOJ,DOB, Age,Country,Stat,Phone,Email,Pincode)  VALUES ('" + txtname.Text + "','" + txtDept.Text + "','" + txtSalary.Text + "','" + txtDoj.Text + "','" + txtDob.Text + "','" + txtAge.Text + "','" + DDlCountry.Text + "','" + Ddlstate.Text + "','" + txtPhone.Text + "','" + TxtEmail.Text + "','" + txtPin.Text + "' )", cn);

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

        }


        protected void Button2_Click1(object sender, EventArgs e)
        {
            this.ClearCachedClientID();


        }

        protected void DDlCountry_SelectedIndexChanged(object sender, EventArgs e)
        {

            Bind_ddlState();

        }

      

        protected void txtDate1_TextChanged(object sender, EventArgs e)
        {


        }

        protected void txtDate2_TextChanged(object sender, EventArgs e)
        {

        }

        protected void txtAge_TextChanged(object sender, EventArgs e)
        {
            DateTime dateOfBirth = new DateTime();
            int currentYear, currentMonth, birthMonth, birthYear, years, months;
            dateOfBirth = Convert.ToDateTime(txtAge.Text);
            currentYear = Convert.ToInt32(DateTime.Now.Year);
            currentMonth = Convert.ToInt32(DateTime.Now.Month);
            birthYear = Convert.ToInt32(dateOfBirth.Year);
            birthMonth = Convert.ToInt32(dateOfBirth.Month);
            years = currentYear - birthYear;
            if ((currentMonth - birthMonth > 0))
            {
                months = Convert.ToInt32(currentMonth - birthMonth);
            }
            else
            {
                years = years - 1;
                months = Convert.ToInt32((12 - birthMonth) + currentMonth);
            }
            txtAge.Text = years.ToString() + "/" + months.ToString();


        }

推荐答案

首先不要这样做！永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。改为使用参数化查询。



使用各种TryParse方法将用户输入转换为适当的数据类型 - 数字转换为 int 或 double 变量，日期到 DateTime 等等 - 然后将转换后的值作为参数传递给SQL，记住向用户报告任何问题，而不是盲目地假设他的输入是正确的。



这样，不仅是因为故意或意外损坏您的数据库更安全，而且你注意到的问题会在同一时间消失。

Start by not doing it like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

Use the various TryParse methods to convert your user inputs to appropriate datatypes - numbers into int or double variables, dates into DateTime and so forth - and then pass the converted values to SQL as parameters, remembering to report any problems to the user instead of just blindly assuming his input is correct.

That way, not only is your database safer from deliberate or accidental damage, but the problem you are noticing will disappear at the same time.

将varchar值转换为int时转换失败



这个因为将字符串值插入表中的整数字段而发生问题。



在数据库表中有4列（Salary，Age，Phone，Pincode）是数据类型int。



所以请检查你传递的所有值是否都是int这些列的工具与否（薪水，年龄，电话，Pincode）。



注意：单引号引用的任何整数值都可以插入整数字段而不给出任何错误。例如：'0'或'123'

"Conversion failed when converting the varchar value to int"

This issue occurred because of inserting a string value into an integer field in the table.

In you database table there are 4 columns (Salary, Age, Phone, Pincode) which are of datatype int.

So Please check whether all values which your passing all are integer or not for these columns (Salary, Age, Phone, Pincode).

Note: Any integer value quoted with single quote can insert to an integer field with out giving any error. Ex: '0' or '123'

首先，将输入中的值附加到SQL语句是一个很大的NO NO，因为它可能会导致SQL注入攻击。阅读：保护您的数据：防止SQL注入[^] [< a href =http://www.codeproject.com/Articles/1105224/Protect-Your-Data-Prevent-SQL-Injectiontarget =_ blanktitle =New Window> ^ ]



其次，养成在中放置食用资源的对象的习惯，例如 SqlConnection 使用语句确保对象在使用后正确处理和关闭。



三，不要编写用于计算的代码 TextChanged 事件的年龄，因为当您从 TextBox 中键入/更改某些内容时，将始终触发该事件。作为替代方案，您可以在按钮的点击事件中编写代码。



现在关闭你的问题...



你说，年龄列是整数类型。现在在 TextChanged 事件中查看您的代码，您正在使用它：

First off, appending the values from your input to your SQL statement is a big NO NO, as it can lead you to SQL injection attack. Read: Protect Your Data: Prevent SQL Injection[^][^]

Second, make it a habit to put objects that eat resources such as SqlConnection within a using statement to ensure that objects will be properly disposed and closed after they are used.

Third, don't write your code for calculating the age at TextChanged event because that event will always be triggered when you type/change something from the TextBox. As an alternative, you could instead write the code at Button's Click event.

Now down your issue...

You said, Age column is of type integer. Now looking at your code at TextChanged event, you are using this:

txtAge.Text = years.ToString() + "/" + months.ToString();

现在你的txtAge.Text值包含字符/，它会触发你的错误。整数类型只能接受数值，因此您需要确保TxtAge的值只应包含数值。为了防止这种情况，您可以：



1.实现客户端验证，只允许使用JavaScript / jQuery为txtAge提供数值

2.按照建议，使用 Int32.TryParse Method（System） [ ^ ]重新验证服务器上的值。例如：

Now your txtAge.Text value contains the character "/" which triggers the error you have. An integer type can only accept numeric values so you need to ensure that the value of your TxtAge should only contain numeric values. To prevent that, you could:

1. implement client-side validation to allow only numeric values for your txtAge using JavaScript/jQuery
2. as suggested, use Int32.TryParse Method (System)[^] to re-validate the value at the server. For example:

int age;
if(Int32.TryParse(txtAge.Text.Trim(), out age))
{
    //do stuff here
    //use the value of age variable in your query
}
else
{
    Response.Write("Not valid");
}

这篇关于将varchar值转换为int时转换失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！