In this article I will explain with an example, how to set ListBox selected value from
SQL Server database in ASP.Net using C# and VB.Net.
Database
I have made use of the following table Hobbies with the schema as follow.
I have already inserted few records in the table.
Note: You can download the database table SQL by clicking the download link below.
HTML Markup
The HTML Markup consists of following controls:
ListBox – For displaying selected and unselected item from database.
The ListBox has been assigned with the SelectionMode property set to Multiple.
Hobbies:<br/>
<asp:ListBox ID="lstHobbies" runat="server" SelectionMode="Multiple"></asp:ListBox>
Namespaces
You will need to import the following namespaces.
C#
using System.Data.SqlClient;
using System.Configuration;
VB.Net
Imports System.Data.SqlClient
Imports System.Configuration
Setting the ListBox selected value from SQL Server database using C# and VB.Net
Inside the Page_Load event handler, first the connection is read from Web.Config file.
Then, a connection to the database is established using the SqlConnection class.
Then, using ExecuteReader method, the records are fetched from the Hobbies Table and set to the respective properties of ListItem class object.
Finally, the ListItem class object is added to the ListBox and the ListBox with selected value is populated.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
string sql = "SELECT HobbyId, Hobby, IsSelected FROM Hobbies";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
ListItem item = new ListItem();
// Set the Text.
item.Text = sdr["Hobby"].ToString();
// Set the Value.
item.Value = sdr["HobbyId"].ToString();
// Set the Selected value.
item.Selected = Convert.ToBoolean(sdr["IsSelected"]);
lstHobbies.Items.Add(item);
}
}
con.Close();
}
}
}
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Dim sql As String = "SELECT HobbyId, Hobby, IsSelected FROM Hobbies"
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
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()
Dim item As ListItem = New ListItem()
' Set the Text.
item.Text = sdr("Hobby").ToString()
' Set the Value.
item.Value = sdr("HobbyId").ToString()
' Set the Selected value.
item.Selected = Convert.ToBoolean(sdr("IsSelected"))
lstHobbies.Items.Add(item)
End While
End Using
con.Close()
End Using
End Using
End If
End Sub
Screenshot
Demo
Downloads