Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

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



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



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

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

No comments:

Post a Comment

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.