step 1: Create Following two tables in sql Server:
CREATE TABLE DEPT1(DEPTID INT IDENTITY(1,1) primary key,DEPTNAME VARCHAR(30))
CREATE TABLE EMP1(EMPID INT IDENTITY(101,1),EMPNAME VARCHAR(30),EMPJOB VARCHAR(30),EMPSAL MONEY, deptid int foreign key references dept1(deptid) )
Step 2: Create a web page named it as("eg1.aspx") and write the following codes:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="eg1.aspx.cs" Inherits="eg1" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script src="Scripts/jQuery-3.2.1.min.js"></script>
<script>
$(document).ready(function () {
function loadData() {
var deptid = $("#ddldept option:selected").val();
$.ajax({
type: "POST",
url: "ViewEmp.aspx",
data:{"deptid":deptid},
datatple: "html",
success: function (data) {
$("#panel1").html(data);
},
error: function (error) { alert("Loading Failed") }
});
}
loadData();
$("#ddldept").change(function () {
loadData();
});
});
</script>
</head>
<body>
<form id="form1" runat="server">
<div align="center">
<b>Deptname:</b>
<asp:DropDownList ID="ddldept" runat="server" />
<br />
<asp:Panel ID="panel1" runat="server" ></asp:Panel>
</div>
</form>
</body>
</html>
CREATE TABLE DEPT1(DEPTID INT IDENTITY(1,1) primary key,DEPTNAME VARCHAR(30))
CREATE TABLE EMP1(EMPID INT IDENTITY(101,1),EMPNAME VARCHAR(30),EMPJOB VARCHAR(30),EMPSAL MONEY, deptid int foreign key references dept1(deptid) )
Step 2: Create a web page named it as("eg1.aspx") and write the following codes:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="eg1.aspx.cs" Inherits="eg1" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script src="Scripts/jQuery-3.2.1.min.js"></script>
<script>
$(document).ready(function () {
function loadData() {
var deptid = $("#ddldept option:selected").val();
$.ajax({
type: "POST",
url: "ViewEmp.aspx",
data:{"deptid":deptid},
datatple: "html",
success: function (data) {
$("#panel1").html(data);
},
error: function (error) { alert("Loading Failed") }
});
}
loadData();
$("#ddldept").change(function () {
loadData();
});
});
</script>
</head>
<body>
<form id="form1" runat="server">
<div align="center">
<b>Deptname:</b>
<asp:DropDownList ID="ddldept" runat="server" />
<br />
<asp:Panel ID="panel1" runat="server" ></asp:Panel>
</div>
</form>
</body>
</html>
Step 3: Write the following code in code behind file:
using System.Data;
using System.Data.SqlClient;
public partial class eg1 : System.Web.UI.Page
{
SqlConnection con = null;
SqlDataReader dr = null;
SqlDataAdapter da = null;
DataSet ds = null;
protected void Page_Load(object sender, EventArgs e)
{
con = new SqlConnection("Data Source=MANOJ\\SQLEXPRESS;Database=quiz;Integrated Security=SSPI");
if(!IsPostBack)
{
bindddl();
}
}
private void bindddl()
{
string sqlquery = "select *from dept1";
da = new SqlDataAdapter(sqlquery, con);
ds = new DataSet();
da.Fill(ds,"dept1");
ddldept.DataSource = ds.Tables["dept1"];
ddldept.DataTextField = "deptname";
ddldept.DataValueField = "deptid";
ddldept.DataBind();
ddldept.Items.Insert(0,new ListItem( "---Select Department---","0"));
}
}
Step 4: Create another web page name it as("ViewEmp.aspx") and write the following code:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ViewEmp.aspx.cs" Inherits="ViewEmp" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="gview1" runat="server" Width="100%" Caption="Employee Data" EmptyDataText="No Employee Data Available for this selected dept" EmptyDataRowStyle-ForeColor="Red" BackColor="White" BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Horizontal">
<AlternatingRowStyle BackColor="#F7F7F7" />
<EmptyDataRowStyle ForeColor="Red"></EmptyDataRowStyle>
<FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />
<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />
<PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" HorizontalAlign="Right" />
<RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="#F7F7F7" />
<SortedAscendingCellStyle BackColor="#F4F4FD" />
<SortedAscendingHeaderStyle BackColor="#5A4C9D" />
<SortedDescendingCellStyle BackColor="#D8D8F0" />
<SortedDescendingHeaderStyle BackColor="#3E3277" />
</asp:GridView>
</div>
</form>
</body>
</html>
Step 5:Write the following code in code behind file in("ViewEmp.aspx.cs")
using System.Data;
using System.Data.SqlClient;
public partial class ViewEmp : System.Web.UI.Page
{
SqlConnection con = null;
SqlDataAdapter da = null;
DataSet ds = null;
string sqlquery = null;
protected void Page_Load(object sender, EventArgs e)
{
if (Request.Form["deptid"] == null)
Response.Redirect("eg1.aspx");
con = new SqlConnection("Data Source=MANOJ\\SQLEXPRESS;Database=quiz;Integrated Security=SSPI");
BindEmpData();
}
private void BindEmpData()
{
int deptid = int.Parse(Request.Form["deptid"].ToString());
if (deptid == 0)
sqlquery = "select e.empid,e.empname,e.empjob,e.empsal,d.deptname from emp1 e,dept1 d where e.deptid=d.deptid";
else
sqlquery = "select e.empid,e.empname,e.empjob,e.empsal,d.deptname from emp1 e,dept1 d where e.deptid=d.deptid and e.deptid=" + deptid;
da = new SqlDataAdapter(sqlquery, con);
ds = new DataSet();
da.Fill(ds, "emp");
gview1.DataSource = ds.Tables["emp"];
gview1.DataBind();
}
}
