Step-by-Step Implementation
Configuring Web.Config
First, add the database connection string inside web.config.
<configuration>
<connectionStrings>
<add name="DbConstr" connectionString="Data Source=SERVERNAME;Initial Catalog=SampleDB;Timeout=600;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
</configuration>
Creating a Stored Procedure
Create a stored procedure in SQL Server to fetch customer records.
CREATE PROCEDURE [dbo].[sp_Customers_List]
AS
BEGIN
SET NOCOUNT ON;
SELECT
CustomerID as 'CustomerID',
CustomerName as 'CustomerName'
FROM
Customers
ORDER BY
CustomerNameASC;
END;
GO
This procedure returns CustomerID and CustomerName for binding to the dropdown.
Creating an ASP.NET Web Form
Add a DropDownList in your ASP.NET WebForm (WebForm1.aspx).
<div class="form">
<div class="form_controls_group">
<div class="item1">
<label>Customer Name</label>
</div>
<div class="item1">
<asp:DropDownList ID="DdlCustomerName" runat="server" Width="200px"></asp:DropDownList>
</div>
<div class="item1">
<asp:CompareValidator ID="CompareValidator2" runat="server"
ControlToValidate="DdlCustomerName" Display="Dynamic"
Operator="NotEqual" ValueToCompare="0"
ErrorMessage="Please select a Customer name."
CssClass="validation" SetFocusOnError="True"></asp:CompareValidator>
</div>
</div>
<div class="form_controls_group">
<div class="item1">
<asp:Button ID="BtnSubmit" runat="server" Text="Submit" />
</div>
</div>
</div>
VB.NET Code to Bind Data
Use the following VB.NET code-behind to bind data from the stored procedure.
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Public Class WebForm1
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not IsPostBack Then
BindData_DdlCustomerName()
End If
End Sub
Private Sub BindData_DdlCustomerName()
Try
Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("DbConstr").ConnectionString)
Using cmd As New SqlCommand("sp_Customers_List", conn)
cmd.CommandType = CommandType.StoredProcedure
Dim dt As New DataTable()
Using sda As New SqlDataAdapter(cmd)
conn.Open()
sda.Fill(dt)
End Using
DdlCustomerName.Items.Clear()
If dt.Rows.Count > 0 Then
DdlCustomerName.DataSource = dt
DdlCustomerName.DataTextField = "CustomerName"
DdlCustomerName.DataValueField = "CustomerID"
DdlCustomerName.DataBind()
End If
DdlCustomerName.Items.Insert(0, New ListItem("-- Select --", "0"))
DdlCustomerName.SelectedIndex = 0
End Using
End Using
Catch ex As Exception
ShowAlert($"Error loading customers: {ex.Message}")
End Try
End Sub
Private Sub ShowAlert(message As String)
message = message.Replace("'", "\'")
ScriptManager.RegisterStartupScript(Me, Me.GetType(), "ShowAlert", $"alert('{message}');", True)
End Sub
End Class
How It Works
- Connection → The connection string from Web.config establishes a link to SQL Server.
- Stored Procedure Execution → sp_Customers_List fetches customer data.
- Binding Data → Results are stored in a DataTable, then bound to the DropDownList.
- Default Option → A placeholder (-- Select --) ensures the user makes a valid selection.
- Validation → CompareValidator prevents form submission if no customer is selected.
Frequently Asked Questions (FAQ)
1. Why is my DropDownList empty?
- Verify the connection string in Web.config.
- Check that the stored procedure exists and returns data.
- Use Try-Catch to log SQL or binding errors.
2. Can I filter customers dynamically?
- Yes. Modify the stored procedure to accept parameters (e.g., filter by city).
3. How to handle large datasets?
- Use paging or AJAX calls instead of loading thousands of records at once.
4. Can I use Entity Framework instead of ADO.NET?
- Yes. Use:
- dbContext.Database.SqlQuery(Of Customer)("EXEC sp_Customers_List").ToList()
Conclusion
Binding MSSQL Stored Procedure data to a DropDownList in ASP.NET (VB.NET) is straightforward and powerful. It improves performance, enhances data security, and ensures a seamless user experience.
By setting up your connection string, stored procedure, WebForm, and VB.NET code properly, you can fetch data dynamically into your dropdown with minimal effort.
Use this approach whenever you need to bind data to DropDownList efficiently in ASP.NET and VB.NET applications.