Sql server, .net and c# video tutorial: Display data from two or more database table columns in asp.net dropdownlist

Suggested Videos
Part 169 - Save image to database using asp.net
Part 170 - Load image from database in asp.net
Part 171 - How to show images from database in gridview in asp.net

In this video 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
asp.net dropdownlist display two columns

asp.net dropdownlist display two columns

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.
Display multiple columns in dropdownlist asp.net

Display multiple columns in dropdownlist asp.net

Step 1 : Create tblRates table and populate it with test data using the SQL Script below

Create Database SampleDB

Use SampleDB

Create table tblRates

(

Id int identity primary key,

Currency nvarchar(5),

Rate decimal(10,2)

)

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)

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

<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>

</head>

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);

}

Display data from two or more database table columns in asp.net dropdownlist

1 comment: