陷阱主键错误（ASP，SP和SQL2000） [英] trap primary key error (ASP, SP and SQL2000)

问题描述

我在SQL 2000中有一个表，在coulumns上有一个复合主键

Instrument_ID（int）和WeekOf（smalldatetime。）我在win 2003上运行asp.

我使用此ASP中的存储过程插入值：

InsertSQL =" Execute osp_insert_Instrument_Schedule"

InsertSQL = InsertSQL& @UserName =''" &安培; strUserName& "''，"

InsertSQL = InsertSQL& @DateInput =''" &安培;日期（）& "''，"

InsertSQL = InsertSQL& @Instrument_ID =" &安培; strInstrument_ID& "，"

InsertSQL = InsertSQL& @Group_ID =" &安培; strGroup_ID& "，"

InsertSQL = InsertSQL& @Project_Code =''" &安培; strProject_Code& "''，"

InsertSQL = InsertSQL& @Drug =''" &安培; strDrug& "''，"

InsertSQL = InsertSQL& @WeekOf =''" &安培; strWeekOf& "''，"

InsertSQL = InsertSQL& @Comments =''" &安培; strComments& "''"


设置Conn = Server.CreateObject（" ADODB.Connection"）

Conn.Open cstInstrScheduleDB

Conn.Execute（InsertSQL）

Conn.Close

设置Conn = Nothing


如何捕获错误？我想提出一些说不好的代码

用户再试一次。我在哪里捕获它？


谢谢

Mike


++++++++++++++ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++ $ + $ $ $

Microsoft OLE DB Provider for SQL Server错误''80040e2f''

违反PRIMARY KEY约束''PK_Tab_Inst_Schedules''。无法在对象''Tab_Inst_Schedules''中插入

重复键。

/ScheduleInputConfirm.asp，第99行

--- -------------------------------------------------- -------------------------------------------------- ---------------------------

存储过程：

CREATE PROCEDURE dbo。 osp_insert_Instrument_Schedule

@UserName varchar（10），

@DateInput smalldatetime，

@Instrument_ID smallint，

@Group_ID smallint，

@Project_Code varchar（10），

@Drug varchar（50），

@WeekOf varchar（300），
@Comments varchar（2000）

AS

声明@NewID int

声明@Spot Smallint

声明@str varchar（300）

开始

设置NoCount

插入Tab_Instr_Sched_Details（Scheduler，ScheduledOn，Instrument_ID） ，

Group_ID，Project_Code，Drug）值（@ UserName，@ DateInput，@ Instrument_ID，

@Group_ ID，@ Project_Code，@ Drug）


SELECT @NewID = SCOPE_IDENTITY（）


插入Tab_Instr_Sched_Comments（Schedule_ID，注释）值（@ NewID，

@Comments）


而@Weekof<> ''''

开始

设置@Spot = CharIndex（''|''，@ WeekOf）

如果@Spot> 0

开始

设置@str =左（@ WeekOf，@ Spot-1）

设置@WeekOf =右（@ WeekOf，Len（@） WeekOf） - @Spot）

结束

否则

开始

设置@str = @WeekOf

设置@WeekOf =''''

结束

插入Tab_Inst_Schedules（Schedule_ID，Instrument_ID，WeekOf）值

（ @NewID，@ Instrument_ID，@ str）

结束

结束

设定NOCOUNT OFF

GO

I have a table in SQL 2000 with a composite Primary Key on coulumns
Instrument_ID (int) and WeekOf (smalldatetime.) I am running asp on win 2003.

I insert values using a stored procedure from this ASP:
InsertSQL = "Execute osp_insert_Instrument_Schedule "
InsertSQL = InsertSQL & "@UserName = ''" & strUserName & "'', "
InsertSQL = InsertSQL & "@DateInput = ''" & Date() & "'', "
InsertSQL = InsertSQL & "@Instrument_ID = " & strInstrument_ID & ", "
InsertSQL = InsertSQL & "@Group_ID = " & strGroup_ID & ", "
InsertSQL = InsertSQL & "@Project_Code = ''" & strProject_Code & "'', "
InsertSQL = InsertSQL & "@Drug = ''" & strDrug & "'', "
InsertSQL = InsertSQL & "@WeekOf = ''" & strWeekOf & "'', "
InsertSQL = InsertSQL & "@Comments = ''" & strComments & "''"

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open cstInstrScheduleDB
Conn.Execute(InsertSQL)
Conn.Close
Set Conn = Nothing

How do I trap the error? I would like to present some code that says bad
user try again. Where do I trap it?

Thanks
Mike

++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++
Error:
Microsoft OLE DB Provider for SQL Server error ''80040e2f''
Violation of PRIMARY KEY constraint ''PK_Tab_Inst_Schedules''. Cannot insert
duplicate key in object ''Tab_Inst_Schedules''.
/ScheduleInputConfirm.asp, line 99

----------------------------------------------------------------------------------------------------------------------------------
Stored procedure:
CREATE PROCEDURE dbo.osp_insert_Instrument_Schedule
@UserName varchar(10),
@DateInput smalldatetime,
@Instrument_ID smallint,
@Group_ID smallint,
@Project_Code varchar(10),
@Drug varchar(50),
@WeekOf varchar(300),
@Comments varchar(2000)
AS
Declare @NewID int
Declare @Spot Smallint
Declare @str varchar(300)

Begin
Set NoCount On
Insert Into Tab_Instr_Sched_Details (Scheduler, ScheduledOn, Instrument_ID,
Group_ID, Project_Code, Drug) Values (@UserName, @DateInput, @Instrument_ID,
@Group_ID, @Project_Code, @Drug)

SELECT @NewID = SCOPE_IDENTITY()

Insert Into Tab_Instr_Sched_Comments (Schedule_ID, Comments) Values (@NewID,
@Comments)

While @Weekof <> ''''
Begin
Set @Spot = CharIndex(''|'', @WeekOf)
If @Spot>0
Begin
Set @str = Left(@WeekOf, @Spot-1)
Set @WeekOf = Right(@WeekOf, Len(@WeekOf)-@Spot)
End
Else
Begin
Set @str = @WeekOf
Set @WeekOf = ''''
End
Insert into Tab_Inst_Schedules (Schedule_ID, Instrument_ID, WeekOf) Values
(@NewID, @Instrument_ID, @str)
End
END
SET NOCOUNT OFF
GO

推荐答案

Mike D写道：

我在SQL 2000中有一个表，在coulumns上有一个复合主键
Instrument_ID（ int）和WeekOf（smalldatetime。）我在win 2003上运行asp.

我使用此ASP中的存储过程插入值：
InsertSQL =" Execute osp_insert_Instrument_Schedule" ;
InsertSQL = InsertSQL& @UserName =''" &安培; strUserName& "'''，"
InsertSQL = InsertSQL& @DateInput =''" &安培;日期（）& "'''，"
InsertSQL = InsertSQL& @Instrument_ID =" &安培; strInstrument_ID& "，"
InsertSQL = InsertSQL& @Group_ID =" &安培; strGroup_ID& "，"
InsertSQL = InsertSQL& @Project_Code =''" &安培; strProject_Code& "'''，"
InsertSQL = InsertSQL& @Drug =''" &安培; strDrug& "'''，"
InsertSQL = InsertSQL& @WeekOf =''" &安培; strWeekOf& "'''，"
InsertSQL = InsertSQL& @Comments =''" &安培; strComments& ""


唉！动态SQL运行存储过程...为什么不使用参数？请参阅
http://tinyurl.com/jyy0
Conn.Open cstInstrScheduleDB
Conn.Execute（InsertSQL）
如何捕获错误？我想提出一些代码，说坏用户再试一次。我在哪里捕获它？

I have a table in SQL 2000 with a composite Primary Key on coulumns
Instrument_ID (int) and WeekOf (smalldatetime.) I am running asp on
win 2003.

I insert values using a stored procedure from this ASP:
InsertSQL = "Execute osp_insert_Instrument_Schedule "
InsertSQL = InsertSQL & "@UserName = ''" & strUserName & "'', "
InsertSQL = InsertSQL & "@DateInput = ''" & Date() & "'', "
InsertSQL = InsertSQL & "@Instrument_ID = " & strInstrument_ID & ", "
InsertSQL = InsertSQL & "@Group_ID = " & strGroup_ID & ", "
InsertSQL = InsertSQL & "@Project_Code = ''" & strProject_Code & "'', "
InsertSQL = InsertSQL & "@Drug = ''" & strDrug & "'', "
InsertSQL = InsertSQL & "@WeekOf = ''" & strWeekOf & "'', "
InsertSQL = InsertSQL & "@Comments = ''" & strComments & "''"
Ugh! Dynamic SQL to run a stored procedure ... Why not use parameters? See
http://tinyurl.com/jyy0


Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open cstInstrScheduleDB
Conn.Execute(InsertSQL) How do I trap the error? I would like to present some code that says
bad user try again. Where do I trap it?

无论你想在哪里捕获它。


你可以在这里陷阱：

下次错误恢复


Conn.Execute InsertSQL ,, 1

if err<> 0然后

''处理错误

''你可以查看连接的错误集合了解详情，例如

''本机sql server错误号


或者你可以在程序中捕获它。这取决于你...


Bob Barrows


-

Microsoft MVP - ASP / ASP.NET

请回复新闻组。我的From

标题中列出的电子邮件帐户是我的垃圾邮件陷阱，因此我不经常检查它。通过发布到新闻组，您将获得更快的回复。

Wherever you want to trap it.

You could trap it here:

on error resume next
Conn.Execute InsertSQL,,1
if err <> 0 then
''handle the error
''you can check the connection''s Errors collection for details such as
''the native sql server error number

Or you can trap it in the procedure. It''s up to you...

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don''t check it very often. You will get a
quicker response by posting to the newsgroup.

" Bob巴罗斯[MVP]"写道：

"Bob Barrows [MVP]" wrote:

Mike D写道：

我在SQL 2000中有一个表，在coulumns上有一个复合主键
Instrument_ID（int）和WeekOf （smalldatetime。）我在win 2003上运行asp.

我使用此ASP中的存储过程插入值：
InsertSQL =" Execute osp_insert_Instrument_Schedule"
InsertSQL = InsertSQL& @UserName =''" &安培; strUserName& "'''，"
InsertSQL = InsertSQL& @DateInput =''" &安培;日期（）& "'''，"
InsertSQL = InsertSQL& @Instrument_ID =" &安培; strInstrument_ID& "，"
InsertSQL = InsertSQL& @Group_ID =" &安培; strGroup_ID& "，"
InsertSQL = InsertSQL& @Project_Code =''" &安培; strProject_Code& "'''，"
InsertSQL = InsertSQL& @Drug =''" &安培; strDrug& "'''，"
InsertSQL = InsertSQL& @WeekOf =''" &安培; strWeekOf& "'''，"
InsertSQL = InsertSQL& @Comments =''" &安培; strComments& "''"

I have a table in SQL 2000 with a composite Primary Key on coulumns
Instrument_ID (int) and WeekOf (smalldatetime.) I am running asp on
win 2003.

I insert values using a stored procedure from this ASP:
InsertSQL = "Execute osp_insert_Instrument_Schedule "
InsertSQL = InsertSQL & "@UserName = ''" & strUserName & "'', "
InsertSQL = InsertSQL & "@DateInput = ''" & Date() & "'', "
InsertSQL = InsertSQL & "@Instrument_ID = " & strInstrument_ID & ", "
InsertSQL = InsertSQL & "@Group_ID = " & strGroup_ID & ", "
InsertSQL = InsertSQL & "@Project_Code = ''" & strProject_Code & "'', "
InsertSQL = InsertSQL & "@Drug = ''" & strDrug & "'', "
InsertSQL = InsertSQL & "@WeekOf = ''" & strWeekOf & "'', "
InsertSQL = InsertSQL & "@Comments = ''" & strComments & "''"

唉！动态SQL运行存储过程...为什么不使用参数？请参阅
http://tinyurl.com/jyy0

Ugh! Dynamic SQL to run a stored procedure ... Why not use parameters? See
http://tinyurl.com/jyy0

设置Conn = Server.CreateObject（" ADODB.Connection"）
Conn.Open cstInstrScheduleDB
Conn.Execute（InsertSQL）

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open cstInstrScheduleDB
Conn.Execute(InsertSQL)

如何捕获错误？我想提出一些代码，说坏用户再试一次。我在哪里捕获它？

How do I trap the error? I would like to present some code that says
bad user try again. Where do I trap it?

你想把它捕获到哪里。

你可以把它陷阱：

错误恢复下一页
Conn.Execute InsertSQL ,, 1
如果错误<> 0然后
''处理错误
''你可以查看连接的错误集合了解详细信息，例如
''本机sql server错误号
或者你可以在程序中捕获它。这取决于你......

鲍勃巴罗斯

-
微软MVP - ASP / ASP.NET
请回复新闻组。我的From
标题中列出的电子邮件帐户是我的垃圾邮件陷阱，因此我不经常检查它。通过发布到新闻组，您将获得更快的回复。

Wherever you want to trap it.

You could trap it here:

on error resume next
Conn.Execute InsertSQL,,1
if err <> 0 then
''handle the error
''you can check the connection''s Errors collection for details such as
''the native sql server error number

Or you can trap it in the procedure. It''s up to you...

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don''t check it very often. You will get a
quicker response by posting to the newsgroup.

感谢重播。我有错误现在被困在asp但是我没有说明事务在我的sp中回滚，当它在Tab_Inst_Schedules表中找到了重复的时候。

Tab_Inst_Schedules表。 />

我会阅读您发布的链接，但我一直讨厌使用

命令对象和参数。我很好奇它是否会加快我的代码。


迈克


这是现在的sp：

CREATE PROCEDURE dbo.osp_insert_Instrument_Schedule


@UserName varchar（10），

@DateInput smalldatetime，

@Instrument_ID smallint ，

@Group_ID smallint，

@Project_Code varchar（10），

@Drug varchar（50），

@WeekOf varchar（300），

@Comments varchar（2000）


AS

声明@NewID int

声明@Spot Smallint

声明@str varchar（300）

BEGIN TRANSACTION

开始

设置NoCount


插入Tab_Instr_Sched_Details（调度程序，ScheduledOn，Instrument_ID，

Group_ID，Project_Code，Drug）值（ @UserName，@ DateInput，@ Instrument_ID，

@Group_ID，@ Project_Code，@ Drug）


SELECT @NewID = SCOPE_IDENTITY（）


插入Tab_Instr_Sched_Comments（Sch edule_ID，评论）价值观（@NewID，

@Comments）



而@Weekof<> ''''

开始

设置@Spot = CharIndex（''|''，@ WeekOf）

如果@Spot> 0

开始

设置@str =左（@ WeekOf，@ Spot-1）

设置@WeekOf =右（@ WeekOf，Len（@） WeekOf） - @Spot）

结束

否则

开始

设置@str = @WeekOf

设置@WeekOf =''''

结束


插入Tab_Inst_Schedules（Schedule_ID，Instrument_ID，WeekOf）值

（@NewID，@ Instrument_ID，@ str）


结束


结束

IF @@错误！= 0

BEGIN

ROLLBACK交易

RAISERROR（''这里有错误。''，11,1）

返回

结束

ELSE

COMMIT TRANSACTION

设置NOCOUNT OFF

GO

Thanks for the replay. I have got the error now being trapped in asp but I
am not geetting the transaction to rollback in my sp when it finds the dup in
the Tab_Inst_Schedules table.

I will read the link you posted but I have always hated working with the
command object and parameters. I am curious if it will speed my code up.

Mike

Here is the sp as it is now:
CREATE PROCEDURE dbo.osp_insert_Instrument_Schedule

@UserName varchar(10),
@DateInput smalldatetime,
@Instrument_ID smallint,
@Group_ID smallint,
@Project_Code varchar(10),
@Drug varchar(50),
@WeekOf varchar(300),
@Comments varchar(2000)

AS
Declare @NewID int
Declare @Spot Smallint
Declare @str varchar(300)

BEGIN TRANSACTION
Begin
Set NoCount On


Insert Into Tab_Instr_Sched_Details (Scheduler, ScheduledOn, Instrument_ID,
Group_ID, Project_Code, Drug) Values (@UserName, @DateInput, @Instrument_ID,
@Group_ID, @Project_Code, @Drug)

SELECT @NewID = SCOPE_IDENTITY()

Insert Into Tab_Instr_Sched_Comments (Schedule_ID, Comments) Values (@NewID,
@Comments)


While @Weekof <> ''''
Begin
Set @Spot = CharIndex(''|'', @WeekOf)
If @Spot>0
Begin
Set @str = Left(@WeekOf, @Spot-1)
Set @WeekOf = Right(@WeekOf, Len(@WeekOf)-@Spot)
End
Else
Begin
Set @str = @WeekOf
Set @WeekOf = ''''
End

Insert into Tab_Inst_Schedules (Schedule_ID, Instrument_ID, WeekOf) Values
(@NewID, @Instrument_ID, @str)

End

END
IF @@ERROR != 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR(''There was an error here.'', 11, 1)
RETURN
END
ELSE
COMMIT TRANSACTION
SET NOCOUNT OFF
GO

Mike D写道：

感谢您的重播。我的错误现在被困在了asp
但我并没有在我的sp中回滚事务，当它在Tab_Inst_Schedules表中找到dup时。

Thanks for the replay. I have got the error now being trapped in asp
but I am not geetting the transaction to rollback in my sp when it
finds the dup in the Tab_Inst_Schedules table.

为了实现这一点，您需要使用名为@@ ERROR的全局变量将错误捕获到存储的

过程中。请参阅下文。

我将阅读您发布的链接，但我一直讨厌使用
命令对象和参数。


阅读。您将看到我不建议使用显式的Command

对象，除非您的过程有输出参数或者您感兴趣

在RETURN语句返回的值中在你的程序中。


我还包含一个工具的链接，可以很容易地生成命令

目标代码，如果你确实需要使用一个显式的命令对象。

我很好奇它是否会加速我的
代码。


它应该，但你会注意到速度的增加是值得怀疑的。

使用参数帮助消除了

创建动态sql所固有的所有问题：


分隔符

SQL注入

必须转义通常是分隔符的字符但

你需要将其视为文字

等。

这是现在的sp：
CREATE PROCEDURE dbo.osp_insert_Instrument_Schedule
< snip>声明@str varchar（300）


DECLARE @err int
BEGIN TRANSACTION
< snip>插入Tab_Inst_Schedules（Schedule_ID，Instrument_ID，WeekOf）
值（@NewID，@ Instrument_ID，@ str）


SET @err = @@ ERROR

IF @err！= 0

- 等等。

结束

结束



以下语句不起作用，因为自生成错误的语句以来，干预语句已发生
。你需要检查

是否在紧跟可能引起错误的声明后的行上出现错误。

IF @@ ERROR！= 0
BEGIN
ROLLBACK交易
RAISERROR（''这里有错误。''，11,1）
返回
END
ELSE
COMMIT TRANSACTION
SET NOCOUNT OFF
GO

In order for that to happen, you need to trap the error in your stored
procedure by using the global variable called @@ERROR. See below.

I will read the link you posted but I have always hated working with
the command object and parameters.
Read it. You will see that I do not recommend using an explicit Command
object unless your procedure has output parameters or if you''re interested
in the value returned by a RETURN statement in your procedure.

I also include a link to a tool that makes it easy to generate the command
object code if you do need to use an explicit command object.
I am curious if it will speed my
code up.
It should, but it''s doubtful you will notice the increase in speed. Where
using parameters help is in eliminating all the problems inherent in
creating dynamic sql:

delimiters
SQL Injection
the necessity to escape characters that would normally be delimiters but
which you need to be treated as literals
etc.

Here is the sp as it is now:
CREATE PROCEDURE dbo.osp_insert_Instrument_Schedule <snip> Declare @str varchar(300)
DECLARE @err int
BEGIN TRANSACTION <snip> Insert into Tab_Inst_Schedules (Schedule_ID, Instrument_ID, WeekOf)
Values (@NewID, @Instrument_ID, @str)
SET @err = @@ERROR
IF @err !=0
--etc.
End

END


The following statement is not working because intervening statements have
occurred since the statement that generated the error. You need to check for
an error on the line immediately following the statement that potentially
raised the error.
IF @@ERROR != 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR(''There was an error here.'', 11, 1)
RETURN
END
ELSE
COMMIT TRANSACTION
SET NOCOUNT OFF
GO

Bob Barrows

-

Microsoft MVP - ASP / ASP.NET

请回复新闻组。我的From

标题中列出的电子邮件帐户是我的垃圾邮件陷阱，因此我不经常检查它。通过发布到新闻组，您将获得更快的回复。

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don''t check it very often. You will get a
quicker response by posting to the newsgroup.

这篇关于陷阱主键错误（ASP，SP和SQL2000）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！