Tech
Forums
Jobs
Books
Events
Videos
Live
More
Interviews
Certification
Training
Career
Members
News
Blogs
Contribute
Article
Blog
Video
Ebook
Interview Question
.NET
.NET Assemblies
.NET Core
.NET Standard
Active Directory
ADO.NET
Agile Development
AJAX
Alexa Skills
Algorithms in C#
Android
Angular
Architecture
ArcObject
Artificial Intelligence
ASP.NET
ASP.NET Core
Augmented Reality
Aurelia
AWS
Azure
Backbonejs
Big Data
BizTalk Server
Blockchain
Bootstrap
Bot Framework
Business
C#
C# Corner
C, C++, MFC
Career Advice
Chapters
CIO
Cloud
COBOL.NET
Coding Best Practices
Cognitive Services
COM Interop
Compact Framework
Cortana Development
Cryptocurrency
Cryptography
Crystal Reports
Current Affairs
Custom Controls
Cyber Security
Data Mining
Databases & DBA
Design Patterns & Practices
DevOps
DirectX
Dynamics CRM
Enterprise Development
Entity Framework
Error Zone
Exception Handling
Expression Studio
F#
Files, Directory, IO
Games Programming
GDI+
General
Google Cloud
Google Development
Graphics Design
Hardware
Hiring and Recruitment
HoloLens
How do I
HTML 5
Internet & Web
Internet of Things
Ionic
iOS
Java
Java and .NET
JavaScript
JQuery
JSON
JSP
Knockout
kotlin
Leadership
Learn .NET
LightSwitch
LINQ
Machine Learning
Microsoft 365
Microsoft Office
Microsoft Phone
Mobile Development
Multithreading
NetBeans
Networking
Node.js
Office Development
OOP/OOD
Open Source
Operating Systems
Oracle
Outsourcing
Philosophy
PHP
Power BI
Printing in C#
Products
Progressive Web Apps
Project Management
Python
Q#
QlikView
R
React
Reports using C#
Robotics & Hardware
Ruby on Rails
Salesforce
Security
Servers
SharePoint
SignalR
Silverlight
Smart Devices
Software Testing
SQL Language
SQL Server
Startups
String in C#
Swift
TypeScript
Unity
UWP
Visual Basic .NET
Visual Studio
WCF
Wearables
Web Development
Web Services
Web3
Windows 10
Windows Controls
Windows Forms
Windows PowerShell
Windows Services
Workflow Foundation
WPF
Xamarin
XAML Standard
XML
XNA
XSharp
Register
Login
9
Answers
How to prevent insert duplicate in SQL Server
Hold On
7y
345
1
Reply
I have a stored procedure to insert data into 2 tables. I uses begin and commit transaction. But my if statement before the 2nd insert is not working. Now I have a duplicate entry not only on the first table but as well as on the 2nd table. Below is my stored procedure and hopefully someone can assist me.
USE [DhipayaHQDB_Test]
GO
/****** Object: StoredProcedure [dbo].[spInsertMotorInsuranceShortTerm_transaction] Script
Date
: 8/9/2017 4:19:46 PM ******/
SET
ANSI_NULLS
ON
GO
SET
QUOTED_IDENTIFIER
ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,
-- Description: <Description,,
-- =============================================
ALTER
PROCEDURE
[dbo].[spInsertMotorInsuranceShortTerm_transaction]
-- Add the parameters for the stored procedure here
(
@MotorInsuranceID
int
,
@RefNumber nvarchar(15),
@IssuanceType nvarchar(50),
@CustomerNo nvarchar(50),
@VehicleType nvarchar(50),
@VehicleBrand nvarchar(50),
@VehiclePlateNo nvarchar(50),
@VehicleEngineNo nvarchar(50),
@VehicleChasisNo nvarchar(50),
@VehiclePower nvarchar(50),
@InsuranceType nvarchar(50),
@InsuranceOption nvarchar(50),
@PeriodFrom
date
,
@PeriodTo
date
,
@ApplicationTime nvarchar(50),
@DateIssued
date
,
@NetPremium
decimal
(18,2),
@RegistrationFee
decimal
(18,2),
@VAT
decimal
(18,2),
@TotalPremium
decimal
(18,2),
@BranchCode
varchar
(50),
@AgentID nvarchar(50),
@Username
varchar
(50),
--@TransactionNumber nvarchar(50),
@AgentGrossCommission
decimal
(18,2),
@AgentTax
decimal
(18,2),
@AgentNetCommission
decimal
(18,2),
@ProductType
varchar
(50),
@Seller nvarchar(50),
@Remarks nvarchar(50),
--customer area
@CustomerID
int
,
@ReferenceNo nvarchar(50),
@CustomerName nvarchar(255),
@TaxPayersID nvarchar(50),
@Village nvarchar(50),
@District nvarchar(50),
@Province nvarchar(50),
@MobilePhone nvarchar(50),
@CustTelephone nvarchar(50),
@CustEmail nvarchar(50),
@Agent nvarchar(50),
@Status nvarchar(50),
@TrailDate nvarchar(50),
@TrailTime nvarchar(50))
AS
BEGIN
BEGIN
TRY
BEGIN
TRANSACTION
-- insert statement for tblMotorInsurance_eCI
INSERT
INTO
[dbo].[tblMotorInsurance_eCI]
(
[RefNumber]
,[IssuanceType]
,[CustomerNo]
,[VehicleType]
,[VehicleBrand]
,[VehiclePlateNo]
,[VehicleEngineNo]
,[VehicleChasisNo]
,[VehiclePower]
,[InsuranceType]
,[InsuranceOption]
,[PeriodFrom]
,[PeriodTo]
,[ApplicationTime]
,[DateIssued]
,[NetPremium]
,[RegistrationFee]
,[VAT]
,[TotalPremium]
,[BranchCode]
,[AgentID]
,[Username]
--,[TransactionNumber]
,[AgentGrossCommission]
,[AgentTax]
,[AgentNetCommission]
,[ProductType]
,[Seller]
,[Remarks]
)
VALUES
(
@RefNumber,
@IssuanceType,
@CustomerNo,
@VehicleType,
@VehicleBrand,
@VehiclePlateNo,
@VehicleEngineNo,
@VehicleChasisNo,
@VehiclePower,
@InsuranceType,
@InsuranceOption,
@PeriodFrom,
@PeriodTo,
@ApplicationTime,
@DateIssued,
@NetPremium,
@RegistrationFee,
@VAT,
@TotalPremium,
@BranchCode,
@AgentID,
@Username,
--@TransactionNumber,
@AgentGrossCommission,
@AgentTax,
@AgentNetCommission,
@ProductType,
@Seller,
@Remarks
)
-- insert statement for tblcustomers_shortterm
if exists (
select
*
from
tblCustomers_ShortTerm
where
CustomerNo=@CustomerNo)
begin
INSERT
INTO
[dbo].[tblCustomers_ShortTerm]
(
[ReferenceNo]
,[CustomerNo]
,[CustomerName]
,[TaxPayersID]
,[Village]
,[District]
,[Province]
,[CustTelephone]
,[MobilePhone]
,[CustEmail]
,[AgentID]
,[Status]
,[Username]
,[TrailDate]
,[TrailTime])
VALUES
(
@ReferenceNo,
@CustomerNo,
@CustomerName,
@TaxPayersID,
@Village,
@District,
@Province,
@MobilePhone,
@CustTelephone,
@CustEmail,
@Agent,
@Status,
@Username,
@TrailDate,
@TrailTime)
end
COMMIT
TRANSACTION
END
TRY
BEGIN
CATCH
ROLLBACK
TRANSACTION
RAISERROR(
'An error occured. All entries are reverted to it original state.'
,16,1)
END
CATCH
END
Thanks in advance.
Post
Reset
Cancel
Answers (
9
)
Next Recommended Forum
how can we upload image in admin panel and show in front...?
Embedded image in Resource reported error ?