Dont wan insert duplicate record into database

[berrie_luv]

Hi, How do i prevent user to insert duplicate record in my database? I need to check whether there is any staffNo same in the database, how do i go abt the codes? Can u pls help mi, n give mi some sample codes?
Below are my codes for e inserting of new record.

Private Sub addStaff()
' Validate form values.
If Not IsValidForm() Then
Exit Sub
End If

Dim cnSQL As SqlConnection
Dim cmSQL As SqlCommand
Dim strSQL As String

Try
strSQL = "INSERT INTO StaffInfo(SurName, Name, StaffNo, Designation, Department, ExtNo, Email) VALUES ('" + txtSur.Text + " ','" + txtName.Text + "','" + txtStaffNo.Text + "','" + txtDes.Text + "','" + txtDep.Text + "','" + txtExt.Text + "', '" + txtEmail.Text + "')"
cnSQL = New SqlConnection(ConnectionString)
cnSQL.Open()
cmSQL = New SqlCommand(strSQL, cnSQL)

'Execute query
cmSQL.ExecuteNonQuery()

' Close and Clean up objects
cnSQL.Close()
cmSQL.Dispose()
cnSQL.Dispose()

Catch Exp As SqlException
MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")

Catch Exp As Exception
MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
End Try

MessageBox.Show(Me, "Your record is saved!", "Record Saved", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
Me.Close()
End Sub

[Lunatic3]

There are some ways to do so, for example if your staffno is primary key then you can catch the error of duplicaion of primary key and so on

strSQL = "INSERT INTO StaffInfo(SurName, Name, StaffNo, Designation, Department, ExtNo, Email) VALUES 
('" + txtSur.Text + " ','" + txtName.Text + "','" + txtStaffNo.Text 
+ "','" + txtDes.Text + "','" + txtDep.Text + "','" + txtExt.Text + "', '" + txtEmail.Text + "')"

I never use such a code in my programs, I prefer to go with parametes than that. Imagine one of the texts in the textboxes have and " or ' or lets say other bad characters in it, then the sql statment will produce errors.

[berrie_luv]

Lunatic3 > > > Then wat u suggest mi to do? Change my insert statement?

[Lunatic3]

Yes, add parameters to your command, then assgin parameters values.

[berrie_luv]

Can u pls show mi some codes? i dun understand..

[MikeStammer]

To answer your original question, there are 2 ways you can go about it. this assumes your database is set up correctly as far as primary keys, constraints, etc are concerned.

1. Run a select statement to check for the data you are about to insert first. In other words, if you have a code or a number that is unique, check the database for it. If you get a record back, it already exists.

2. Just insert it and use a try catch block to check for SQL insert errors. If it is an insert error, then handle it however you want. if it is some other error, handle it some other way. For instance:

Try
cmdRefNum.ExecuteNonQuery()
Catch ex As Exception
' if we violate the constraint, we just ignroe the error. this keeps reference numbers unique on a per 204 basis
If Not ex.Message.StartsWith("Violation of UNIQUE KEY constraint 'IX_tbl204ReferenceNumberRefNumCon'") Then
Throw New Exception("Error inserting 204 reference number")
End If
End Try

Also, dont call dispose on your objects. Just close them and then set them to Nothing if you want to make them elegible for garbage collection

[MikeStammer]

I would post some example code here, but the stupid ass 10000 character limit screws that up. Check attached file.

[berrie_luv]

Oh.. thanks liao, i got it.