Excel 2 SqlDatabase导入数据 [英] Excel 2 SqlDatabase import data

问题描述

亲爱的所有人，


我正在为Excel Spredsheet数据编写一个程序到SqlSarver数据库.当我运行程序时，出现此错误(将数据excel上载至Db)

靠近cmd.Executenonquery
当IDENTITY_INSERT设置为OFF时，无法为表"Emp"中的标识列插入显式值.

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;
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
    }

推荐答案

该错误非常明显:除非先打开IDENTITY_INSERT，否则无法写入标记为"Identity"的字段.
身份字段由数据库控制，它负责分配值.当您尝试写入ID字段时，它会抱怨并且不会让您.

要么停止在该字段上使用Identity(如果数据总是要来自Excel)，请不要将ID字段写入数据库，或者编写一个存储过程来打开IDENTITY_INSERT，插入该行，然后再将其关闭.

以错误的方式打开/关闭-OriginalGriff [/edit]

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.

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

请访问下面的链接以获取更多参考信息
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 [ ^ ]

如果这对您有帮助
请投票或接受解决方案
谢谢

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

这篇关于Excel 2 SqlDatabase导入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！