How to Bind SQL Server Stored Procedure Data into ListBox in Asp.Net C# VB.Net

When we Google Bind data into ListBox we found a lot of code example focused on SQL Server table data. But more often we need to Bind Data from SQL Server stored procedure. That’s why in this article I am going to explain How one can Bind ListBox with SQL Server Stored Procedure.

Related Post:
How to Bind SQL Server Parameterized Stored Procedure Data into ListBox in Asp.Net C# VB.Net


To Do the Example We need to create a Database Table First:

CREATE TABLE [dbo].[Article](
    [ArticleID] [int] NOT NULL,
    [Title] [varchar](max) NOT NULL,
    [IsPublished] [bit] NOT NULL,
([ArticleID] ASC)

Now we need to Insert some Data:

INSERT INTO ARTICLE VALUES(1,'Populate SQL Data Into Checkboxlist',1)
INSERT INTO ARTICLE VALUES(2,'Checkboxlist data population example',0)
INSERT INTO ARTICLE VALUES(3,'C# Checkboxlist lesson',1)
INSERT INTO ARTICLE VALUES(4,'VB.Net Checkboxlist Example',0)
INSERT INTO ARTICLE VALUES(5,' Checkboxlist load data',0)

Now Create the SQL Server Stored Procedure:

CREATE PROCEDURE [dbo].[GetArticles]
	SELECT * FROM Article;

Now Modify the Web.Config file to prepare Connection String:

    <add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Test;Trusted_Connection=yes;" providerName="System.Data.SqlClient"/>
    <!--<add name="BONConnection" connectionString="Data;Initial Catalog=DBNAME;User Id=UserName;Password=YourPassword;" providerName="System.Data.SqlClient" />-->

Note: If you use windows authentication then use the first key. Otherwise use second key line. Update server name, Database name, User name, Password as per your settings.

Now Add a ListBox Like Below:

<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <asp:ListBox ID="ListBox1" runat="server" SelectionMode="Multiple"></asp:ListBox>

Bind SQL Server Stored Procedure Data into ListBox Control:
C# Code:

    protected void Page_Load(object sender, EventArgs e)
        if (!IsPostBack)
            string sConstr = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
            SqlConnection Conn = new SqlConnection(sConstr);

            using (Conn)
                SqlCommand command = new SqlCommand("dbo.GetArticles", Conn);
                SqlDataAdapter da = new SqlDataAdapter();
                DataTable dt = new DataTable();

                command.CommandType = CommandType.StoredProcedure;
                da.SelectCommand = command;

                ListBox1.DataSource = dt;
                ListBox1.DataValueField = "ArticleID";
                ListBox1.DataTextField = "Title";

Note: Don’t forget to add “System.Data”, “System.Data.SqlClient”, “System.Configuration” namespaces.

VB.Net Code:

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Dim sConstr As String = ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString
            Dim Conn As New SqlConnection(sConstr)

            Using Conn
                Dim command As New SqlCommand("dbo.GetArticles", Conn)
                Dim da As New SqlDataAdapter()
                Dim dt As New DataTable()

                command.CommandType = CommandType.StoredProcedure
                da.SelectCommand = command

                ListBox1.DataSource = dt
                ListBox1.DataValueField = "ArticleID"
                ListBox1.DataTextField = "Title"
            End Using
        End If

    End Sub

Note: Don’t forget to add “System.Data”, “System.Data.SqlClient”, “System.Configuration” namespaces.

Hope now you can Bind SQL Server Stored Procedure or SP into a ListBox Control easily.

Download Code Example C#        Download Code Example VB.Net

Posted in .Net,, C#, ListBox, VB.Net

Leave a Reply

Your email address will not be published. Required fields are marked *