In this article I will explain with an example, how to update Database Table using ListBox in Windows Forms (WinForms) Application using C# and VB.Net.
Database
I have made use of the following table Hobbies with the schema as follow.
![Update ListBox selected values into Database in C# and VB.Net]()
I have already inserted few records in the table.
![Update ListBox selected values into Database in C# and VB.Net]()
Note: You can download the database table SQL by clicking the download link below.
Form Design
The following form consists of:
Label – For displaying text.
ListBox – For capturing user input.
The ListBox has been assigned with a SelectionMode property to MultiSimple for allowing multiple item selection.
![Update ListBox selected values into Database in C# and VB.Net]()
Button – For updating database table.
The Button has been assigned with a Click event handler.
![Update ListBox selected values into Database in C# and VB.Net]()
Populating ListBox with records using C# and VB.Net
Inside the Form_Load event handler, first a Generic List collection of ListItem class is created.
Then, the connection string is read from App.Config file.
After that, a connection to the database is established using the SqlConnection class and records are fetched from the Hobbies Table using ExecuteReader method of the SqlCommand class.
The fetched records are added to the Generic List collection of ListItem class created earlier which is assigned to the DataSource property of the ListBox.
C#
private void Form1_Load(object sender, EventArgs e)
{
List<ListItem> hobbies = new List<ListItem>();
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string sql = "SELECT HobbyId, Hobby FROM Hobbies";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
hobbies.Add(new ListItem(sdr["Hobby"].ToString(),sdr["HobbyId"].ToString()));
}
}
con.Close();
lstHobbies.DataSource = hobbies;
}
}
}
VB.Net
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim hobbies As List(Of ListItem) = New List(Of ListItem)()
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim sql As String = "SELECT HobbyId, Hobby FROM Hobbies"
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand(sql, con)
con.Open()
Using sdr As SqlDataReader = cmd.ExecuteReader()
While sdr.Read()
hobbies.Add(New ListItem(sdr("Hobby").ToString(),sdr("HobbyId").ToString()))
End While
End Using
con.Close()
lstHobbies.DataSource = hobbies
End Using
End Using
End Sub
Updating Database Table using ListBox in C# and VB.Net
When the Update Button is clicked, the HobbyId of selected item i.e. Hobbies are determined using LINQ syntax and stored into a Generic List collection of string.
Then, a FOR EACH loop is executed over the ListBox items inside which UpdateHobby method is called which accepts the value i.e. HobbyId of the item and the status of that item whether it is selected or not.
Note: The status is determined based on the presence of that item value i.e. HobbyId in the Generic List collection of string created earlier.
UpdateHobby
Inside this method, a connection to the database is established using the SqlConnection class.
Then, the HobbyId and IsSelected value i.e. TRUE or FALSE are passed as parameter to the SqlCommand class object and the records are updated using ExecuteNonQuery method.
Finally, a success message is displayed using MessageBox.
C#
private void OnUpdate(object sender, EventArgs e)
{
// Selected Hobby Ids.
List<string> selectedHobbyIds = (from selectedItem in lstHobbies.SelectedItems.Cast< ListItem>()
select selectedItem.Value).ToList();
foreach (ListItem item in lstHobbies.Items)
{
// Update Hobby.
this.UpdateHobby(int.Parse(item.Value), selectedHobbyIds.Contains(item.Value));
}
MessageBox.Show("Records successfully updated!");
}
private void UpdateHobby(int hobbyId, bool isSelected)
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string sql = "UPDATE Hobbies SET IsSelected = @IsSelected WHERE HobbyId = @HobbyId";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("@HobbyId", hobbyId);
cmd.Parameters.AddWithValue("@IsSelected", isSelected);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
VB.Net
Private Sub OnUpdate(sender As Object, e As EventArgs) Handles btnSubmit.Click
' Selected Hobby Ids.
Dim selectedHobbyIds As List(Of String) = (From selectedItem In lstHobbies.SelectedItems.Cast(Of ListItem)()
Select selectedItem.Value).ToList()
For Each item As ListItem In lstHobbies.Items
' Update Hobby.
Me.UpdateHobby(Integer.Parse(item.Value), selectedHobbyIds.Contains(item.Value))
Next
MessageBox.Show("Records successfully updated!")
End Sub
Private Sub UpdateHobby(hobbyId As Integer, isSelected As Boolean)
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim sql As String = "UPDATE Hobbies SET IsSelected = @IsSelected WHERE HobbyId = @HobbyId"
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand(sql, con)
cmd.Parameters.AddWithValue("@HobbyId", hobbyId)
cmd.Parameters.AddWithValue("@IsSelected", isSelected)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
End Sub
Screenshots
Form
![Update ListBox selected values into Database in C# and VB.Net]()
Records before update
![Update ListBox selected values into Database in C# and VB.Net]()
Records after update
![Update ListBox selected values into Database in C# and VB.Net]()
Downloads
