'Delete Snippet using rs.find'

Private Sub cmdDelete_Click()
    'Declare Variables
    Dim index As Integer
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim answer As Integer
    Dim selectedName as string

    'Set up Database Connection
    Set conn = New ADODB.Connection
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\SnippetsDatabase.mdb"

    index = lstSnippets.ListIndex

    'If Index is Valid
    If index <> -1 Then

        'Prompt user
        answer = MsgBox("Do you want delete this Snippet?", vbQuestion + vbYesNo, App.Title)
        'If user Clicked Yes
        If answer = vbYes Then

            'Get the currently selected text from the lstSnippets Listbox. If this is not the snipped name then this won't work!
            selectedName = lstSnippets.Text

            lblSnippetNamePreview.Caption = "Snippet Name: "
            lblSnippetLangPreview.Caption = "Snippet Language: "
            txtSnippetCodePreview.Text = ""

            'Open Database connection
            conn.Open

            'Open Table
            Set rs = New ADODB.Recordset
            rs.Open "tblSnippets", conn, adOpenKeyset, adLockOptimistic

            Do While not rs.eof
                rs.Find "[Snippet_Name] ='" & selectedName & "' AND [Snippet_Lang] = '" & Snippet_Lang & "'"
                if not rs.eof then
                    rs.Delete
                end if
            Loop
            rs.Close
            conn.Close

            'Cleanup
            Set rs = Nothing
            Set conn = Nothing

            'Update Form
            lstSnippets.RemoveItem index
            MsgBox "Snippet deleted sucessfully", vbInformation + vbOKOnly, App.Title
        End If
    End If
End Sub


'Delete Snippet using ado command

Private Sub cmdDelete_Click()
    'Declare Variables
    Dim index As Integer
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sqlcmd as ADODB.Command
    Dim answer As Integer
    Dim selectedName as string

    'Set up Database Connection
    Set conn = New ADODB.Connection
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\SnippetsDatabase.mdb"

    index = lstSnippets.ListIndex

    'If Index is Valid
    If index <> -1 Then

        'Prompt user
        answer = MsgBox("Do you want delete this Snippet?", vbQuestion + vbYesNo, App.Title)
        'If user Clicked Yes
        If answer = vbYes Then

            'Get the currently selected text from the lstSnippets Listbox. If this is not the snipped name then this won't work!
            selectedName = lstSnippets.Text

            lblSnippetNamePreview.Caption = "Snippet Name: "
            lblSnippetLangPreview.Caption = "Snippet Language: "
            txtSnippetCodePreview.Text = ""

            'Open Database connection
            conn.Open

            'Create SqlCmd
            set sqlcmd = new ADODB.Command

            'Command text is the SQL query to run. We want to DELETE the rows from tblSnippets where the Columns Snippet_Name and Snippet_Lang match what we selected in the form
            '? - a placeholder for a parameter

            with sqlcmd
                .CommandText = "DELETE FROM tblSnippets WHERE [Snippet_Name] = ? AND [Snippet_Lang] = ?"
                'Add in selectedName and Snippet_Lang as parameters
                'Append a Text Parameter as an Input, with a Max Length of 255, with value of selectedName
                .Parameters.Append .CreateParameters(,adVarChar, adParamInput, 255, selectedName)
                'Append a Text Parameter as an Input, with a Max Length of 255, with value of Snipped_Lang
                .Parameters.Append .CreateParameters(,adVarChar, adParamInput, 255, Snippet_Lang)
                'Set Sqlcmd as prepared
                .Prepared = True
                'Set the connection to the one we just opened
                .ActiveConnection = conn
                'Run the query
                .Execute
            end with

            'Cleanup
            Set rs = Nothing
            set sqlcmd = Nothing
            Set conn = Nothing

            'Update Form
            lstSnippets.RemoveItem index
            MsgBox "Snippet deleted sucessfully", vbInformation + vbOKOnly, App.Title
        End If
    End If
End Sub