将数据类型varchar转换为int时出错。 [英] Error converting data type varchar to int.

问题描述

嗨
我在添加员工详细信息时GridView中有转换类型错误。我使用了存储过程。



我尝试过什么：

Hi I Have Convertion type error in the GridView while Adding Employee details.I have used Stored procedure.

What I have tried:

ALTER PROCEDURE [dbo].[gvempdetails]

     @Action VARCHAR (10),
     @EMPLOYEEID Int =0,
     @EMPLOYEENAME VARCHAR(100)= NULL ,
     @DEPARTMENT VARCHAR(100)= NULL ,
     @AGE VARCHAR(30)= NULL,
     @SALARY INT = NULL
     
AS
BEGIN
      SET NOCOUNT ON 
     IF @Action = 'SELECT'
      BEGIN
      SELECT EmpId,EmpName,Dep,Age,Sal
      FROM gvdetails17
       END
       
     IF @Action='INSERT'
       BEGIN
       INSERT INTO gvdetails17(EmpId,EmpName,Dep,Age,Sal) VALUES(@EMPLOYEEID,@EMPLOYEENAME,@DEPARTMENT,@AGE,@SALARY)
        END       
            
     IF @Action ='UPDATE'
      BEGIN
      UPDATE gvdetails17 SET EmpName=@EMPLOYEENAME,Dep=@DEPARTMENT,Age=@AGE,Sal=@SALARY WHERE EmpId=@EMPLOYEEID
      END
      
      
     IF @Action='DELETE'
      BEGIN
      DELETE FROM gvdetails17 where EmpId=@EMPLOYEEID
      END
      SET NOCOUNT OFF
      END

代码背后

Code Behind

protected void gvDetails_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName.Equals("Add"))
            {

                TextBox txtEmpId = (TextBox)gvDetails.FooterRow.FindControl("txtempid");
                TextBox txtEmpName = (TextBox)gvDetails.FooterRow.FindControl("txtempname1");
                TextBox txtEmpDep = (TextBox)gvDetails.FooterRow.FindControl("txtdep1");
                TextBox txtAge = (TextBox)gvDetails.FooterRow.FindControl("txtage1");
                TextBox txtSal = (TextBox)gvDetails.FooterRow.FindControl("txtsal1");

                string Id = (txtEmpId.Text);
                string name = txtEmpName.Text;
                string department = txtEmpName.Text;
                string age = txtEmpDep.Text;
                string salary = txtEmpDep.Text;

                INSERTEmployee(Id, name, department, age, salary);
                gvDetails.EditIndex = -1;
                bind();


            }
        }
        protected void INSERTEmployee(string Id, string name, string department, string age, string salary)
        {

            SqlCommand cmd = new SqlCommand();
            cn.Open();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "gvempdetails22";

            cmd.Parameters.Add(new SqlParameter("@Action", SqlDbType.VarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@EMPLOYEEID", SqlDbType.VarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@EMPLOYEENAME", SqlDbType.VarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@DEPARTMENT", SqlDbType.VarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@AGE", SqlDbType.VarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@SALARY", SqlDbType.VarChar, 50));

            cmd.Parameters["@Action"].Value = "INSERT";
            cmd.Parameters["@EMPLOYEEID"].Value = Id;
            cmd.Parameters["@EMPLOYEENAME"].Value = name;
            cmd.Parameters["@DEPARTMENT"].Value = department;
            cmd.Parameters["@AGE"].Value = age;
            cmd.Parameters["@SALARY"].Value = salary;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            gvDetails.DataSource = ds;
            gvDetails.DataBind();
            cn.Close();

推荐答案

可能的错误来源是年龄。为什么年龄和薪水都是varchar？它们不是天生的数字吗？似乎age是数据库表中的int数据类型，但是您的存储过程是在声明varchar，将其更改为int。

在代码背后，使用 SqlParameterCollection.AddWithValue方法（String，Object）（System.Data.SqlClient） [ ^ ]并将字符串解析为int，例如

The likely source of error is the age. Why age and salary are varchar? Are they not number by nature? It seems that the age is of int data type in the database table but your store procedure is stating varchar, change it to int.
On code behind, use SqlParameterCollection.AddWithValue Method (String, Object) (System.Data.SqlClient)[^] and parse the string to int, e.g.

cmd.Parameters.AddWithValue("@AGE", int.Parse(age.Trim()));

将你传递给SQL的内容与你告诉SQL的内容进行比较期待：

Compare what you are passing to SQL with what you tell SQL to expect:

ALTER PROCEDURE [dbo].[gvempdetails]

     @Action VARCHAR (10),
     @EMPLOYEEID Int =0,
     @EMPLOYEENAME VARCHAR(100)= NULL ,
     @DEPARTMENT VARCHAR(100)= NULL ,
     @AGE VARCHAR(30)= NULL,
     @SALARY INT = NULL

告诉SQL期望 @EMPLOYEEID 和 @SALARY 是整数值。

但是你没有传递整数：

Tells SQL to expect @EMPLOYEEID and @SALARYto be integer values.
But you don't pass integers:

cmd.Parameters.Add(new SqlParameter("@Action", SqlDbType.VarChar, 50));
cmd.Parameters.Add(new SqlParameter("@EMPLOYEEID", SqlDbType.VarChar, 50));
cmd.Parameters.Add(new SqlParameter("@EMPLOYEENAME", SqlDbType.VarChar, 50));
cmd.Parameters.Add(new SqlParameter("@DEPARTMENT", SqlDbType.VarChar, 50));
cmd.Parameters.Add(new SqlParameter("@AGE", SqlDbType.VarChar, 50));
cmd.Parameters.Add(new SqlParameter("@SALARY", SqlDbType.VarChar, 50));

您传递字符串，因此SQL会尝试将它们转换为整数，可能是失败的。检查你的数据，如果它应该是一个整数，在你的C＃代码中解析它并传递一个整数值，而不是一个字符串。



另外，你的字符串大小也不匹配：你为每个传递50个字符，但有些应该是10个，或100个或30个。你需要匹配它们，并在将它传递给SQL之前验证你的数据长度，否则你将获得也可以使用后面的截断错误。

You pass strings, so SQL tries to convert them to integers, and is probably failing. Check your data, and if it's supposed to be an integer, Parse it in your C# code and pas an integer value, not a string.

In addition, your string sizes don't match either: you are passing 50 characters for each, but some are supposed to be 10, or 100, or 30. You need to match those, and verify your data lengths before you pass that to SQL or you will get truncation errors with those later as well.

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