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

In many cases we need to collect information from SQL Server Stored Procedure with some business condition. For such situations we need to pass dynamic parameter into the SQL Server Stored Procedure to get desired output. In my previous example I have described How one can Bind ListBox with SQL Server Stored Procedure. But in this article I will Explain How one can Bind ListBox with Parameterized SQL Server Stored Procedure.

How to Bind SQL Server Parametrized Stored Procedure Data into ListBox in Asp.Net Csharp 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',1)
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].[GetPublishedArticles] 
@bPublished bit
	SELECT * FROM Article WHERE IsPublished=@bPublished;

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 Parameterized 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.GetPublishedArticles", Conn);
                command.Parameters.Add(new SqlParameter("@bPublished", SqlDbType.Bit));
                command.Parameters["@bPublished"].Value = 1;
                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.GetPublishedArticles", Conn)
                command.Parameters.Add(New SqlParameter("@bPublished", SqlDbType.Bit))
                command.Parameters("@bPublished").Value = 1
                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 even the SP needs SQL Server Parameter 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 *