deleting records in sql database table

[Bashir ahmad]

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.

thx

[AndreRet]

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
    
        Else
    If MsgBox("This employee already exists. Continue with entry?", vbQuestion + vbYesNo, "Employee Exist") = vbYes Then
        txtIDNumber.SetFocus
        
        Exit Sub
            Else
        MsgBox "New Employee entry cancelled.", vbOKOnly + vbInformation, "Entry Cancelled"
        
        Image3_Click
        Exit Sub
    End If
End If
End Sub

'In your update event, BEFORE updateing have something like -
Call CheckDuplicate

'If cancel entry was selected, exit, else 
Rs.Update

[Bashir ahmad]

thx for the reply, what I actually want is to delete records directly from the table, not through code.

[babbu]

delete * from tablename where primarykeyvalue = value

[Bashir ahmad]

but the table is without PK.

[AndreRet]

Which client are you using, access, msSql, MySql?

Furthermore, if you say you want to delete records directly, do you mean from the client (Access, MySql etc) and NOT your application and you yourself or the user must delete?

[Bashir ahmad]

sir
i enter data directly in an sql database table and also delete or update from the table itself not from using vb application. but for the identical records, neither deletion nor update works.

[vb5prgrmr]

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

Good Luck