none
Error Converting DataType nvarchar to Int

    Question

  • Hi All !!

    When i Perform the Update Operation i'm getting Error Converting DataType nvarchar to int. I'l post the Codes wch i used ::

    First Part is SQL Queries ::
    CREATE TABLE Patient_Info
    (
    
    ID UNIQUEIDENTIFIER,
    NAME VARCHAR(30),
    AGE INT NOT NULL, 
    SEX VARCHAR(30),
    PROBLEM VARCHAR(255),
    PHONE INT NOT NULL,
    ADDRESS VARCHAR(30),
    COMMENTS VARCHAR(255),
    )
    
    /* INSERT STORED PROCEDURE */
    CREATE PROCEDURE INSERTDATA
    (
    @ID UNIQUEIDENTIFIER,
    @NAME VARCHAR(30),
    @AGE INT,
    @SEX VARCHAR(30),
    @PROBLEM VARCHAR(255),
    @PHONE INT,
    @ADDRESS VARCHAR(30),
    @COMMENTS VARCHAR(30)
    )
    AS 
    INSERT INTO [Patient_Info] ([ID],[NAME],[AGE],[SEX],[PROBLEM],[PHONE],[ADDRESS],[COMMENTS]) 
    Values
    (@ID,@NAME,@AGE,@SEX,@PROBLEM,@PHONE,@ADDRESS,@COMMENTS)
    
    /* UPDATE STORED PROCEDURE */
    CREATE PROCEDURE UPDATEDATA
    (
    
    @ID UNIQUEIDENTIFIER,
    @NAME VARCHAR(30),
    @AGE INT,
    @SEX VARCHAR(30),
    @PROBLEM VARCHAR(255),
    @PHONE INT,
    @ADDRESS VARCHAR(30),
    @COMMENTS VARCHAR(255)
    )
    AS 
    UPDATE [Patient_Info] 
    SET
    [NAME]=@NAME,
    [AGE]=@AGE,
    [SEX]=@SEX,
    [PROBLEM]=@PROBLEM,
    [PHONE]=@PHONE,
    [ADDRESS]=@ADDRESS,
    [COMMENTS]=@COMMENTS WHERE [ID]=@ID
    
    Second is Respective C# Code ::
    
     public int Insert(Patient_Class patient)
    
     {
     SqlConnection conn = new SqlConnection(connStr);
     conn.Open();
     SqlCommand cmd = new SqlCommand("INSERTDATA", conn);
     cmd.CommandType = CommandType.StoredProcedure;
    
     try
    
    
     {
     cmd.Parameters.AddWithValue("@ID", patient.Id);
     cmd.Parameters.AddWithValue("@NAME", patient.Name);
     cmd.Parameters.AddWithValue("@AGE", patient.Age);
     cmd.Parameters.AddWithValue("@SEX", patient.Sex);
     cmd.Parameters.AddWithValue("PROBLEM", patient.Problem);
     cmd.Parameters.AddWithValue("PHONE", patient.Phone);
     cmd.Parameters.AddWithValue("ADDRESS", patient.Address);
     cmd.Parameters.AddWithValue("COMMENTS", patient.Comments);
    
     return cmd.ExecuteNonQuery();
    
     }
    
    
    
    
     catch
    
     {
     throw;
    
     }
     finally
    
     {
     cmd.Dispose();
    
     conn.Close();
     conn.Dispose();
    
     }
    
    
     }
    
    
     public int Update(Patient_Class patient)
    
     {
    
    
     SqlConnection conn = new SqlConnection(connStr);
     conn.Open();
    
     SqlCommand cmd = new SqlCommand("UPDATEDATA", conn);
    
     cmd.CommandType = CommandType.StoredProcedure;
    
     try
     {
     cmd.Parameters.AddWithValue("@ID", patient.Id);
     cmd.Parameters.AddWithValue("@NAME", patient.Name);
     cmd.Parameters.AddWithValue("@AGE", patient.Age);
     cmd.Parameters.AddWithValue("@SEX", patient.Sex);
     cmd.Parameters.AddWithValue("PROBLEM", patient.Problem);
     cmd.Parameters.AddWithValue("PHONE", patient.Phone);
     cmd.Parameters.AddWithValue("ADDRESS", patient.Address);
     cmd.Parameters.AddWithValue("COMMENTS", patient.Comments);
    
     return cmd.ExecuteNonQuery();
    
     }
    
     catch
     {
     throw;
    
     }
    
     finally
    
     {
    
     cmd.Dispose();
     conn.Close();
     conn.Dispose();
    
     }
     This is the Code i wrote for "UPDATE" Button ::
    
    private void btn_Update_Click(object sender, EventArgs e)
    
      {
       int intResult = 0;
       dbAccess_Class dbClass = new dbAccess_Class();
    
       Patient_Class patient = new Patient_Class();
    
       patient.Id = lbl_ID.Text;
    
       patient.Name = text_NAME.Text;
    
       patient.Age = Int32.Parse(text_AGE.Text);
    
       patient.Sex = text_SEX.Text;
    
       patient.Problem = text_Prob.Text;
    
       patient.Phone = Int32.Parse(text_Phone.Text);
    
       patient.Address = text_ADDRESS.Text;
    
       patient.Comments = text_Comments.Text;
       try
    
       {
    
        intResult = dbClass.Update(patient);
    
        if (intResult > 0)
    
         MessageBox.Show("Updated in Database");
    
        dataGridView1.DataSource = ds.Tables[0];
     
    
       }
    
       catch (Exception ex)
    
       {
    
        MessageBox.Show(ex.Message.ToString());
    
       }
    
       finally
    
       {
    
        patient = null;
    
        dbClass = null;
    
       } 
    
    
    
      }
    
    
    Insertion is Performed Well, But during the Update Process, after updating some value, i click the "UPDATE" Button 
    
    that Time i'm getting the Error "ERROR CONVERTING DATATYPE NVARCHAR TO INT". Even in the UPDATE Button Function if i
    
    call the Insert function that time It's getting Inserting Again, I don't know Y for Update dis Type of error
    
    coming. Pls Help me to Solve this Problem, Thanks a Lot in Advance !!
    
    
    
    

    :) TAMILAN2233 (:
    • Edited by tamilan2233 Thursday, February 17, 2011 4:14 PM
    Thursday, February 17, 2011 3:35 PM

Answers

  • One thing...

    create "Phone" parameter as varchar - so you will be able to insert 0 as 1st character and spaces. Believe me - you will have less headackes (tested on my skin).

    Maybe this is causing the problems. Change the data type in database (to varchar, 50), and in all the code (remove int.Parse), and in sroredProcedure (PHONE VARCHAR (50))

    Hope this salves the issue.

     

    • Proposed as answer by Naomi NModerator Thursday, February 17, 2011 10:38 PM
    • Marked as answer by tamilan2233 Friday, February 18, 2011 3:53 AM
    Thursday, February 17, 2011 6:23 PM

All replies

  • This happens when you try to insert nvarchar datatype into an int datatype column.

    Your table has 2 column with INT datatype AGE and PHONENUM.

    Check you data to see if you have characters for this columns.


    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    Thursday, February 17, 2011 3:39 PM
  • Thanks for ur Response !!

    CREATE TABLE Patient_Info
    (
    ID UNIQUEIDENTIFIER,
    NAME VARCHAR(30),
    AGE INT NOT NULL, 
    SEX VARCHAR(30),
    PROBLEM VARCHAR(255),
    PHONE INT NOT NULL,
    ADDRESS VARCHAR(30),
    COMMENTS VARCHAR(255),
    )
    

    In my Table i've assigned "Int" Values only for the "Age" and "PhoneNo" Colums, and i've one Doubt if it is So, then during the Insert

    also it should Show dis Error na, But it's getting Inserted, Only "error converting datatype nvarchar to int" comes When the Update

    Operation happens !!


    :) TAMILAN2233 (:

    Thursday, February 17, 2011 3:48 PM
  • One thing...

    create "Phone" parameter as varchar - so you will be able to insert 0 as 1st character and spaces. Believe me - you will have less headackes (tested on my skin).

    Maybe this is causing the problems. Change the data type in database (to varchar, 50), and in all the code (remove int.Parse), and in sroredProcedure (PHONE VARCHAR (50))

    Hope this salves the issue.

     

    • Proposed as answer by Naomi NModerator Thursday, February 17, 2011 10:38 PM
    • Marked as answer by tamilan2233 Friday, February 18, 2011 3:53 AM
    Thursday, February 17, 2011 6:23 PM
  • I agree with Mitja's comment. You cannot use int for phone numbers. Zeroes in phone numbers are usually significant... There are also non-digit numbers in phone numbers. For instance my number (with some digits moved around) is +46-37-5061147. The hyphens are not really needed, but the + is important. And moreover, all those digits do not fit into an int.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Thursday, February 17, 2011 10:31 PM