January 3, 2003 at 12:10 pm
Hello,
I need to customize a SQL Server 2000 error message that occurs when a user attempts to insert a duplicate record into a table.
The code for the table into which the records are entered looks like this:
**********************************
CREATE TABLE [tblMMStudentTestScores] (
[PERMNUM] [varchar] (12) NOT NULL ,
[TestShortName] [nvarchar] (8) NOT NULL ,
[TestScore] [smallint] NULL ,
[DateEntered] [smalldatetime] NOT NULL ,
CONSTRAINT [FK_tblMMStudentTestScores_Student_Data_Main] FOREIGN KEY
(
[PERMNUM]
) REFERENCES [Student_Data_Main] (
[PERMNUM]
),
CONSTRAINT [FK_tblMMStudentTestScores_tblTests2] FOREIGN KEY
(
[TestShortName]
) REFERENCES [tblTests] (
[TestShortName]
)
) ON [PRIMARY]
**********************************
Records are kept unique in this table by a Unique, Nonclustered Index with IGNORE_DUP_KEY on fields 'PERMNUM' and 'TestShortName'.
If the user attempts an insert records and one of them happens to be a duplicate with the following example syntax in the QA,
INSERT tblMMStudentTestScores
(PERMNUM, TestShortName)
VALUES
('600', 'ABCK1Q')
QA displays the following message:
"Server: Msg 3604, Level 16, State 1, Line 1
Duplicate key was ignored."
The duplicates are ignored, and the rest of the unique records are inserted into the table.
How can I change 'Duplicate key was ignored' to be expressed as 'Duplicate records for existing students cannot be entered, but records for new students will be allowed'?
The code that will do this needs to be incorporated into the following SP:
****************************************
alter Procedure MMCreateRecords_sp
@TestShortName nvarchar (8)
AS
DECLARE @Error int
Insert tblMMStudentTestScores
(TC.Permnum,TC.TestShortName)
Select distinct
SD.Permnum,TS.TestShortName
from
Student_Data_Main SD
INNER JOIN tblGradeCodes2 GC ON GC.GRADE = SD.Grade
INNER JOIN tblTests TS ON TS.GradeCode = GC.GradeCode
INNER JOIN Teacher_Data_Main TD ON TD.TeacherID = SD.TeacherID
where TS.TestShortName = @TestShortName
and TD.systemusername = (system_user)
SELECT @Error=@@Error
IF @Error != 0
BEGIN
IF @Error = 3604
BEGIN
PRINT 'Duplicate records for existing students cannot be entered.' PRINT 'Records for new students will be allowed'
END
ELSE
BEGIN
PRINT 'An error has occured. Please call the I.T. help line at'
PRINT 'ext. 4357 and report error number ' + CONVERT(varchar, @Error)
END
RETURN @Error
END
*****************************************
As you can see, I have attempted to put something together to help me manage the error message, but it does not work. Do I need to use RAISERROR instead of PRINT? I still get:
"Server: Msg 3604, Level 16, State 1, Line 1
Duplicate key was ignored."
Thanks for your help.
CSDunn
January 6, 2003 at 8:00 am
This was removed by the editor as SPAM
January 6, 2003 at 10:53 am
If I remember right level 16 errors stop further processing of the proc and therefore I don't think you can replace the message.
In my procs I check for duplicates (SELECT ... etc) and produce error message if found and exit proc.
In answer to your ohter question, you have to use raise error to return messages to the calling app, e.g.
RAISERROR ('Duplicate records for existing students cannot be entered. Records for new students will be allowed',16,1)
Far away is close at hand in the images of elsewhere.
Anon.
January 6, 2003 at 4:59 pm
I wound up putting my error checking code in the application interface. In this case it was an Access 2000 Project.
I found out that SQL Server 2000 passes its errors back to VBA through the ADO Errors collection of the Connection object in an Access 2000 Project. The code below is probably not the best way to do what I needed, but it works like I intended. Remember, this is ADO code for an Access 2000 Project in which a button sends a parameter value to a SQL SP called 'MMCreateRecords_sp':
Private Sub Command52_Click()
Dim cmd As ADODB.Command
Dim prmInput As ADODB.Parameter
Dim err1 As ADODB.Error
On Error GoTo MM_ErrorTrap
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "dbo.MMCreateRecords_sp"
Set prmInput = cmd.CreateParameter("@TestShortName", adVarChar, adParamInput, 8, Forms!frmSelectByTest!TestShortName)
cmd.Parameters.Append prmInput
cmd.Execute , , adExecuteNoRecords
Forms!frmSelectByTest.Form!frmSelectByTestSUB.Requery
Set prmInput = Nothing
Set cmd = Nothing
MM_ErrorTrap_exit:
Forms!frmSelectByTest.Form!frmSelectByTestSUB.Requery
Set cmd = Nothing
Exit Sub
MM_ErrorTrap:
For Each err1 In cmd.ActiveConnection.Errors
Next
MsgBox "Duplicate records for existing students cannot be entered, but data for new students will entered.", vbInformation, "Duplicate Records"
Resume MM_ErrorTrap_exit
End Sub
I removed any error checking code out of the SP. The code above assumes that the message presented is appropriate for any 'err1' that is passed in, but it will work well for what I am doing.
January 6, 2003 at 5:06 pm
Typically the error trap you have presented here can leave the user confused and
scratching their heads. What if is not a duplicate record, but some FK violation
has occurred, or and check constraint? Or a data type mismatch? You always
presenting them with the same hard coded error is very bad practice IMHO. Also without the "On Error" you trap will not fire, but will cause a run time error, that may get handled by a parent error handler as error handlers stack along with the call stack.
Tim C.
//Will write code for food
Tim C //Will code for food
January 6, 2003 at 6:54 pm
I think the code is ok, but I think you need to consider if the user really cares. If Joe was already in the list and they add again, when they view the list Joe will be there and they wont care. I do agree that if you're going to put up the error, clean it up for the user.
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply