In this tutorial we will discuss displaying data from two or more database table columns in an ASP.NET Dropdownlist
We will use the following table tblRates for this demo
Retrieve data from the database table and display it in the dropdownlist as shown below. Data from the Currency and Rate columns must be concatenated and displayed in the dropdownlist.
Step 1 : Create tblRates table and populate it with test data using the SQL Script below
Please note : You can find the $ and £ pound symbols on the keyborad. To get € (Euro) and ? (Indian Rupee) symbols use the following SELECT statements in SQL Server Management studio
Step 2 : Create a blank ASP.NET Web Application project. Name it Demo.
Step 3 : Include the following connection string in Web.config file
Step 4 : Add a WebForm to the project. Name it WebForm1.aspx
Step 5 : Drag and Drop a Dropdownlist on the WebForm. Set the ID of the dropdownlist to ddlPrice. The HTML in the WebForm at this point should be as shown below.
Step 6 : Copy and paste the following code in the code-behind file
Create Database SampleDB
Go
Use SampleDB
Go
Create table tblRates
(
Id int identity primary key,
Currency nvarchar(5),
Rate decimal(10,2)
)
Go
Insert into tblRates values ('$', 20)
Insert into tblRates values ('€', 18.33)
Insert into tblRates values ('£', 16.07)
-- Do not forget to include N before the Indian Rupee symbol
-- Otherwise when you select the data you get ? instead of ? symbol
Insert into tblRates values (N'?', 1330.39)
Go
Please note : You can find the $ and £ pound symbols on the keyborad. To get € (Euro) and ? (Indian Rupee) symbols use the following SELECT statements in SQL Server Management studio
Select CHAR(128)
Select NCHAR(8377)
Step 2 : Create a blank ASP.NET Web Application project. Name it Demo.
Step 3 : Include the following connection string in Web.config file
<connectionStrings>
<add name="SampleDB"
connectionString="server=localhost;database=SampleDB;Integrated Security=True;"
providerName="System.Data.SqlClient" />
</connectionStrings>
Step 4 : Add a WebForm to the project. Name it WebForm1.aspx
Step 5 : Drag and Drop a Dropdownlist on the WebForm. Set the ID of the dropdownlist to ddlPrice. The HTML in the WebForm at this point should be as shown below.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs"
Inherits="Demo.WebForm1" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body style="font-family: Arial">
<form id="form1" runat="server">
Price :
<asp:DropDownList ID="ddlPrice" runat="server"></asp:DropDownList>
</form>
</body>
</html>
Step 6 : Copy and paste the following code in the code-behind file
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;
namespace Demo
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindDataSetData();
}
}
private void BindDataSetData()
{
string cs = ConfigurationManager
.ConnectionStrings["SampleDB"].ConnectionString;
SqlConnection con = new SqlConnection(cs);
SqlDataAdapter da = new SqlDataAdapter("Select * from tblRates", con);
DataSet ds = new DataSet();
da.Fill(ds);
ds.Tables[0].Columns
.Add("CurrencyAndRate", typeof(string), "Currency + ' ' + Rate");
ddlPrice.DataTextField = "CurrencyAndRate";
ddlPrice.DataValueField = "Id";
ddlPrice.DataSource = ds;
ddlPrice.DataBind();
}
private void BindDataReaderData()
{
string cs = ConfigurationManager
.ConnectionStrings["SampleDB"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("Select * from tblRates", con);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
ListItem li = new ListItem(rdr["Currency"] + " " #
+ rdr["Rate"], rdr["Id"].ToString());
ddlPrice.Items.Add(li);
}
}
}
}
}