Click here to Skip to main content
15,887,746 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi I Have Convertion type error in the GridView while Adding Employee details.I have used Stored procedure.

What I have tried:

SQL
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
C#
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();
Posted
Updated 22-Feb-17 21:57pm
Comments
Ehsan Sajjad 23-Feb-17 3:54am    
can you post the exception details and specifically on which line it is occuring?

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()));
 
Share this answer
 
v3
Compare what you are passing to SQL with what you tell SQL to expect:
SQL
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

Tells SQL to expect @EMPLOYEEID and @SALARYto be integer values.
But you don't pass integers:
C#
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));
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.
 
Share this answer
 
v2

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