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

Question

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 = "admin@userpoint.com"
                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.

Check this question : [Stop Inserting in Table if record already exists](http://stackoverflow.com/questions/1388235/stop-inserting-in-table-if-record-already-exists) — Shoban, Sep 07 '09 at 08:48

Preet Sangha · Accepted Answer · 2009-09-07T09:02:23.817

2

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)

edited Sep 07 '09 at 09:02

answered Sep 07 '09 at 08:42

Preet Sangha

64,563
18
145
216

Dear but I can have many course id with different cpuserid vice versa – Manoj Singh Sep 07 '09 at 08:48
Thats ok. The above just means that only unique row with both values will be allowed, any other permutation is allowed. – Preet Sangha Sep 07 '09 at 08:50
I am controlling this in my sql server proc, just I want to throw exception message if user tries to insert same record – Manoj Singh Sep 07 '09 at 08:55

score 1 · Answer 2 · answered Sep 07 '09 at 08:53

1

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.

answered Sep 07 '09 at 08:53

Iain Hoult

3,889
5
25
39

I have created Unique Index on both the column, as well as checked in my proc using IF NOT EXISTS (SELECT * FROM tblDelegate WHERE CourseID = @CourseID and CPUserID = @CPUserID) before it insert, now how should I show message to user that records cannot be inserted as it is already present in table – Manoj Singh Sep 07 '09 at 08:59

score 0 · Answer 3 · answered Sep 07 '09 at 08:43

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...

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

3 Answers3