How to stop inserting same record in table using vb.net

Question

Hi,

I am using VB.NET and below code on button click event.

Protected Sub ibtnSendInvites_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles ibtnSendInvites.Click
        Try
            Dim emailList As New List(Of String)
            Dim conString As String = WebConfigurationManager.ConnectionStrings("LocalSqlServer").ConnectionString
            Dim con As New SqlConnection(conString)
            con.Open()
            Dim cmd As SqlCommand
            For Each curRow As GridViewRow In GridView1.Rows
                Dim chkSelect As CheckBox = CType(curRow.Cells(1).FindControl("chkSelect"), CheckBox)
                Dim emailLabel As Label = CType(curRow.Cells(1).FindControl("lblEmailAddress"), Label)
                If chkSelect.Checked Then
                    emailList.Add(emailLabel.Text)
                    cmd = New SqlCommand("uspInsertDelegate", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    cmd.Parameters.Add("@CourseID", SqlDbType.Int).Value = Session("CourseID")
                    cmd.Parameters.Add("@CPUserID", SqlDbType.Int).Value = CType(curRow.Cells(1).FindControl("lblCPUserID"), Label).Text
                    cmd.Parameters.Add("@StatusID", SqlDbType.Int).Value = 25
                    cmd.Parameters.Add("@CreateUser", SqlDbType.VarChar).Value = Session("LoggedInUser")
                    cmd.ExecuteNonQuery()
                End If
            Next
            For Each email As String In emailList
                Dim message As String = "Please confirm your booking "
                Dim subject As String = "UserPoint Course Booking Invitation Email"
                Dim from As String = "[email protected]"
                SendEmail.SendMessage(subject, message, from, email, "")
            Next
        Catch ex As Exception

        End Try
    End Sub

I want to throw exception if user tries to insert same record having same CourseID and CPUserID.

Thanks.

Best Regards, MS

Answer 1

Put a unique index on the DATABASE table on those two columns. You'll get an exception back if you try to insert a duplicate.

CREATE UNIQUE INDEX IDX_Course_UserId_Delegate
     ON Delegate (CourseID,CPUserID)

Or alter the Sp to check first

Or Add a insert trigger on the DB that will raise and exception.

Ok in your catch

 Throw New Exception("CANNOT INSERT DUPLICATE TROW", ex)

Answer 2

Two options really - query the table first to see if the row exists and throw a custom exception (wrap the whole thing in a transaction), or, enforce this in the DB with a unique constraint/index (you should do this anyway). The DB will then raise an error back when you try it...

Answer 3

Check this question : Stop Inserting in Table if record already exists

Answer 4

Instead of getting it to throw an exception you could change your stored procedure "uspInsertDelegate" to check if the row already exists before trying to do the insert, and report back whether or not it did it.

I would also create the unique index in the database as Preet says, just to be on the safe side.