• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

Set Error Statement for Users

We have the below mentioned index on our table tankers, which is working great. However we need to set our own error for users to better understand problem

Error statement would be like this:
Sorry the record could be updated because the same record exist into the database against trailerno, loadon & Loadby

My Unique index coding as below:

USE [OilDB]
GO
/****** Object:  Index [IDX_Tankers_unq]    Script Date: 12/24/2009 13:15:59 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IDX_Tankers_unq] ON [dbo].[tankers]

([TrailerNo] ASC,
[LoadOn] ASC,
[Lb] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

0
Mehram
Asked:
Mehram
  • 6
  • 5
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
While Inserting records into your Table, instead of your default INSERT statement it should be like this

BEGIN TRY
       INSERT INTO ur_table ....
END TRY
BEGIN CATCH
       RAISEERROR(50001, 10, 10, N'Sorry the record could be updated because the same record exist into the database against trailerno, loadon & Loadby');
END CATCH
0
 
MehramAuthor Commented:
Sir, I just try to make in statement, Please guide

CREATE TRIGGER trg_Check_Dublicate_Permit ON  Tankers(Insert,Update) AS
BEGIN TRY
       Select TrailerNo, LoadOn, LB from Tankers Group by TrailerNo, LoadOn, LB  Having Count(1) >1
END TRY
BEGIN CATCH
       RAISEERROR(50001, 10, 10, N 'Sorry the record could be updated because the same record exist into the database against trailerno, loadon & Loadby');
END CATCH
0
 
spikellyCommented:
You can do this using the RAISERROR command in sql. Either you will raise that error in a procedure or trigger, or you will add it in sql server errors and just call that error when it occurs on the system.

To add to the sql server do this:
EXEC sp_addmessage @msgnum=50001,@severity=10,
@msgtext='Sorry the record could be updated because the same record exist into the database against trailerno, loadon & Loadby'
Then when the error occurs, call it like this: RAISERROR ( 50001,10,1 )

You can do the same without adding the error to the sql server errors; put the following in your code where users are supposed to received the error:
RAISERROR ('Sorry the record could be updated because the same record exist into the database against trailerno, loadon & Loadby'
 ,10,1 )

Hope this will help.
Regards
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
MehramAuthor Commented:
Actually I just created the index to avoid dublication, now I need to display error if any to users.
To do this i am going to create Triger mentioned above.

Please guid how could I would do that?
0
 
MehramAuthor Commented:
For Example We need to Display error on the following statement


Declare @Path Varchar(50)
Set @PAth='C:\DataInsertFile.xls'

Begin Try
IF object_id('Tempp') IS NOT NULL DROP TABLE Tempp
EXEC('SELECT * Into Tempp FROM OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source=' + @Path + '; Extended Properties=''''Excel 12.0'''''')...[Sheet1$]')
--EXEC('SELECT * FROM Tempp')
Insert Into Tankers(ComCode,FYear, SU,PermitNo,Receiver,Product,unitvalue,ExchangeRate,LB,LP,via,LF,DP,Trp,invoiceno, SupInvoiceNo,DD250No,orderno,trailerno,
qty,qty15c, loadon,tripno,docno,sealno,drivername,idcard,licenseno,location)
--'Select * from Tempp

select      CC='0'+ convert(varchar(1), a.CC), J.WorkingYEar, b.transno,a.permitno,i.transno,c.transno,a.perunit,a.Er,h.transno,e.transno,f.transno,g.transno,g.transno,d.transno,
            a.invoiceno,a.RefinvNo,a.DD250No,a.OrderNo,a.TLNo,a.DespQty,A.Qtyat15c,a.despdate,a.tripno,a.docno,a.sealno,
            a.driverName,a.IDCardNo,A.LicenseNo,a.Location
from Tempp a
join mtSup b on a.supplier=b.su
Join mtPrd c on a.product=c.product
Join mtTrp d on a.transporter=d.Trp
Join mtLP e on a.lp=loadingpoint
Join mtVia f on a.via=f.via
Join mtDP g on a.dp=g.dischargingpoint
Join mtLB h on a.LC=h.Loadby
Join mtCnCode i on a.Receivers=i.receiver
Join mtYear j on a.despdate >= j.Startingdate and a.despdate <=j.EndingDate

--Join mtContractor j on A.Contractor=j.Contractor
--Exec FuelTableUpdate '\\192.168.0.10\Fuel\DatainsertFile.xls'
--Select * from Tempp
--select * from mtCnCode
End Try

BEGIN CATCH
       RAISEERROR(50001, 10, 10, N 'Sorry the record could be updated because the same record exist into the database against trailerno, loadon & Loadby');
END CATCH
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Using Trigger, you can't catch this error..
You can catch this error only if you a procedure to replace your existing INSRT / UPDATE statements..

Kindly confirm whether this is possilbe in your environment so that if not let's think about an alternate solution..
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Missed up several comments while typing this one..
I hope your objective is to inform the user that they are inserting duplicate records or not..

If yes, the easier workaround would be to

* Before Inserting records into your table, check whether those records are already present in your table or not. If yes, then send an error message at that time itself and this would be efficient compared to creating index, catching error message from it and displaying the error message..
0
 
MehramAuthor Commented:
SIR, it is possible.
But i haven't idea to implement. Just created triger pasted above. Please guide.
0
 
MehramAuthor Commented:
Triger
CREATE TRIGGER trg_Check_Dublicate_Permit ON  Tankers For Insert,Update AS
BEGIN TRY
       Select TrailerNo, LoadOn, LB from Tankers Group by TrailerNo, LoadOn, LB  Having Count(1) >1
END TRY
BEGIN CATCH
       RAISEERROR(50001, 10, 10, N 'Sorry the record could be updated because the same record exist into the database against trailerno, loadon & Loadby');
END CATCH

Error:
Msg 102, Level 15, State 1, Procedure trg_Check_Dublicate_Permit, Line 6
Incorrect syntax near 'RAISEERROR'.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Ok..Let me give you the steps in detail about how to achieve it:

Problem Scenario:

1. First user INSERT's record into table.
2. Second user Tries to INSERT the same records again.

Workaround:

1. Before inserting check whether the records are present in the table earlier by doing

SELECT count(*) from TANKERS
where TrailerNo = ?
AND LoadOn = ?
and Lb = >

2. If it returns 1, then throw an error message to the user stating that the record already exists with the message which you mentioned.
3. If not, then insert the record successfully into the table.

Using this way, we can ideally prevent the problem at the application level itself..
0
 
MehramAuthor Commented:
Almost it is same,
Only change:
User enter data in Bulk as I showed you in my last question and we need to rollback if any one matched with the existing record and raise





0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Any way we can identify few among the bulk records earlier so that we can apply some logic to it..

If we are not able to identify it, then its hard to give warning message as you requested.
And validation is also complex if we don't know what set of records it might repeat in your table..
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now