Error Converting datatype nvarchar to int from c# to sql

Question

Hi Guys please help me out in my code where I got stuck and went wrong, I have two tables; one employee table and another branch table. The branch id will be stored in employee table which is a foreign key. When I'm inserting the data through stored procedure where I kept the code like bleow

Create procedure [dbo].[Insertemployee]

@d28 int,@d1 nchar(15),@d2 nchar(30),@d3 int,@d4 nchar(10),@d5 nchar(20),@d6 nchar(30),@d7 varchar(100),@d8 nchar(100),@d9 nchar(10),@d10 nchar(10),@d11 nchar(30),@d12 nchar(30),@d13 nchar(30),@d14 nchar(30),@d15 nchar(30),@d16 date,@d17 varchar(100),@d18 int,@d19 int,@d20 int,@d21 int,@d22 int,@d23 int,@d24 int,@d25 image,@d26 date,@d27 date,@d29 nchar(10)
AS
    BEGIN
        SET NOCOUNT ON
    BEGIN TRY
        BEGIN TRANSACTION
insert into Employee(Id,StaffID,StaffName,Branchid,Gender,DOB,FatherName,PermanentAddress,Country,PhoneNo,MobileNo,DateOfJoining,VisaNumber,PassportNumber,PassportExpiryDate,NationalityID,NationalityIDExpiryDate,Designation,BasicSalary,CostOfAllowance,HRA,TeaAndSnacks,Bonus,Loan,OtherDeduction,Picture,createddate,lastmodified,Active) VALUES (@d28,@d1,@d2,(select branchid from Branch where Branch.BranchName=@d3),@d4,@d5,@d6,@d7,@d8,@d9,@d10,@d11,@d12,@d13,@d14,@d15,@d16,@d17,@d18,@d19,@d20,@d21,@d22,@d23,@d24,@d25,@d26,@d27,@d29);
COMMIT TRANSACTION
    END TRY
        BEGIN CATCH
            IF @@TRANCOUNT >0
            BEGIN
                ROLLBACK TRANSACTION
            END
            EXECUTE uspLogError 
        END CATCH
END;`

and my front end code is below

 cmd = new SqlCommand("InsertEmployee");
            cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;
            cmd.Parameters.AddWithValue("@d28",txtID.Text);

                cmd.Parameters.AddWithValue("@d26", System.DateTime.Now);
                cmd.Parameters.AddWithValue("@d27", System.DateTime.Now);

            cmd.Parameters.AddWithValue("@d1", txtStaffID.Text);

                cmd.Parameters.AddWithValue("@d2",txtStaffName.Text);
                cmd.Parameters.AddWithValue("@d3",txtDepartment.Text);
                cmd.Parameters.AddWithValue("@d4",cmbGender.Text);
                cmd.Parameters.AddWithValue("@d6",txtFatherName.Text);
                cmd.Parameters.AddWithValue("@d7",txtPAddress.Text);
                cmd.Parameters.AddWithValue("@d8",txtTAddress.Text);
                cmd.Parameters.AddWithValue("@d9",txtPhoneNo.Text);
                cmd.Parameters.AddWithValue("@d10",txtMobileNo.Text);
                cmd.Parameters.AddWithValue("@d11",dtpDateOfJoining.Value);
                cmd.Parameters.AddWithValue("@d12",visanumber.Text.Trim());
                cmd.Parameters.AddWithValue("@d14",PassportExpirydate.Text);
                cmd.Parameters.AddWithValue("@d15",NationalityID.Text.Trim());
            if (NationalityID.Text == "")
            {
                cmd.Parameters.AddWithValue("@d16", "");
            }
            else
            {
                cmd.Parameters.AddWithValue("@d16", NationalityExpirydate.Value);
            }
                cmd.Parameters.AddWithValue("@d17",txtDesignation.Text.Trim());
                cmd.Parameters.AddWithValue("@d13",Passportnumber.Text.Trim());
                cmd.Parameters.AddWithValue("@d18",Convert.ToInt32(txtBasicSalary.Text.Trim()));
                if (txtLIC.Text == "")
                {
                    cmd.Parameters.AddWithValue("@d19", 0);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@d19",Convert.ToInt32(txtLIC.Text.Trim()));

                }
                if (txtGrpInsurance.Text == "")
                {
                    cmd.Parameters.AddWithValue("@d21", 0);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@d21",Convert.ToInt32(txtGrpInsurance.Text.Trim()));
                }
                if (txtFamilyBenefitFund.Text == "")
                {
                    cmd.Parameters.AddWithValue("@d22",0);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@d22",Convert.ToInt32(txtFamilyBenefitFund.Text.Trim()));
                }
                if (txtLoans.Text == "")
                {
                    cmd.Parameters.AddWithValue("@d23",0);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@d23",Convert.ToInt32(txtLoans.Text.Trim()));
                }
                if (txtOtherDeductions.Text == "")
                {
                    cmd.Parameters.AddWithValue("@d24",0);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@d24",Convert.ToInt32(txtOtherDeductions.Text.Trim()));
                }
                if (txtIncomeTax.Text == "")
                {
                    cmd.Parameters.AddWithValue("@d20",0);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@d20",Convert.ToInt32(txtIncomeTax.Text.Trim()));
                }
            cmd.Parameters.AddWithValue("@d29", chkActive.Text);
                cmd.Parameters.AddWithValue("@d5",DOB.Text);
                MemoryStream ms = new MemoryStream();
                Bitmap bmpImage = new Bitmap(pictureBox1.Image);

                bmpImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);

                byte[] data = ms.GetBuffer();
                SqlParameter p = new SqlParameter("@d25", SqlDbType.Image);
                p.Value = data;
                cmd.Parameters.Add(p);
                cmd.ExecuteNonQuery();

You must use the meaning full parameter names instead of using d2, d3. Also @d28 first parameter is passed as a string for id column which is int. Look for similar mistakes as this might not be the only mistake. — Adil
– Adil, Commented Jun 23, 2016 at 6:38

Midhun Mundayadan · Accepted Answer · 2016-06-23 06:35:19Z

1

d3 field can accept int but you are passing string`

default type of TextBox is string

you can fix this by converting to int

cmd.Parameters.AddWithValue("@d3",txtDepartment.Text);

to

 cmd.Parameters.AddWithValue("@d3",Convert.ToInt(txtDepartment.Text));

answered Jun 23, 2016 at 6:35

Midhun Mundayadan

3,1923 gold badges24 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Iqbal Khan Over a year ago

i did this before but i got an error as` input string was not in correct format `

Midhun Mundayadan Over a year ago

@IqbalKhan i put one example above now you have to check what you are passing... what you stored procedure can accept to do transaction

Malcolm · Accepted Answer · 2016-06-23 06:48:37Z

1

You are passing few fields as string to DB datatype int :

cmd.Parameters.AddWithValue("@d28",txtID.Text);

cmd.Parameters.AddWithValue("@d3",txtDepartment.Text);

you should convert them to int prior passing it to db

 cmd.Parameters.AddWithValue("@d28",Convert.ToInt32(txtID.Text.Trim()));
  cmd.Parameters.AddWithValue("@d3",Convert.ToInt32(txtDepartment.Text.Trim()));

Just curious how come your txtDepartment be type of int , either you have to make a check whether there is string being passed or its an number . If it accepts only number you might have to check the value being passed to txtDepartment.Text and vice -versa.

edited Jun 23, 2016 at 6:48

answered Jun 23, 2016 at 6:41

Malcolm

1,8073 gold badges23 silver badges49 bronze badges

3 Comments

Iqbal Khan Over a year ago

I'm Still getting same error as input string was not in correct format

Malcolm Over a year ago

@IqbalKhan what is value in the txtID.Text and txtDepartment.Text

Iqbal Khan Over a year ago

I'm inserting integer value in txtid.Text and in txtdepartment.text containing the reference branchid from branch table like branchname-electronics & branchid -1 so this branchid stores in txtdepartment.text

sujith karivelil · Accepted Answer · 2016-06-23 06:59:05Z

1

I think the above answer is correct and he find the issues as well(Congrats). But the actual problem here is the Parameters.AddWithValue. It will not allows you to specify the type and will cause this kind of datatype conversion errors. So What i suggest you is, use Parameters.Add() instead for Parameters.AddWithValue() So that you can specify the type of expecting data and there by able to validate the input. you can see the below example(which help you to solve the issue as well):

cmd.Parameters.Add("@d28", SqlDbType.Int).Value = int.Parse(txtID.Text.Trim());

Where SqlDbType is an Enumeration which Specifies SQL Server-specific data type of a field, property, for use in a SqlParameter. int.TryParse() will be another best suggestion for you to perform error free conversion from string to integer.

Update : It seems The SP accepts values of different types, but you are giving all as string(.Text). This will create the same issue, So changing only "@d28" will not solve the issue, Try to read the attached link and make necessary changes in the code.

edited Jun 23, 2016 at 6:59

answered Jun 23, 2016 at 6:41

sujith karivelil

29.1k7 gold badges60 silver badges90 bronze badges

5 Comments

Iqbal Khan Over a year ago

I'm Still getting same error as input string was not in correct format

sujith karivelil Over a year ago

@IqbalKhan : I hope you changed only @d28; Please change all parameters as per my suggestion and see what is happening. see the updates

Iqbal Khan Over a year ago

I thoroughly checked the parameters with using break point it shown error when it reaches to ExecutenonQuery

sujith karivelil Over a year ago

@IqbalKhan : Adding values to the parameters associated with the command will not create errors in this situation. It will passed to the stored procedure at the time of execution of the query. At that time it determines and shows the error.

Iqbal Khan Over a year ago

yes i knew but what my intention is tell to you that might be i did a mistake in a stored procedure where i passed the value of branch as (select branchid from Branch where Branch.BranchName=@d3) might be i want to convert here any cast or convert.....

Collectives™ on Stack Overflow

Error Converting datatype nvarchar to int from c# to sql

3 Answers 3

2 Comments

3 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related