[2005] Delete Access Record From Listbox

[Tddupre]

Hi I have searched for about week through the forums and the internet, but was unable to find anything that worked for me.

I have listbox that gets its data from an access database. I need to figure out how to delete the selected item in the listbox from the database.

Thanks any advice would be appreciated.

[Hack]

Are you familiary with SQL DELETE queries?

Dim sSQL As String
sSQL = "DELETE FROM yourtable WHERE fieldname = '" & ListBox1.Text & "' "

[Tddupre]

Are you familiary with SQL DELETE queries?

Dim sSQL As String
sSQL = "DELETE FROM yourtable WHERE fieldname = '" & ListBox1.Text & "' "

Yea I knew it was something like that. Here Is My Add Code & What I Have For My Delete Code, The Delete Still Does Nothing, I know I am missing Something.

Edit: If I switch me.lstAccounts.text to me.lstAccounts.selecteditem I get...

Operator '&' is not defined for string "DELETE FROM Table1 WHERE Account" and type 'DataRowView'.

Add Code:

Dim con As New OleDb.OleDbConnection
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim SQL As String
        'Check To Make Sure There Is An Entry In The Site Field
        If Me.SiteText.Text = "" Then
            MsgBox("Please Enter A Site To Search", MsgBoxStyle.Critical, "Warning!")
        Else
            'Connect To Accounts Database
            con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\Tdupre8863\Desktop\Accounts.mdb"

            con.Open()
            SQL = "SELECT Username FROM Table1 where Account = '" & Me.SiteText.Text & "'"

            da = New OleDb.OleDbDataAdapter(SQL, con)
            da.Fill(ds, "Boxes")
            Me.lstAccounts.DataSource = ds.Tables(0)
            Me.lstAccounts.DisplayMember = "Username"
        End If

Delete Code:

  Dim con As New OleDb.OleDbConnection
        Dim sSQL As String
        If MsgBox("Would You Like To Delete This Account?", MsgBoxStyle.YesNo, "Delete?") = MsgBoxResult.Yes Then
            con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\Tdupre8863\Desktop\Accounts.mdb"
            con.Open()
            sSQL = "DELETE FROM Table1 WHERE Account = '" & Me.lstAccounts.Text & "' "
            con.Close()
            Me.lstAccounts.Refresh()
        Else
            MsgBox("Error", MsgBoxStyle.Critical, "Error")
        End If

[Tddupre]

@sikkaiappan

Y are you posting this in an existing thread?

[Hack]

@sikkaiappan

Y are you posting this in an existing thread?

FYI: I moved what had been posted in your thread into a thread of its own, and moved that thread to VB6.

I'm not sure why are you doing a SELECT when you really want to do a delete. Anyway, I've take some of your code and put it together with some of mine, and you can take these and place it whereever it makes the most sense for your project.

Dim con As New OleDb.OleDbConnection              
'Connect To Accounts Database
con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\Tdupre8863\Desktop\Accounts.mdb"
con.Open()

sSQL = "DELETE FROM yourtable WHERE fieldname = @WhatyouwanttoDelete "

Dim command As New OleDBCommand(sSQL, con)
With command.Parameters
     .AddWithValue("@WhatyouwanttoDelete", ListBox1.Text)
End With

command.ExecuteNonQuery()

[Tddupre]

ok i know im getting closer
im getting the error: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

My Database name is Accounts.mdb
My Table Name is Table1
My Columns Are Account (which is the first column)& Username
I need the selected item in the listbox deleted from this datbase

Updated Code:

Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDelete.Click
        Dim con As New OleDb.OleDbConnection
        Dim sSql As String
        'Connect To Accounts Database
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = ../Accounts.mdb"
        con.Open()
 
        sSql = "DELETE FROM Table1 WHERE Account = @Account "
 
        Dim command As New OleDbCommand(sSQL, con)
        With command.Parameters
            .AddWithValue("@Account", Me.lstAccounts.SelectedItem)
        End With
 
        Me.lstAccounts.Refresh()
        command.ExecuteNonQuery()
        con.Close()
    End Sub