Click here to Skip to main content
14,880,336 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Dear All,

I was write a program for Excel Spredsheet data to SqlSarver database. when i am running a program i got this error(Upload data excel to Db)

Near the cmd.Executenonquery
Cannot insert explicit value for identity column in table 'Emp' when IDENTITY_INSERT is set to OFF.

public void insertdataintosql(string EmpId, string EmpName, string EmpSalary, string IsActive)
               SqlConnection conn = new SqlConnection ("Data Source=BBU\\SQLEXPRESS;Initial Catalog=DataBase1;Integrated Security=True");
        //SqlConnection conn = new SqlConnection("Data Source=.\\sqlexpress;AttachDbFileName=C:\\Program Files\\Microsoft SQL Server\\MSSQL10.SQLEXPRESS\\MSSQL\\DATA\\Information.mdf;Trusted_Connection=yes");
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = "insert into Emp (EmpId,EmpName,EmpSalary,IsActive) values(@EmpId,@EmpName,@EmpSalary,@IsActive)";
        cmd.Parameters.Add("@EmpId", SqlDbType.Int).Value = EmpId;
        cmd.Parameters.Add("@EmpName", SqlDbType.NVarChar).Value = EmpName;
        cmd.Parameters.Add("@EmpSalary", SqlDbType.NVarChar).Value = EmpSalary;
        cmd.Parameters.Add("@IsActive", SqlDbType.NVarChar).Value = IsActive;
        cmd.CommandType = CommandType.Text;
Updated 12-Feb-12 2:22am

The error is quite explicit: you cannot write to a field you have marked as "Identity" unless you turn on IDENTITY_INSERT first.
An Identity field is controlled by the DB, and it is responsible to assigning values. When you try to write to the ID field, it complains and won't let you.

Either stop using Identity on the field (if the data is always going to come from Excel), do not write the ID field to the DB, or write a stored procedure to turn IDENTITY_INSERT on, insert the row, and turn it back off.

[edit]on / off the wrong way round - OriginalGriff[/edit]
SET IDENTITY_INSERT dbo.emp ON --> means: please no auto increment, i want to insert the value myself.
SET IDENTITY_INSERT dbo.emp OFF --> means: please auto increment and i cannot insert the value myself.

you would set identity insert on only in the case that you would like to provide the identity value in your insert statement.

insert into emp (empno, ename,sal,deptno)
values (@empno, @ename, @sal, @deptno)

visit the following links below for more references[^][^][^]

If this will help you
please Vote or Accept solution
2011999 12-Feb-12 8:48am
Thank u my problem is solved
zyck 12-Feb-12 10:02am
your welcome (n_n)

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