Question Validating Duplicate Record of SQL Database before Inserting & Updating

[tashiduks]

Hi Everyone,

Can any one tell me how to validate the Duplicate record before Inserting and Updating Command is Trigered from VB.Net Form. I am new to VB.


Tashee

 

[Herman]

Change your UPDATE or INSERT query to a SELECT one?

 

[khan_naved]

can u please give us an example...

 

[Herman]

Well what is your update query? What exactly needs to be validated? If you just want to see if Column1 already contains some value before running the INSERT query just look for that value in Column1 with a SELECT...

SELECT * FROM Table1 WHERE Column1 = somevalue

In code, if using ADO, just construct your query like so, assuming the variable SomeValue contains a numeric value you will be inserting:

strQuery = "SELECT * FROM Table1 WHERE Column1 = " & SomeValue.ToString

Trying to INSERT duplicate data in an indexed column that does not allow duplicates will raise a SQL exception, so you could also just look for the exception to detect duplicates. Potentially twice as fast if duplicates are a rare occurrence.

 

[tashiduks]

re : Validating Duplicate Record of SQL Database before Inserting & Updating

Hi,

Thanks for your support and happy to go through it. Well i have got one store procedure for insert which works fine. If i want to add a validation in my insert stored procedure then how will i.

This my Stored Procedure:

/*
Name: usp_InsertContactTitleSetup
Description: Insert the Record to dbo.hrContactTitlesSetup table
Author: Tashi
Modification: Insert

Description Date Changed By
Created Procedure 25/02/2011 Tashi
*/
CREATE PROCEDURE [dbo].[usp_InsertContactTitlesSetup]
(
@CtsTitleID CHAR(4),
@CtsTitle VARCHAR(10),
@IsDefault BIT,
@SetDate DATETIME,
@UserName VARCHAR(20),
@CMD_Flag CHAR(1)
)
WITH ENCRYPTION
AS
INSERT INTO [dbo].[hrContactTitlesSetup] VALUES
(
@CtsTitleID,
@CtsTitle,
@IsDefault,
@SetDate,
@UserName
)
INSERT INTO [dbo].[hrContactTitlesSetup_Audit] VALUES
(
@CtsTitleID,
@CtsTitle,
@CMD_Flag,
@IsDefault,
@SetDate,
@UserName
)

where the validation line will go, can you please help me.

Tashee