In this article I am going to show that how we can fetch data in a DropDownList from Sql server. Here I used a DropDownList in which on the load of form the Name value will come from a record table.
In fetching data from database in a DropDownList we have to set a property of DropDownList like as.
DroplistData.DataTextField = "Name";
In DropDownList we can fetch only one column value at a time.
Firstly I designed a form where I used a DopDownList. In which all value of one selected column will come. I also used a button and a DataGrid here. On clicking of button the complete record will show in DataGrid of that value which user select from DropDownList.
DropDownListIndataAccess.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" Debug="true" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>DropDownListIn Data accessing</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table cellpadding="0" cellspacing="0" border="4" width="50%" align="center">
<tr><td>
<asp:DropDownList ID="DroplistData" runat="server" Width="200px" >
</asp:DropDownList>
</td></tr>
<tr><td><table>
<tr><td> </td></tr>
<tr><td>
<asp:Button ID="ShowDataGrid" runat="server" Text="Click Here To Show The Full record of Selected Name " OnClick="GridShow_Click" />
</td></tr>
</table></td></tr>
<tr><td> <asp:DataGrid ID="datagrid" runat="server"></asp:DataGrid></td></tr>
</table>
</div>
</form>
</body>
</html>
DropDownListIndataAccess.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
SqlConnection con;
SqlCommand cmd=new SqlCommand();
SqlDataAdapter da;
DataSet ds = new DataSet();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
rebind();
}
}
public void rebind()
{
try
{
con = new SqlConnection("Data Source=MCN0100;Initial Catalog = DropDowndataBase; Uid=sa; Pwd=");
cmd.CommandText = "select * from Record";
cmd.Connection = con;
con.Open();
DroplistData.DataSource = cmd.ExecuteReader();
DroplistData.DataTextField = "Name";
DroplistData.DataBind();
}
catch (Exception ex)
{
Response.Write(ex.StackTrace);
}
}
public void datashow()
{
con = new SqlConnection("Data Source=MCN0100;Initial Catalog = DropDowndataBase; Uid=sa; Pwd=");
string nameshow = DroplistData.Text.ToString().Trim();
cmd.CommandText = "select * from Record where Name='" + nameshow + "' ";
cmd.Connection = con;
da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Open();
cmd.ExecuteNonQuery();
datagrid.DataSource = ds;
datagrid.DataBind();
con.Close();
}
protected void GridShow_Click(object sender, EventArgs e)
{
datashow();
}
}
When user will run the application then the window will look like this:
Figure 1: Here all name are coming in DropDownList.
Figure 2: If I select Aaryan and click on button then complete record of Aaryan comes in DataGrid.