We help IT Professionals succeed at work.

Can someone please look at this and tell me what I need to fix to prevent this '457' run time error?

Nancy Pursel
Nancy Pursel asked
on
PLEASE HELP ME. No one in IT for my company can help me figure out why I am getting this error.
This is the code in the file of the database I am trying to run, everything else in the application works just fine,
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[db_RoundsOrderSeq]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[db_RoundsOrderSeq]
GO

CREATE Procedure [dbo].[db_RoundsOrderSeq]
      @Action char(3),
      @HospitalCode char(1) = '',
      @RoundsKey varchar(8) = '',
      @Sequence smallint = 0,
      @Location varchar(10) = '',
      @Room varchar(8) = '',
      @Bed varchar(4) = '',
      @ReturnErrorDescription varchar (200) = '' OUTPUT

AS
BEGIN
-- db_RoundsOrderSeq: Insert, Update, Delete
-- (C) Copyright 2013 Universal Hospital Services, Inc.
-- Created by SMH 04/24/2013
-- Version 8.00
-- Changed by SMH 02/27/2014 Right Justify Room and Bed
-- Version 8.01

-- Return Version only
If @Action = 'VER'
   BEGIN
   Select Version = '8.01'
   RETURN
   END

Set @ReturnErrorDescription = ''
DECLARE
      @ROS_RoundsKey varchar(8)
      
-- Right Justify Room and Bed (already done in table class)
SET @Room = RIGHT('        ' + RTRIM(@Room), 8)
SET @Bed = RIGHT('    ' + RTRIM(@Bed), 4)
 
-- Import Record
IF @Action = 'IMP'
      BEGIN
      Set @ROS_RoundsKey = ''
      Select @ROS_RoundsKey = RoundsKey
            From RoundsOrderSeq
            Where HospitalCode = @HospitalCode And RoundsKey = @RoundsKey And Sequence = @Sequence
      IF @ROS_RoundsKey = ''
            Set @Action = 'INS'
      ELSE
            Set @Action = 'UPD'
      
      END
      
-- Insert record
If @Action = 'INS'
   BEGIN
   INSERT INTO RoundsOrderSeq (
      HospitalCode,
      RoundsKey,
      Sequence,
      Location,
      Room,
      Bed
      )
   VALUES(
      @HospitalCode,
      @RoundsKey,
      @Sequence,
      @Location,
      @Room,
      @Bed
      )
   RETURN @@ERROR
   END

-- Update record
If @Action = 'UPD'
   BEGIN
   UPDATE RoundsOrderSeq SET
      Location = @Location,
      Room = @Room,
      Bed = @Bed
   Where HospitalCode = @HospitalCode And RoundsKey = @RoundsKey
      And Sequence = @Sequence
   
 
   END

-- Delete record
If @Action = 'DLT'
   BEGIN
Incare-Error-Rounds.PNG
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Looks to be duplicating a 'KEY'.
Must be calling the insert on the above.

Can you show the application code?
CERTIFIED EXPERT

Commented:
If this has been working all along and no changes have been made then I would be looking at user error here.
Are you sure the SAVE should give a different result?
lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
By looking at the code above it looks to me that

[...]
-- Insert record
If @Action = 'INS'
   BEGIN
[...]

can be executed via the SP IF the @Action parameter is passes = 'INS' so in my opinion the SP must be fixed to include the same check for
[...]
-- Insert record
If @Action = 'INS'
   BEGIN
[...]

as it is performed under
[...]
-- Import Record
IF @Action = 'IMP'
[...]

And I believe that can be done easily if you add the 'INS' to the above check like:

[...]
-- Import Record
IF @Action = 'IMP' OR @Action = 'INS'
      BEGIN
      --check IF the record exists first
      Set @ROS_RoundsKey = ''
      Select @ROS_RoundsKey = RoundsKey
            From RoundsOrderSeq
            Where HospitalCode = @HospitalCode And RoundsKey = @RoundsKey And Sequence = @Sequence
      IF @ROS_RoundsKey = ''
            Set @Action = 'INS'
      ELSE
            Set @Action = 'UPD'
      END
[...]

Open in new window

Explore More ContentExplore courses, solutions, and other research materials related to this topic.