当 IDENTITY_INSERT 设置为 OFF 时,不能为表 'Qf_Log' 中的标识列插入显式值的有关问题
当 IDENTITY_INSERT 设置为 OFF 时,不能为表 'Qf_Log' 中的标识列插入显式值的问题
有这么一张表
CREATE TABLE [dbo].[Qf_Log](
[id] [int] IDENTITY(1,1) NOT NULL,
[Empid] [int] NOT NULL,
[LogContent] [varchar](200) NOT NULL,
[LogTime] [datetime] NULL CONSTRAINT [DF_Qf_Log_LogTime] DEFAULT (getdate())
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
有这么一个存储过程
ALTER PROCEDURE [dbo].[Qf_LoginLog]
@Empid int ,
@areaid int,
@schoolid int,
@classid int,
@Content varchar(200)
AS
BEGIN
insert into dbo.Qf_Log(Empid,LogContent,Logtime)
values (@Empid,'发送给'+convert(varchar(4),@areaid)+convert(varchar(6),@schoolid)+convert(varchar(6),@classid)+'内容:'+@Content,getdate())
END
这样调用
private void LogRecord( )
{
Jxllt.DBUtility.DbHelperSQL.ConnDataBase("jxtmsg", "");
SqlParameter[] list ={ new SqlParameter("@Empid",SqlDbType.Int),
new SqlParameter("@areaid",SqlDbType.Int),
new SqlParameter("@schoolid",SqlDbType.Int),
new SqlParameter("@classid",SqlDbType.Int),
new SqlParameter("@Content",SqlDbType.VarChar,200)
};
list[0].Value = Convert.ToInt32(Session["Empid"]);
list[1].Value = Convert.ToInt32(this.DropArea.SelectedItem.Value);
if (this.DropSchool.SelectedIndex == 0)
{
list[2].Value = DBNull.Value;
}
else
{
list[2].Value = Convert.ToInt32(this.DropSchool.SelectedItem.Value);
}
if (this.DropClass.SelectedIndex == 0)
{
list[3].Value = DBNull.Value;
}
else
{
list[3].Value = Convert.ToInt32(this.DropClass.SelectedItem.Value);
}
list[4].Value = this.txtMessage.Text.ToString();
Jxllt.DBUtility.DbHelperSQL.RunProcedure("Qf_LoginLog", list);
但是在我执行的时候,它会报错说“当 IDENTITY_INSERT 设置为 OFF 时,不能为表 'Qf_Log' 中的标识列插入显式值。 ”
这是怎么回事?该怎么解决
------解决方案--------------------
有这么一张表
CREATE TABLE [dbo].[Qf_Log](
[id] [int] IDENTITY(1,1) NOT NULL,
[Empid] [int] NOT NULL,
[LogContent] [varchar](200) NOT NULL,
[LogTime] [datetime] NULL CONSTRAINT [DF_Qf_Log_LogTime] DEFAULT (getdate())
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
有这么一个存储过程
ALTER PROCEDURE [dbo].[Qf_LoginLog]
@Empid int ,
@areaid int,
@schoolid int,
@classid int,
@Content varchar(200)
AS
BEGIN
insert into dbo.Qf_Log(Empid,LogContent,Logtime)
values (@Empid,'发送给'+convert(varchar(4),@areaid)+convert(varchar(6),@schoolid)+convert(varchar(6),@classid)+'内容:'+@Content,getdate())
END
这样调用
private void LogRecord( )
{
Jxllt.DBUtility.DbHelperSQL.ConnDataBase("jxtmsg", "");
SqlParameter[] list ={ new SqlParameter("@Empid",SqlDbType.Int),
new SqlParameter("@areaid",SqlDbType.Int),
new SqlParameter("@schoolid",SqlDbType.Int),
new SqlParameter("@classid",SqlDbType.Int),
new SqlParameter("@Content",SqlDbType.VarChar,200)
};
list[0].Value = Convert.ToInt32(Session["Empid"]);
list[1].Value = Convert.ToInt32(this.DropArea.SelectedItem.Value);
if (this.DropSchool.SelectedIndex == 0)
{
list[2].Value = DBNull.Value;
}
else
{
list[2].Value = Convert.ToInt32(this.DropSchool.SelectedItem.Value);
}
if (this.DropClass.SelectedIndex == 0)
{
list[3].Value = DBNull.Value;
}
else
{
list[3].Value = Convert.ToInt32(this.DropClass.SelectedItem.Value);
}
list[4].Value = this.txtMessage.Text.ToString();
Jxllt.DBUtility.DbHelperSQL.RunProcedure("Qf_LoginLog", list);
但是在我执行的时候,它会报错说“当 IDENTITY_INSERT 设置为 OFF 时,不能为表 'Qf_Log' 中的标识列插入显式值。 ”
这是怎么回事?该怎么解决
------解决方案--------------------
- SQL code
--测试资料 CREATE TABLE t1(ID int IDENTITY,A int) INSERT t1 VALUES(1) INSERT t1 VALUES(2) INSERT t1 VALUES(3) DELETE FROM t1 WHERE A=2 GO --将ID=3的记录的ID值改为2 SET IDENTITY_INSERT t1 ON INSERT t1(ID,A) SELECT 2,A FROM t1 WHERE ID=3 DELETE FROM t1 WHERE ID=3 SET IDENTITY_INSERT t1 OFF SELECT * FROM t1
------解决方案--------------------
从错误提示看是
"人工插入自增列",但代码看不出来,
运行profiler,跟踪一下sql
------解决方案--------------------
- SQL code
CREATE TABLE [dbo].[Qf_Log]( [id] [int] IDENTITY(1,1) NOT NULL, [Empid] [int] NOT NULL, [LogContent] [varchar](200) NOT NULL, [LogTime] [datetime] NULL CONSTRAINT [DF_Qf_Log_LogTime] DEFAULT (getdate()) ) create PROCEDURE [dbo].[Qf_LoginLog] @Empid int , @areaid int, @schoolid int, @classid int, @Content varchar(200) AS BEGIN insert into dbo.Qf_Log(Empid,LogContent,Logtime) values (@Empid,'发送给'+convert(varchar(4),@areaid)+convert(varchar(6),@schoolid)+ convert(varchar(6),@classid)+'内容:'+@Content,getdate()) end exec [dbo].[Qf_LoginLog] 5,'1111','222222','333333','abcdef' select * from [dbo].[Qf_Log]
------解决方案--------------------
你把它设置成ON试一下不就完了吗
------解决方案--------------------