CODEWITHSUNDEEP
sql-servervb.net
ivandinglasan
ivandinglasan

Reputation: 384

Check for duplicate records in sql database using vb.net

Assuming my table consist of two columns ID and Name.

And assume I have my stored procedure working on vb.net that inserts rows into the database.

But my system needs to check if an ID entered in a textbox already exists in the database when ADD button is click.

CREATE PROCEDURE AddOfficeEquipmentProfile
(
@OE_ID      varchar(11),    
@OE_Category        char(3) =NULL,      
@OE_SubCategory char(3)=    NULL,       
@OE_Name        varchar(35)=NULL,       
@OE_User        varchar(35)=NULL,   
@OE_Brand       varchar(15)=NULL,   
@OE_Model       varchar(35)=NULL,   
@OE_Specs       varchar(1000)=NULL,     
@OE_SerialNo        varchar(35)=NULL,   
@OE_PropertyNo  varchar(35)=NULL,   
@OE_MacAddress  varchar(100)=NULL,      
@OE_Static_IP       varchar(15)=NULL,   
@OE_Vendor      varchar(35)=NULL,   
@OE_PurchaseDate    smalldatetime,      
@OE_WarrantyInclusiveYear   int=NULL,   
@OE_WarrantyStatus  char(2)=    NULL,   
@OE_Status      varchar(15)=NULL,       
@OE_Dept_Code   char(3)=    NULL,   
@OE_Location_Code   char(8)=    NULL,       
@OE_Remarks     varchar(1000)=  NULL
)
AS

INSERT INTO tblOfficeEquipmentProfile (OE_ID, OE_Category, OE_SubCategory, OE_Name, OE_User, OE_Brand, OE_Model, OE_Specs, OE_SerialNo,
OE_PropertyNo, OE_MacAddress, OE_Static_IP, OE_Vendor, OE_PurchaseDate, OE_WarrantyInclusiveYear, OE_WarrantyStatus, OE_Status, OE_Dept_Code,
OE_Location_Code, OE_Remarks ) 
VALUES (@OE_ID, @OE_Category, @OE_SubCategory, @OE_Name, @OE_User, @OE_Brand, @OE_Model, 
@OE_Specs, @OE_SerialNo, @OE_PropertyNo, @OE_MacAddress, @OE_Static_IP, @OE_Vendor, @OE_PurchaseDate, @OE_WarrantyInclusiveYear, @OE_WarrantyStatus,
@OE_Status, @OE_Dept_Code, @OE_Location_Code, @OE_Remarks)

GO

Upvotes: 0

Views: 3363

Answers (4)

ochakov
ochakov

Reputation: 458

Check the following article to create a SP finding duplicate rows in any table:

http://www.codeproject.com/Articles/157977/Remove-Duplicate-Rows-from-a-Table-in-SQL-Server

Upvotes: 0

PHeiberg
PHeiberg

Reputation: 29851

Building on the answer from @marc_s. In order to show a message to the user in case there already is a row in the database with the same id, you can check the number of affected rows from the query execution result.

This assumes that the stored procedure only inserts the row if the id is not present and does not emit any errors/exceptions.

Using ADO.NET (with an existing command executing the stored procedure):

Dim affectedRows as Integer = command.ExecuteNonQuery()
If affectedRows = 0 Then
    'Handle the error here
    MessageBox.Show("There is already a Profile with the supplied id")
Else
    'Insert was made
End If

Upvotes: 0

marc_s
marc_s

Reputation: 755491

If this is for SQL Server and you're using a stored procedure - just try something like this:

CREATE PROCEDURE AddOfficeEquipmentProfile
(
   @OE_ID      varchar(11),    
    ..... all your other parameters here.....
)
AS 
   IF NOT EXISTS (SELECT * FROM dbo.tblOfficeEquipmentProfile WHERE OE_ID = @OE_ID) 
       INSERT INTO dbo.tblOfficeEquipmentProfile(.... list of columns.....)
       VALUES (......list of values................)

Assuming that OE_ID is your primary key and will be unique. Just check if that @OE_ID doesn't exist yet, and if it doesn't - insert the data. If it exists - don't do anything.

Upvotes: 0

Damith
Damith

Reputation: 63105

few things you can do

  1. make ID column as primary key, when insert you will get exception if duplicated
  2. You can use auto increment ID, then you don't need to check ID exit or not. database will handle that
  3. If you can't do above, run select statement or stored procedure to check whether id exist or not.

Upvotes: 3

Related Questions