In this article I will explain with an example, how to save (insert) dynamic DropDownList Selected Value to SQL Server database table in ASP.Net using C# and VB.Net.
This article will also explain how to populate (bind) dynamic DropDownLists from SQL Server database table in ASP.Net using C# and VB.Net.
Database
Tables for populating data
I have made use of the following table Customers with the schema as follows.
![Save (Insert) dynamic DropDownList Selected Value to database in ASP.Net using C# and VB.Net]()
I have already inserted few records in the table.
![Save (Insert) dynamic DropDownList Selected Value to database in ASP.Net using C# and VB.Net]()
Note: You can download the database table SQL by clicking the download link below.
Table for saving data
I have made use of the following table Names with the schema as follows.
![Save (Insert) dynamic DropDownList Selected Value to database in ASP.Net using C# and VB.Net]()
Note: You can download the database table SQL by clicking the download link below.
HTML Markup
The HTML Markup consists of an ASP.Net Panel control and two Buttons, one for adding the dynamic DropDownLists and other one for saving the values of the dynamically generated DropDownLists to the SQL Server database.
<asp:Panel ID="pnlDropDownLists" runat="server">
</asp:Panel>
<hr />
<asp:Button ID="btnAdd" runat="server" Text="Add New" OnClick="AddDropDownList" />
<asp:Button ID="btnSave" runat="server" Text="Save" OnClick="Save" />
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
VB.Net
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Dynamically creating and adding DropDownLists on Button Click
When the Add Button is clicked, first the count of all the DropDownLists present in the Panel control is determined and then it is incremented to generate an Index which will be used for generating a unique ID for the DropDownList control.
The unique ID is passed to the CreateDropDownList method, which creates a dynamic DropDownList, populates it from database and appends it to the Panel control.
Note: It is very important to give a common prefix (example ddlDynamic) for all DropDownLists as it will be used to find and recreate all dynamic DropDownLists on PostBack.
C#
protected void AddDropDownList(object sender, EventArgs e)
{
int index = pnlDropDownLists.Controls.OfType<DropDownList>().ToList().Count + 1;
this.CreateDropDownList("ddlDynamic" + index);
}
private void CreateDropDownList(string id)
{
DropDownList dropDown = new DropDownList();
dropDown.ID = id;
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT CustomerId, Name FROM Customers"))
{
cmd.Connection = con;
con.Open();
dropDown.DataSource = cmd.ExecuteReader();
dropDown.DataTextField = "Name";
dropDown.DataValueField = "CustomerId";
dropDown.DataBind();
con.Close();
}
}
pnlDropDownLists.Controls.Add(dropDown);
Literal lt = new Literal();
lt.Text = "<br />";
pnlDropDownLists.Controls.Add(lt);
}
VB.Net
Protected Sub AddDropDownList(ByVal sender As Object, ByVal e As EventArgs)
Dim index As Integer = pnlDropDownLists.Controls.OfType(Of DropDownList)().ToList().Count + 1
Me.CreateDropDownList("ddlDynamic" & index)
End Sub
Private Sub CreateDropDownList(ByVal id As String)
Dim dropDown As DropDownList = New DropDownList()
dropDown.ID = id
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand("SELECT CustomerId, Name FROM Customers")
cmd.Connection = con
con.Open()
dropDown.DataSource = cmd.ExecuteReader()
dropDown.DataTextField = "Name"
dropDown.DataValueField = "CustomerId"
dropDown.DataBind()
con.Close()
End Using
End Using
pnlDropDownLists.Controls.Add(dropDown)
Dim lt As Literal = New Literal()
lt.Text = "<br />"
pnlDropDownLists.Controls.Add(lt)
End Sub
Retaining the dynamic DropDownLists on PostBack
In order to retain the dynamic DropDownLists across PostBacks, we need to make use of Page’s PreInit event to recreate the dynamic DropDownLists using the Request.Form collection.
First all the keys containing the string ddlDynamic are fetched and then for each key the CreateDropDownList method is called.
C#
protected void Page_PreInit(object sender, EventArgs e)
{
if (this.IsPostBack)
{
List<string> keys = Request.Form.AllKeys.Where(key => key.Contains("ddlDynamic")).ToList();
int i = 1;
foreach (string key in keys)
{
this.CreateDropDownList("ddlDynamic" + i);
i++;
}
}
}
VB.Net
Protected Sub Page_PreInit(ByVal sender As Object, ByVal e As EventArgs) Handles Me.PreInit
If Me.IsPostBack Then
Dim keys As List(Of String) = Request.Form.AllKeys.Where(Function(key) key.Contains("ddlDynamic")).ToList()
Dim i As Integer = 1
For Each key As String In keys
Me.CreateDropDownList("ddlDynamic" & i)
i += 1
Next
End If
End Sub
Saving values of dynamically created DropDownLists to database in ASP.Net
When the Save Button is clicked, a loop is executed over all the DropDownLists present inside the Panel control. Inside the loop, the selected value of each DropDownList is fetched and inserted into SQL Server database table.
C#
protected void Save(object sender, EventArgs e)
{
foreach (DropDownList dropDown in pnlDropDownLists.Controls.OfType<DropDownList>())
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO Names(Name) VALUES(@Name)"))
{
cmd.Connection = con;
cmd.Parameters.AddWithValue("@Name", dropDown.SelectedItem.Text);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
}
VB.Net
Protected Sub Save(ByVal sender As Object, ByVal e As EventArgs)
For Each dropDown As DropDownList In pnlDropDownLists.Controls.OfType(Of DropDownList)()
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand("INSERT INTO Names(Name) VALUES(@Name)")
cmd.Connection = con
cmd.Parameters.AddWithValue("@Name", dropDown.SelectedItem.Text)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
Next
End Sub
Screenshots
Dynamic DropDownLists
![Save (Insert) dynamic DropDownList Selected Value to database in ASP.Net using C# and VB.Net]()
Values of dynamic DropDownLists saved in database Table
![Save (Insert) dynamic DropDownList Selected Value to database in ASP.Net using C# and VB.Net]()
Downloads
