Delete Whole Record In Access Using ListBox

[ariffin246]

Hi Everyone.

I am totally new here and this is my first post..
I have tried many place, since i could get any solution.
I have no choice then posting here.

basically, i have one delete form, where i will load the firstname from a table in access(hardcode) to listbox.
then when a user choose the first name, from list box and click delete.

the entire row related to the first name should be delete.

Please guide me. thank you

The code, i wrote so far..

Imports System.Data
Imports System.Data.OleDb
Public Class Delete
Dim inc, MaxRows As Integer
Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim da As New OleDb.OleDbDataAdapter
Dim sql As String




Private Sub Delete_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
System.Environment.CurrentDirectory.ToString() & "\DataBase.mdb"

con.Open()

MsgBox("A Connection to the Database is now open")
sql = "SELECT * From Contacts"
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "DataBase")

Me.lstNames.DataSource = ds.Tables("DataBase")
'Me.ddlName.ValueMember = "ID"
Me.lstNames.DisplayMember = "FirstName"

End Sub


Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
'lstNames.Items.Remove(lstNames.SelectedItem)


Dim cb As New OleDbCommandBuilder(da)

ds.Tables("DataBase").Rows(inc).Delete()

MaxRows = MaxRows - 1

inc = 0



da.Update(ds, "DataBase")



MessageBox.Show("Contact Deleted Succesfully")


End Sub

Private Sub NavRecords()
lstNames.Text = ds.Tables
End Sub
End Class

[__avd]

First, search a row using Select method of DataTable.

Dim r() As DataRow

        r = ds.Tables("Database").Select("ID=" & ddlName.SelectedValue)
        If r.Length <> 0 Then
            r(0).Delete()
        End If

Full code.

Imports System.Data
Imports System.Data.OleDb
Public Class Delete
    Dim inc, MaxRows As Integer
    Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As New OleDb.OleDbDataAdapter
    Dim sql As String

    Private Sub Delete_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                             System.Environment.CurrentDirectory.ToString() & "\DataBase.mdb"

        con.Open()

        MsgBox("A Connection to the Database is now open")
        sql = "SELECT * From Contacts"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "DataBase")

        Me.lstNames.DataSource = ds.Tables("DataBase")
        'Me.ddlName.ValueMember = "ID"
        Me.lstNames.DisplayMember = "FirstName"

    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        'lstNames.Items.Remove(lstNames.SelectedItem)

        Dim cb As New OleDbCommandBuilder(da)

         
        Dim r() As DataRow

        r = ds.Tables("Database").Select("ID=" & ddlName.SelectedValue)
        If r.Length <> 0 Then
            r(0).Delete()
        End If
        MaxRows = MaxRows - 1

        inc = 0
        da.Update(ds, "DataBase")
        MessageBox.Show("Contact Deleted Succesfully")
    End Sub

    Private Sub NavRecords()
        lstNames.Text = ds.Tables
    End Sub
End Class

[ariffin246]

Dim r() As DataRow

        r = ds.Tables("Database").Select("FirstName=" & lstNames.SelectedItem)
        If r.Length <> 0 Then
            r(0).Delete()
        End If
        MaxRows = MaxRows - 1

i modified and tried to run the code above.. where i select firstname from a list called lstnames.

i can build and run. but when i click delete button.
i am getting follwing error..

Operator '&' is not defined for string "FirstName=" and type 'DataRowView

the firstname is exist in database..

[ariffin246]

Figured it out and solved...

thanks

[jhade]

ddlName

what is ddlName in btnDelete?? thank you.