How to delete Selected Item From Listview and Database

[Jaseem Ahmed]

i want to delete selected record from listview and database, but when i press delete button, it deltes onlye first record of listview not selected record...
here is code...

Private Delte_Click()
Call Mydatacon
Dim rs As adodb.Recordset
Set rs = New adodb.Recordset
rs.Open "SELECT * FROM AAAAA_DEMO", con, adOpenStatic, adLockOptimistic 'WHERE code='listview1.SelectedItem'"
If ListView1.SelectedItem.Index And Not rs.EOF Then
rs.Delete
 Else
MsgBox "No Any record Found."
End If
End Sub

[Jx_Man]

Function to check id in database

Public Function CheckId(Id As String) As Boolean
    rs.Open "SELECT * FROM AAAAA_DEMO WHERE code ='" & Trim(Id) & "'", _
        Con, adOpenDynamic, adLockBatchOptimistic
    
    While Not rs.EOF
        If Id = rs!Id Then
            CheckId = True
        Else
            CheckId = False
        End If
        rs.MoveNext
    Wend
    rs.Close
End Function

Private Delte_Click()
Call Mydatacon
Dim rs As adodb.Recordset
Dim SQlQuery As String
Set rs = New ADODB.Recordset

If CheckId(ListView1.SelectedItem) = True Then 'check selected id in database
    SQlQuery = "DELETE FROM AAAAA_DEMO WHERE code ='" & Trim(ListView1.SelectedItem) & "'"
    Con.Execute SQlQuery, , adCmdText
Else
    MsgBox "Record not find"
End If
End Sub

[Jaseem Ahmed]

THANKS JX...ITS WORKING VERY WELL, BUT STILL IT IS ERRORING,WHEN THERE IN NO ANY RECORD IN LISTVIEW..

"Object Variable or with block variable not set."

If CheckId(ListView1.SelectedItem) = True Then 'check selected id in database[

this is the one error, other wise performing best..
and can u plz explain this code to me??

rs.Open "SELECT * FROM AAAAA_DEMO WHERE code ='" & Trim(Id) & "'", _
        Con, adOpenDynamic, adLockBatchOptimistic

where='" & Trim(Id)" what is it's functionalty and why you used adOpenDynamic, adLockBatchOptimistic??

[Jx_Man]

THANKS JX...ITS WORKING VERY WELL, BUT STILL IT IS ERRORING,WHEN THERE IN NO ANY RECORD IN LISTVIEW..

"Object Variable or with block variable not set."

Just check the items, if there are no data in listview then display a message.

If ListView2.ListItems.Count > 0 Then
    If CheckId(ListView2.SelectedItem) = True Then
        SQlQuery = "DELETE FROM Users WHERE Id_User ='" & Trim(ListView2.SelectedItem) & "'"
        Conn.Execute SQlQuery, , adCmdText
    Else
        MsgBox "Record not find"
    End If
Else
    MsgBox "no data on list view"
End If

where='" & Trim(Id)" what is it's functionalty

Trim used to remove white space at the beginning and end of a string.
Ex : Trim(" Daniweb ") -> Result : "Daniweb"

[Jx_Man]

don't forget to mark this thread as solved :)