1,105,625 Community Members

How to avoid duplicate record

Member Avatar
eakaglo
Newbie Poster
8 posts since Feb 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Please I'd be thankful if someone could very kindly edit the following code for me. Currintly, the code doesn't enable the user to save a new record; but, it's supposed to allow insertion of new record as well as prevent insertion of duplicate record.

Try
            cmd2 = New SqlCommand("select count(*) from ProgramDetails.Subjects where IDNumber = '" & txtIDNumber.Text & "'", cn)
            queryResult = cmd2.ExecuteScalar
            If queryResult > 0 Then
                If String.IsNullOrEmpty(subject_id) Or rbnCore.Checked = False And rdbElective.Checked = False Or String.IsNullOrEmpty(username) Or String.IsNullOrEmpty(password) Then
                    MessageBox.Show("One or more fields have not been left empty.", "Save Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
                    txtSubjectCode.Focus()
                    'Else
                    If rbnCore.Checked = True Then
                        subject = "Core"
                    Else : subject = "Elective"
                    End If

                    If String.IsNullOrEmpty(subject_id) Or rbnCore.Checked = False And rdbElective.Checked = False Or String.IsNullOrEmpty(username) Or String.IsNullOrEmpty(password) Then
                        MessageBox.Show("One or more fields have not been left empty.", "Save Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
                        txtSubjectCode.Focus()
                        qry = "Insert into ProgramDetails.Subjects(SubjectCode,SubjectName,SubjectType,UserID,Password)values(@SubjectCode,@SubjectName,@SubjectType,@UserID,@Password)"
                        cmd = New SqlCommand(qry, cn)
                        cmd.Parameters.Add(New SqlParameter("@SubjectCode", txtSubjectCode.Text))
                        cmd.Parameters.Add(New SqlParameter("@SubjectName", txtSubName.Text))
                        cmd.Parameters.Add(New SqlParameter("@SubjectType", subject))
                        cmd.Parameters.Add(New SqlParameter("@UserID", txtUserName.Text))
                        cmd.Parameters.Add(New SqlParameter("@Password", txtPassword.Text))
                        cmd.ExecuteNonQuery()
                        MessageBox.Show("Record successfully saved", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        btnSave.Enabled = False
                        btnClear.Focus()
                        showgrid()
                    End If
                End If
            Else
                MessageBox.Show("Record already exists.", "Existing Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
Member Avatar
Ancient Dragon
Achieved Level 70
27,643 posts since Aug 2005
Reputation Points: 5,232 [?]
Q&As Helped to Solve: 3,038 [?]
Skill Endorsements: 115 [?]
Team Colleague
Featured
Sponsor
 
0
 

You don't have to do anything in your code to prevent adding rows that contain duplicate key field(s), the database server to do that if you designed the table to require key fields to be unique values. All your program needs to do is catch the db server's exception. If the record already exists then an exception is thrown, line 32 will not get executed.

IMO you have too many checks inside the try/catch block. Inside the try/catch block all you need are the lines that actualy do the insertion, the database server will do the rest.

Member Avatar
eakaglo
Newbie Poster
8 posts since Feb 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

The table into which the record has to be saved has an IsIdentity primary key which means that the key is generated automatically by SQL Server but the user is still able to save existing record.

Member Avatar
eakaglo
Newbie Poster
8 posts since Feb 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

I however admit that line 14-16 should have been removed since it's same as line 5-7.

Member Avatar
adam_k
Veteran Poster
1,056 posts since Jun 2011
Reputation Points: 239 [?]
Q&As Helped to Solve: 212 [?]
Skill Endorsements: 17 [?]
 
1
 
        cmd2 = New SqlCommand("select count(*) from ProgramDetails.Subjects where IDNumber = '" & txtIDNumber.Text & "'", cn)
        queryResult = cmd2.ExecuteScalar
        If queryResult > 0 Then

So basically you are telling your program that if count is greater that 0 (records have been found) do the insert. If it's less or equal to 0 (impossible to be less or no matches found) error the user.
change If queryResult > 0 Then to If queryResult = 0 Then

Member Avatar
Ancient Dragon
Achieved Level 70
27,643 posts since Aug 2005
Reputation Points: 5,232 [?]
Q&As Helped to Solve: 3,038 [?]
Skill Endorsements: 115 [?]
Team Colleague
Featured
Sponsor
 
1
 

Are you able to change the table schema? If yes, then use one or more fields as the unique key. Auto id is nice, but not very usefor for preventing duplicate records.

Question Answered as of 1 Year Ago by Ancient Dragon and adam_k
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article