Query a database and insert if not present

Question

I would like to add a way to see if an entry is already in a connected database in the following code. If it is then don't add the entry and pop up a dialog saying something to the effect of "already been scanned" and if it is not, proceed as usual.

Using connection As New SqlClient.SqlConnection("Data Source=XXXXX;Initial Catalog=XXXXXX;Integrated Security=True;Pooling=False;Encrypt=False"), _
    cmd As New SqlClient.SqlCommand("INSERT INTO [XXXXX] (TrackingNumber, Date) SELECT @TrackingNumber, @Date WHERE NOT EXISTS (SELECT * FROM([XXXXX])WHERE TrackingNumber = @TrackingNumber AND Date = @Date)", connection)
        cmd.Parameters.Add("@TrackingNumber", SqlDbType.VarChar, 50).Value = TrNum
        cmd.Parameters.Add("@Date", SqlDbType.DateTime, 8).Value = TrDate
        connection.Open()
        cmd.ExecuteNonQuery()
        connection.Close()
End Using

Matthew Flynn · Accepted Answer · 2010-06-21 16:33:58Z

1

You should be able to put your inputs into a subquery that checks for redundancy:

INSERT INTO [XXXXXXX] (TrackingNumber, Date) 
    SELECT @TrackingNumber, @Date from DUAL 
    WHERE NOT EXISTS (
        SELECT * 
        FROM [XXXXXXX]
        WHERE TrackingNumber = @TrackingNumber AND Date = @Date)

answered Jun 21, 2010 at 16:33

Matthew Flynn

2,23819 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Martin Smith Over a year ago

@0bfus - For SQL Server just delete the from DUAL you can get rows affected as the return value of cmd.ExecuteNonQuery. If zero nothing was changed.

Martin Smith Over a year ago

You don't need the brackets here SELECT * FROM([XXXXX])

0bfus Over a year ago

alright another error at the same point once the brackets are out and it says "Violation of PRIMARY KEY constraint 'PK_XXXXX'. Cannot insert duplicate key in object 'dbo.XXXXX'. The statement has been terminated." so it sees its a duplicate but now it needs to alert the user it is a duplicate and also not crash the app lol

0bfus Over a year ago

@Martin Smith the trackingnumber is and also thanks, new here. should i make a new column in the table that auto increments and make that the primary?

0bfus Over a year ago

@Martin Smith alright it no longer throws any errors but it also does not tell me it has already been enetered , is there a way?

|

Martin Smith · Accepted Answer · 2010-06-21 18:38:54Z

0

My VB.NET might be somewhat off but hopefully this should give the general idea!

Dim rowsAffected AS Integer

    Using connection As New SqlClient.SqlConnection("Data Source=XXXXX;Initial Catalog=XXXXXX;Integrated Security=True;Pooling=False;Encrypt=False"), _
        cmd As New SqlClient.SqlCommand("INSERT INTO [XXXXX] (TrackingNumber, Date) SELECT @TrackingNumber, @Date WHERE NOT EXISTS (SELECT * FROM [XXXXX] WHERE TrackingNumber = @TrackingNumber)", connection)
        cmd.Parameters.Add("@TrackingNumber", SqlDbType.VarChar, 50).Value = TrNum
        cmd.Parameters.Add("@Date", SqlDbType.DateTime, 8).Value = TrDate
        connection.Open()
        rowsAffected = cmd.ExecuteNonQuery()
        connection.Close()
    End Using


If rowsAffected = 0 Then
MsgBox "Scanned Already"
Else
MsgBox "Inserted Succesfully"
End If

answered Jun 21, 2010 at 18:38

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

6 Comments

0bfus Over a year ago

@Martin Smith One more quick thing, any reason this will not allow letters and only numbers to be entered?

Martin Smith Over a year ago

@0bfus I can't see any reason why that would be the case. @TrackingNumber is varchar(50) so should allow letters. Does it give you an error?

0bfus Over a year ago

@Martin Smith It allows one entry but it stores it in the database as a blank entry with a date and if you try any other entry it says it's inserted succesfully. very odd.

Martin Smith Over a year ago

@0bfus - I'd try putting a break point on cmd.Parameters.Add("@TrackingNumber",... to check nothing else in your code is messing with TrNum and if that doesn't shed any light run SQL Profiler to see the statement actually being sent.

0bfus Over a year ago

@Martin Smith Not sure what i did but it works fine for some reason now. Thanks again.

|

Collectives™ on Stack Overflow

Query a database and insert if not present

2 Answers 2

9 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related