trap primary key error (ASP, SP and SQL2000)

[Mike D]

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

 

[Bob Barrows [MVP]]

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?

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

 

[Mike D]

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




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.

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

 

[Bob Barrows [MVP]]

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.

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

Declare @str varchar(300)

DECLARE @err int

BEGIN TRANSACTION

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

 

[Mike D]

Thanks for all of your help but my sp still isn't working. I know I am
missing something.

My sp works with 1 insert into Tab_Inst_Schedules. If I have multiple then
I get a The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION
error.

Here is the 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
Declare @NewID int
Declare @Spot Smallint
Declare @str varchar(300)

Declare @err int

Set NoCount On

BEGIN TRANSACTION



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)
Set @err = @@error
If @err != 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('There was an error here.', 11, 1)
RETURN
END
ELSE
COMMIT TRANSACTION

END
GO


Thanks
Mike

 

[Bob Barrows [MVP]]

Thanks for all of your help but my sp still isn't working. I know I
am missing something.

My sp works with 1 insert into Tab_Inst_Schedules. If I have
multiple then I get a The COMMIT TRANSACTION request has no
corresponding BEGIN TRANSACTION error.

Here is the sp
CREATE PROCEDURE dbo.osp_insert_Instrument_Schedule

Declare @err int

DECLARE @trancount int
SET @trancount = @@TRANCOUNT

If @err != 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('There was an error here.', 11, 1)
RETURN
END
ELSE

No. Don't commit it here inside the loop!

COMMIT TRANSACTION
END
END

Commit it here after the loop is finished!

IF @@TRANCOUNT > @trancount
COMMIT TRANSACTION

Bob Barrows

 

[Mike D]

I still don't have it working. I have been reading about fatal and non-fatal
errors. If this is a fatal error can a rollback be performed and if it isn't
why can't I get it to rollback after a primary key constraint error??

Is this how most developers would approach this or should I be querying for
something before I make any inserts. This is frustrating as hell!!

Mike


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
Declare @NewID int
Declare @Spot Smallint
Declare @str varchar(300)

Declare @err int

Set NoCount On

BEGIN TRANSACTION

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

Set @err = @@error
If @err <> 0
Begin
ROLLBACK TRANSACTION
Print 'Error Occured'
End
ELSE
COMMIT TRANSACTION
GO

 

[Bob Barrows [MVP]]

I still don't have it working. I have been reading about fatal and
non-fatal errors. If this is a fatal error can a rollback be
performed and if it isn't why can't I get it to rollback after a
primary key constraint error??

Is this how most developers would approach this or should I be
querying for something before I make any inserts. This is
frustrating as hell!!

I have several stored procedures that use this technique. You really should
use the @trancount variable I illustrated in my previous message. I'll show
it to you again.

Mike


SP:
CREATE PROCEDURE dbo.osp_insert_Instrument_Schedule

<snip>
DECLARE @trancount int
SET @trancount = @@TRANCOUNT
BEGIN TRANSACTION

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

END

Set @err = @@error

Sigh! I said two messages ago that you have to check for an error on the
line immediately following the statement that potentially raised the error.
You had that part correct! Why did you undo that good work? The END
statement clears any errors, so @@ERROR will always contain 0 at this point.

If @err <> 0
Begin
ROLLBACK TRANSACTION
Print 'Error Occured'
End
ELSE
COMMIT TRANSACTION
GO

The ELSE part is where you are having the problem! Get rid of it. Go back to
doing this:


Insert into Tab_Inst_Schedules (Schedule_ID, Instrument_ID, WeekOf)
Values (@NewID, @Instrument_ID, @str)
Set @err = @@error
If @err != 0
BEGIN
IF @@TRANCOUNT > @trancount
ROLLBACK TRANSACTION
RAISERROR('There was an error here.', 11, 1)
RETURN
END
END
--OUTSIDE OF THE LOOP, COMMIT THE TRANSACTION
--If your code makes it to this point, no errors occurred, right?
IF @@TRANCOUNT > @trancount
COMMIT TRANSACTION

Bob Barrows

 

[Bob Barrows [MVP]]

I still don't have it working. I have been reading about fatal and
non-fatal errors. If this is a fatal error can a rollback be
performed and if it isn't why can't I get it to rollback after a
primary key constraint error??

Is this how most developers would approach this or should I be
querying for something before I make any inserts. This is
frustrating as hell!!

Actually, it might make more sense to query for the existing record before
attempting to insert. Many developers frown on using errors to control
program flow. You will probably be better off doing this:

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
IF NOT EXISTS (SELECT * FROM Tab_Inst_Schedules WHERE
Instrument_ID = @Instrument_ID AND WeekOf = @str)
Insert into Tab_Inst_Schedules (Schedule_ID,
Instrument_ID, WeekOf)
Values (@NewID, @Instrument_ID, @str)
ELSE
BEGIN
IF @@TRANCOUNT > @trancount
ROLLBACK TRANSACTION
RAISERROR('This record already exists.', 11, 1)
RETURN
END
End
IF @@TRANCOUNT > @trancount
COMMIT TRANSACTION


HTH,
Bob Barrows

 

[Mike D]

That's it. Thank you very much for your patience. I've got a lot to learn
about errors in stored procedures!

Mike

 

[Mike D]

This link was posted for me yesterday by Bob Barrows maybe it will help you.
http://tinyurl.com/jyy0

Mike