How to remove item(s) from list box and database with a button

PRAISE PHS

Well-known member
Joined
Jun 2, 2011
Messages
58
Programming Experience
Beginner
Hi Forumites,
I'm using vb.net 2008. In my app, I have a button for adding item(s) on a list box and also sending the item(s) to my SQL DB. In my DB, I have a field called Availability with a default value of 1( i.e. to show the item is available). So anytime I add an item, the availability is set to 1. Please my challenge now is that when an item is added on the list box, I want to have a button for removing such item from the list box and also from the database( i.e. setting the availability to 0). Pls kindly help me as I'm a newbie.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,706
Location
Sydney, Australia
Programming Experience
10+
First of all, do you really want to remove it from the database or do you actually want to leave it in the database with an availability of 0? They are two quite different things. I'm guessing that it's the latter.

Secondly, what is the data type of that Availability column in the database? It should be 'bit'. As such, it will use values 0 and 1 in the database but, in your VB code, that data type maps to type Boolean so, as is logical, you'll be using the values True and False in your VB code.

As for your question, it's no different to any other data access. You use a data adapter to Fill a DataTable with the data and then bind that to the ListBox via a BindingSource. You can add and edit the data via the DataTable or the BindingSource, NOT the ListBox directly. If you want to exclude certain rows from the ListBox then you set the Filter property of the BindingSource appropriately, e.g.
myBindingSource.Filter = "Availability = True"
To save the changes, you use the same data adapter to Update the database.
 

PRAISE PHS

Well-known member
Joined
Jun 2, 2011
Messages
58
Programming Experience
Beginner
First of all, do you really want to remove it from the database or do you actually want to leave it in the database with an availability of 0? They are two quite different things. I'm guessing that it's the latter.

Secondly, what is the data type of that Availability column in the database? It should be 'bit'. As such, it will use values 0 and 1 in the database but, in your VB code, that data type maps to type Boolean so, as is logical, you'll be using the values True and False in your VB code.

As for your question, it's no different to any other data access. You use a data adapter to Fill a DataTable with the data and then bind that to the ListBox via a BindingSource. You can add and edit the data via the DataTable or the BindingSource, NOT the ListBox directly. If you want to exclude certain rows from the ListBox then you set the Filter property of the BindingSource appropriately, e.g.
myBindingSource.Filter = "Availability = True"
To save the changes, you use the same data adapter to Update the database.

Thanks a lot Jim for the response. I actually used tinyint as the datatype for my availability and set it default value to 1. I want it that whenever I select an item on the list box and I then click a button having 'remove item' as its text property. The availability of the said selected item should be set to 0 in my DB...Pls kindly help me as I don't really understand the last part of your post and I can't see myBindingSourcce.Filter on my code window(possibly I'm not doing the right thing). Thanks and I look forward to hearing from you soon.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,706
Location
Sydney, Australia
Programming Experience
10+
Then that would be the thing to do. If you haven't added one then you don't have one to use. I thought that I'd specified that in my first post but apparently not, so sorry about that.
 

PRAISE PHS

Well-known member
Joined
Jun 2, 2011
Messages
58
Programming Experience
Beginner
Then that would be the thing to do. If you haven't added one then you don't have one to use. I thought that I'd specified that in my first post but apparently not, so sorry about that.

Thanks Jim for your kind responses. Have done that but it's still not giving me what I want. Though, the selected item is being removed from my list box, but when I select an item to display the ones whose availability is 1, the ones had removed with your code still appear. Signifying that their availability are not set to 0(using bindingforce1.filter = "availability = true"). Even from my DB, the availability is still 1( just to confirm my statement). Also, I don't know how to go about (You use a data adapter to Fill a DataTable with the data and then bind that to the ListBox via a BindingSource),from your first response. Probably thats where I'm getting wrong. pls kindly help me out.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,706
Location
Sydney, Australia
Programming Experience
10+
This thread shows you how to retrieve data from a database and save the changes back again:

Retrieving and Saving Data in Databases

In between, you do as I said previously. Bind the DataTable to a BindingSource and bind that to the ListBox, setting the Filter as instructed. You can get the selected record as a DataRowView from the Current property of the BindingSource. Just as with a DataRow, you can get or set a field in a DataRowView by column name or ordinal. When you change the value of the Availability column, the record will disappear from the ListBox courtesy of the BindingSource Filter.
 

PRAISE PHS

Well-known member
Joined
Jun 2, 2011
Messages
58
Programming Experience
Beginner
Thanks Jim. But, I'm still having issues using the sourcebinding and listbox. Below is what I have in my code :
VB.NET:
Adapter = New SqlDataAdapter("SELECT * FROM ItemSerialNo", MyConn)

            scb = New SqlCommandBuilder(Adapter)
            ds = New DataSet

            Adapter.Fill(ds, "ItemSerialNo")

            Dim DT As New DataTable

            BindingSource1.DataSource = (ds.Tables("IteSerialNo"))
            lstSerialNo.DataSource = BindingSource1

            BindingSource1.Filter = "Availability = False"

            Adapter.Update(ds,"ItemSerialNo")


When I click on the Remove Item Button to execute the above code after selecting an item on the list box, it clears all the list box items including the ones not selected. Also the items return back after ending the app and running it again. Pls what do you thing I can do. Thanks
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,706
Location
Sydney, Australia
Programming Experience
10+
Thanks Jim. But, I'm still having issues using the sourcebinding and listbox. Below is what I have in my code :
VB.NET:
Adapter = New SqlDataAdapter("SELECT * FROM ItemSerialNo", MyConn)

            scb = New SqlCommandBuilder(Adapter)
            ds = New DataSet

            Adapter.Fill(ds, "ItemSerialNo")

            Dim DT As New DataTable

            BindingSource1.DataSource = (ds.Tables("IteSerialNo"))
            lstSerialNo.DataSource = BindingSource1

            BindingSource1.Filter = "Availability = False"

            Adapter.Update(ds,"ItemSerialNo")


When I click on the Remove Item Button to execute the above code after selecting an item on the list box, it clears all the list box items including the ones not selected. Also the items return back after ending the app and running it again. Pls what do you thing I can do. Thanks

Why would you execute the above code when you click a Button to remove an item? That makes no sense. That code is retrieving data from the database. Surely you have to already have retrieved data from the database if you expect to remove an item from the ListBox. Did you read that thread of mine I provide a link to? Did you notice how the retrieving and saving of the data is completely separate?

Think about it. You retrieve the data and bind it when the form opens. That is the one and only time you do that and it includes setting the filter. Removing an item takes one or two lines of code, i.e. get the current item and set the appropriate field. That's it; no retrieving data, binding or saving changes. When you're done editing the data, then you save all the changes in a single batch.

That thread provides all you need to retrieve and save the data. All you have to do is add a couple of lines to bind and a couple of lines to remove an item.

Also, why are you creating a DataTable that you don't use? Either get rid of the DataSet and use the DataTable you're creating or get rid of the DataTable. I suggest the former and, again, you simply have to follow what I've already provided.
 
Top Bottom