i enter 3 identical records in an sql database table. Normally this should not have happened and the dbms is to check for identical records. Now when i try to delete or update any record, an error message is displayed and the record is neither deleted nor updated. any help from any one plz.
Create a sub that checks for duplicate values BEFORE updating the first time a user adds a new record -
Private Sub CheckDuplicate()
Dim dupstr As String
dupstr = txtIDNumber.Text
Set rsDuplicate = New ADODB.Recordset
rsDuplicate.Open "SELECT * FROM SalesmanSetUp WHERE IdentityNumber = " & "'" & dupstr & "'", cnConsultant, adOpenStatic, adLockOptimistic
If rsDuplicate.BOF = True Then
ElseIf rsDuplicate.EOF = True Then
If MsgBox("This employee already exists. Continue with entry?", vbQuestion + vbYesNo, "Employee Exist") = vbYes Then
MsgBox "New Employee entry cancelled.", vbOKOnly + vbInformation, "Entry Cancelled"
'In your update event, BEFORE updateing have something like -
'If cancel entry was selected, exit, else
Well if you are actually using M$'$ SQL Server of any version since 6.5, you will need to rollback your database and rebuild it from the log table up until the point of where that first record is entered. On how to do this exactly? Look in the help files for SQL Server or visit a M$ SQL Server forum.
IF using access, compact and repair database prior to attempting to delete records as what you may be seeing is ghost records (*) due to running low on memory. A reboot might be in order also.
(*) Don't exactly know what to call them, but when those funny things happen right before your machine looks like it wants to crash... if you know what I mean...
Perhaps a picture of what you are talking about might be in order...