Deleting data from listbox selection

[Peinecone]

I have a listbox that is populated by a DataSet. I want to be able to delete a record when a item is selected and the user clicks the "Delete" button. But I am not able to figure it out. Here is what I have now for the code.

 Public Class frmSysco

    Dim inc As Integer
    Dim MaxRows As Integer

    Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As New OleDb.OleDbDataAdapter
    Dim dt As New DataSet
    Dim db As New OleDb.OleDbDataAdapter
    Dim bs As New BindingSource
    Dim bsCat As New BindingSource
    Dim daCat As New OleDb.OleDbDataAdapter
    Dim dsCat As New DataSet
    Dim bsPUnit As New BindingSource
    Dim daPUnit As New OleDb.OleDbDataAdapter
    Dim dsPUnit As New DataSet
    Dim bsUvUM As New BindingSource
    Dim daUvUM As New OleDb.OleDbDataAdapter
    Dim dsUvUM As New DataSet
    Dim sql As String


    Private Sub frmSysco_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con.ConnectionString = "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source = D:\Ben's Documents\Work Things\Cost Manangment System\MainDatabase.accdb"
        con.Open()
        sql = "SELECT * FROM SyscoMasterPriceGuide"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "SyscoPrice")
        con.Close()

        bs.DataSource = ds.Tables("SyscoPrice")
        bs.Sort = "ProductName"
        lstName.DataSource = bs
        lstName.DisplayMember = "ProductName"
        lstName.ValueMember = "ID"

        con.Open()
        sql = "SELECT * FROM PurchaseCategories"
        daCat = New OleDb.OleDbDataAdapter(sql, con)
        daCat.Fill(dsCat, "Category")
        con.Close()
        bsCat.DataSource = dsCat.Tables("Category")
        bsCat.Sort = "PurchaseCategory"
        cboCategory.DataSource = bsCat
        cboCategory.DisplayMember = "PurchaseCategory"
        cboCategory.ValueMember = "ID"

        con.Open()
        sql = "SELECT * FROM PurchaseUnits"
        daPUnit = New OleDb.OleDbDataAdapter(sql, con)
        daPUnit.Fill(dsPUnit, "Unit")
        con.Close()
        bsPUnit.DataSource = dsPUnit.Tables("Unit")
        bsPUnit.Sort = "Unit"
        cboUM.DataSource = bsPUnit
        cboUM.DisplayMember = "Unit"
        cboUM.ValueMember = "ID"

        con.Open()
        sql = "SELECT * FROM UniversalUnit"
        daUvUM = New OleDb.OleDbDataAdapter(sql, con)
        daUvUM.Fill(dsUvUM, "Unit")
        con.Close()
        bsUvUM.DataSource = dsUvUM.Tables("Unit")
        bsUvUM.Sort = "Unit"
        cboUnivUM.DataSource = bsUvUM
        cboUnivUM.DisplayMember = "Unit"
        cboUnivUM.ValueMember = "ID"

        MaxRows = ds.Tables("SyscoPrice").Rows.Count

        Populate()
  End Sub


  Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        If MessageBox.Show("Do you really want to Delete this Record?", _
            "Delete", MessageBoxButtons.YesNo, _
            MessageBoxIcon.Warning) = DialogResult.No Then
            MsgBox("Operation Cancelled")
            Exit Sub
        End If
        Dim dr As DataRow = DirectCast(bs.Current, DataRowView).Row
 'I am missing something here 
        dr.Delete()

        da.Update(ds, "SyscoPrice")

    End Sub

 

[jmcilhinney]

Bind your data to a BindingSource and then bind that to the ListBox and/or other controls. To delete the selected record you call RemoveCurrent on the BindingSource.

 

[cjard]

(which will remove the current record from the datatable, but it will not delete it from the database. If youre after marking the row as deleted so that it will be removed from the DB during the next dataadapter.Update, use myBindingsource.Current.Delete )

 

[jmcilhinney]

(which will remove the current record from the datatable, but it will not delete it from the database. If youre after marking the row as deleted so that it will be removed from the DB during the next dataadapter.Update, use myBindingsource.Current.Delete )

No, that is not correct. The actual result of calling RemoveCurrent on a BindingSource depends on the specific IList implementation that its bound to. If you bound a DataTable to your BindingSource then the specific IList implementation is a DataView. The IList.RemoveAt method implementation of the DataView class will Delete the row at the specified index. Try this:

Dim table As New DataTable
Dim source As New BindingSource

source.DataSource = table

table.Columns.Add("ID", GetType(Integer))

MessageBox.Show(table.Rows.Count.ToString(), "Table Row Count")
MessageBox.Show(source.Count.ToString(), "Source Row Count")

table.Rows.Add(1)

MessageBox.Show(table.Rows.Count.ToString(), "Table Row Count")
MessageBox.Show(source.Count.ToString(), "Source Row Count")
MessageBox.Show(table.Rows(0).RowState.ToString(), "Row State")

table.AcceptChanges()

MessageBox.Show(table.Rows.Count.ToString(), "Table Row Count")
MessageBox.Show(source.Count.ToString(), "Source Row Count")
MessageBox.Show(table.Rows(0).RowState.ToString(), "Row State")

source.RemoveCurrent()

MessageBox.Show(table.Rows.Count.ToString(), "Table Row Count")
MessageBox.Show(source.Count.ToString(), "Source Row Count")
MessageBox.Show(table.Rows(0).RowState.ToString(), "Row State")

The logic behind this is that it's impossible to explicitly remove an item from a DataView. A DataView doesn't contain any data of its own. It's just a view of the data in a DataTable. As such, calling RemoveAt on a DataView can't actually remove the item at the specified index. What does happen is that it calls the Delete method of the item at the specified index, which is functionally equivalent to calling Delete on the corresponding DataRow.

 

[cjard]

Maybe we're talking at crossed purposes; I was supplementing your post by pointing out that:

If the listbox's datasource is a DataTable, and if:
it is the users intention that highlighting a row in the listbox and pressing Delete will (eventually) cause it to be removed from the database, then myBS.Current.Delete should be called
The underlying row will be marked as deleted and the db row deleted when Update is next performed. It will also be removed from the underlying datatable when Update() is performed. (my advice)


it is the users intention that the row should be removed from the listbox without ever removing the row from the database, then RemoveCurrent should be used. The row will be removed from the underlying datatable immediately, and hence will not be available to a data adapter that is performing an Update(), ergo not deleted from the database (your advice)

The logic behind this is that it's impossible to explicitly remove an item from a DataView

True, but MSDN informs us that calling Delete on a DataRowView will mark the underlying row as deleted. It will disappear from the DataTable next time an AcceptChanges is performed, and a dataadapter does this during an Update.

So the OP must choose what to call based on how he wants to affect the database: No affect? Use RemoveCurrent. Affect? Use Current.Delete followed by da.Update(dt)

 

[cjard]

        dr.Delete()

        da.Update(ds, "SyscoPrice")

    End Sub

This is the code I would expect if you plan on removing a row from the DB, but you havent said in what way it doesnt work..

You can use the debgger to help you see the rows before and after every step. Do you know how the use the debugger?

Put a breakpoint on the dr.Delete line
When the code breaks, point to DS (below) and click the magnifying glass in the tooltip. See your dataset? Check the row is in SyscoPrice
Now single step over the Delete() command, and check the DS again - I can't remember if it shows row states or not.. (I think not), neither can I remember if it shows you rows that are rowstate "deleted" but you can use the Immediate window for this.. Either do:

?dr.RowState

or

?ds.Tables("SyscoPrice").GetChanges(RowState.Deleted)

and see if your row is marked as deleted

Now run the Update, and again check to see if your row is
a) still in the table
b) gone from the db

If it's still in the dataset, does your data adapter have AcceptChangesDuringUpdate set to false?
Does the row disappear if you call ds.Tables("SyscoPrice").AcceptChanges()?

 

[jmcilhinney]

Maybe we're talking at crossed purposes; I was supplementing your post by pointing out that:

If the listbox's datasource is a DataTable, and if:
it is the users intention that highlighting a row in the listbox and pressing Delete will (eventually) cause it to be removed from the database, then myBS.Current.Delete should be called
The underlying row will be marked as deleted and the db row deleted when Update is next performed. It will also be removed from the underlying datatable when Update() is performed. (my advice)


it is the users intention that the row should be removed from the listbox without ever removing the row from the database, then RemoveCurrent should be used. The row will be removed from the underlying datatable immediately, and hence will not be available to a data adapter that is performing an Update(), ergo not deleted from the database (your advice)


True, but MSDN informs us that calling Delete on a DataRowView will mark the underlying row as deleted. It will disappear from the DataTable next time an AcceptChanges is performed, and a dataadapter does this during an Update.

So the OP must choose what to call based on how he wants to affect the database: No affect? Use RemoveCurrent. Affect? Use Current.Delete followed by da.Update(dt)

No, you are mistaken. RemoveCurrent does NOT remove the DataRow from the DataTable. It deletes it. You're saying that you need to call Current.Delete because it does something different to RemoveCurrent. It does NOT. They have EXACTLY the same effect. Did you try running the code I posted? If you do then you'll see what I'm saying. After calling RemoveCurrent on the BindingSource the DataRow is still in the DataTable and its RowState is Deleted, just waiting for you to call Update on your DataAdapter.

When you bind to a BindingSource the DataSource must be either an IList or an IListSource, which is just a source for an IList. When you call RemoveCurrent on the BindingSource it calls the RemoveAt method of the underlying IList. The actual result of that depends on how that specific IList object implements the RemoveAt method. The way the DataView implements the IList.RemoveAt method is to call the Delete method of the DataRowView at the specified index.

By the way, you could only actually call Current.Delete on a BindingSource without a cast if Option Strict was Off, which we all know is a bad idea. With Option Strict On it would have to be:

DirectCast(myBindingSource.Current, DataRowView).Delete()

and there's no point writing that code when:

myBindingSource.RemoveCurrent()

has the same effect.

 

[cjard]

By jove; you're right!

That's horrendous; IMHO an incredible oversight to make Remove behave differently (in terms of consequential data loss) in datatable vs a dataview..

Thanks for the heads up.. I also stumbled across an old thread I'd raised on MSDN discussing a related issue. Use of Remove on a datatable itself is implemented as a row .Delete() followed by a row.AcceptChanges. This can have relational consequences because removing a parent can mark children as deleted but under certain circumstances the children don't have AcceptChanges called, so removal of a parent could cause database record deletion..

One more for the list of dataset caveats.. Thanks jmc

 

[Peinecone]

So guys. Is there a way to do this? I have to admit that a lot of this is over my head, but it seems like there should be an easy way around it.

Right now the listbox is set up with a binding source here

       bs.DataSource = ds.Tables("SyscoPrice")
        bs.Sort = "ProductName"
        lstName.DataSource = bs
        lstName.DisplayMember = "ProductName"
        lstName.ValueMember = "ProductCode"

So if I use the bs.RemoveCurrent() function and da.Update(ds) I am able to delete rows that were already in the database when the form loads. But if I create a new item, then try to delete it, I get the error "the DeleteCommand affected 0 of the expected 1 records." If I just use the bs.RemoveCurrent() function, it deletes it from the listbox view, but not the database (whether it is new or not.)

So what happens when I add a new item? Is it added to the listbox dataview and the database, but not the dataset?
Here is the code when I add a new item.

                Dim dr As DataRow = DirectCast(bs.Current, DataRowView).Row
                dr.Item("ProductName") = txtName.Text
                dr.Item("Category") = cboCategory.SelectedValue
                dr.Item("Price") = txtPrice.Text
                dr.Item("UM") = cboUM.SelectedValue
                dr.Item("Units") = txtUnits.Text
                dr.Item("UnitSize") = txtUnitSize.Text
                If cboUnivUM.SelectedValue <> Nothing Then
                    dr.Item("UniversalUM") = cboUnivUM.SelectedValue
                End If
                dr.Item("UnivUnitsPer") = txtUnivPer.Text
                da.Update(ds, "SyscoPrice")

 

[jmcilhinney]

If you call RemoveCurrent on the BindingSource it will call Delete on the current DataRowView. If the RowState of the associated DataRow is Unchanged or Modified, i.e. that record already exists in the database, the RowState will change to Deleted and the record will be deleted from the database when you call Update. If the RowState is Added, i.e. you've just added the row and it doesn't exist in the database yet, the RowState will change to Detached and the row is removed from the DataTable altogether, as if it never existed.

It's really very simple:

1. Populate a DataTable using a DataAdapter.
2. Bind the DataTable to a BindingSource and the BindingSource to your controls.
3. To add a new row call AddNew on the BindingSource to get a DataRowView, set its fields and call EndEdit on the BindingSource to add the row to the DataTable.
4. To edit the current row get a DataRowView from the Current property of the BindingSource and set its fields, then call EndEdit.
5. To delete a row call RemoveCurrent on the BindingSource.
6. When you're ready to save the changes call EndEdit on the BindingSource to ensure any pending changes are committed and then call Update on the DataAdapter and pass the DataTable.

There's rarely a need to get the Row property of a DataRowView because you can simply get and set the field values through the DataRowView in the same way.

 

[jmcilhinney]

If you call RemoveCurrent on the BindingSource it will call Delete on the current DataRowView. If the RowState of the associated DataRow is Unchanged or Modified, i.e. that record already exists in the database, the RowState will change to Deleted and the record will be deleted from the database when you call Update. If the RowState is Added, i.e. you've just added the row and it doesn't exist in the database yet, the RowState will change to Detached and the row is removed from the DataTable altogether, as if it never existed.

It's really very simple:

1. Populate a DataTable using a DataAdapter.
2. Bind the DataTable to a BindingSource and the BindingSource to your controls.
3. To add a new row call AddNew on the BindingSource to get a DataRowView, set its fields and call EndEdit on the BindingSource to add the row to the DataTable.
4. To edit the current row get a DataRowView from the Current property of the BindingSource and set its fields, then call EndEdit.
5. To delete a row call RemoveCurrent on the BindingSource.
6. When you're ready to save the changes call EndEdit on the BindingSource to ensure any pending changes are committed and then call Update on the DataAdapter and pass the DataTable.

There's rarely a need to get the Row property of a DataRowView because you can simply get and set the field values through the DataRowView in the same way.

 

[cjard]

Sorry.. I did do an example project for this a few days ago, but work has been so mental I havent been able to get on to post it. Here it is:

 

[Peinecone]

Thank you very much guys. I was able to have the form do exactly what I need now.