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.


C#
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;
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
    }
Posted
Updated 12-Feb-12 2:22am
v3

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]
   
v2
SQL
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.

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

SET IDENTITY_INSERT dbo.emp ON
insert into emp (empno, ename,sal,deptno)
values (@empno, @ename, @sal, @deptno)



visit the following links below for more references
http://www.sqlservercentral.com/Forums/Topic423276-146-1.aspx[^]
http://www.dbforums.com/microsoft-sql-server/765137-identity_insert-not-happening.html[^]
http://www.dotnetspider.com/forum/157155-Insert-data.aspx[^]

If this will help you
please Vote or Accept solution
Thanks
   
v2
Comments
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