Excel 2 SqlDatabase import data

Question

1.00/5 (1 vote)

See more:

SQL-server-2000

SQL-Server-2005

, +

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 12-Feb-12 1:55am

2011999

Updated 12-Feb-12 2:22am

Costica U

v3

Add a Solution

2 solutions

Solution 1

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]

Posted 12-Feb-12 2:09am

OriginalGriff

Updated 12-Feb-12 2:10am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

zyck · Accepted Answer · 2012-02-12T02:13:00

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