How to avoid duplicate record

[eakaglo]

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

[Ancient Dragon]

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.

[eakaglo]

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.

[eakaglo]

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

[adam_k]

        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

[Ancient Dragon]

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.