CODEWITHSUNDEEP
sql-servervb.net
user3916571
user3916571

Reputation: 25

How to check for duplicate entries before inserting any data? I am using Visual Basic.Net and SQL Server

Here is my code using stored procedure in visual basic.net: 

Dim sqlComm As New SqlCommand()

sqlComm.Connection = sqlCon
sqlComm.CommandText = "InsertDataIntoTable"
sqlComm.CommandType = CommandType.StoredProcedure

sqlComm.Parameters.AddWithValue("Name", txtname.Text)
sqlComm.Parameters.AddWithValue("Surname", txtsurname.Text)
sqlComm.Parameters.AddWithValue("Age", Integer.Parse(txtage.Text))

sqlCon.Open()
sqlComm.ExecuteNonQuery()
sqlCon.Close()

Stored procedure code using Microsoft SQL Server 2012.

I tried to insert 

IF(SELECT COUNT(*) FROM tblSPExample WHERE Name= @Name) < 1

before then begin keyword but my problem is it can't prompt message in visual basic there is a duplicate name that already exists in the database.

ALTER PROCEDURE [dbo].[InsertDataIntoTable]
   @Name varchar(50),
   @Surname varchar(50),
   @Age int
AS
BEGIN
   INSERT INTO tblSPExample(Name, Surname, Age)
   VALUES (@Name, @Surname, @Age)
END

Upvotes: 0

Views: 2182

Answers (2)

marc_s
marc_s

Reputation: 755491

Use the IF NOT EXISTS() answer provided by @R.T. - that's much more efficient than your SELECT COUNT(*) .... > 0 approach!

In order to find out in your VB.NET code whether something has been inserted, check out the return value from the ExecuteNonQuery call:

Executes a Transact-SQL statement against the connection and returns the number of rows affected.

So if you check for this being > 0, you know a row has been inserted; if it is = 0 then no row has been inserted:

int rowsInserted = sqlComm.ExecuteNonQuery();

if (rowsInserted <= 0)   // no rows were inserted
   MessageBox.Show("No rows inserted");   // or whatever you want to do ....

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172628

You may try like this in your stored procedure:

ALTER PROCEDURE [dbo].[InsertDataIntoTable]
@Name varchar(50),
@Surname varchar(50),
@Age int 
AS

BEGIN
   IF NOT EXISTS (SELECT 1 FROM dbo.tblSPExample WHERE Name = @Name and Surname = @Surname and Age = @Age)
   INSERT INTO tblSPExample(Name, Surname, Age)
   VALUES ( @Name, @Surname, @Age)
END

Upvotes: 3

Related Questions