Check for duplicates before inserting into Access

[kre1973]

I'm trying to check for duplicates before I do an INSERT into the Access database table. I basically want to alert the user if the 'ProjectName' and the 'MileStones' are already in the table..
thanks

Private Sub btnADD_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnADD.Click
             strSQL = "INSERT INTO Project" & _
            "(ProjectName, Planned, Actual, MileStones, Status, [Next], Comments)VALUES " & _
            "('" & txtProject.Text & "'," & _
            "'" & txtPlanned.Text & "','" & txtActual.Text & "'," & _
            "'" & txtMile.Text & "','" & txtStatus.Text & "'," & _
            "'" & txtNext.Text & "','" & txtComment.Text & "')"
            connection.Open()
            Dim cmd As New OleDbCommand(strSQL, connection)
            cmd.ExecuteNonQuery()
            connection.Close()
End Sub

 

[AlexCuse]

Rather than performing two database operations each time, wouldn't it be easier to set the two columns to be the table's primary key, and display the appropriate message if you get a primary key violation exception?

Hope this helps,

Alex

[small]----signature below----[/small]
Numbers is hardly real and they never have feelings but you push too hard, even numbers got limits
Why did one straw break the camel's back? Here's the secret: the million other straws underneath it

My Crummy Web Page

 

[PRPhx]

I agree with AlexCuse. In your table, set your primary key to not allow duplicates. When a user enter a duplicate and clicks the add button, you'll get a message about duplicates not being allowed (Or add your own custom message in a try catch block.)

IF that doesn't work for you, you could do a search for the values after the user moves off the text box. If the search finds an existing value, alert the user, place the focus back on the textbox, and allow the user to either correct the entry or enter a completely new one.