I want add cascade drop down list in my project and by using stored procedure want to bind data in cascade drop down in asp .Net In three tier architecture project
I have two table Department and Designation and I am retrieveing data by using stored procedure and bind department table data to department drop down list. Now when I select any Department Then Cascade (second) drop down should show respective designation related to that department eg i select IT Depart from first Drop down list then second drop down list should show (Tester,.Net Developer,Java Developer)
First DropDownList is Showing All values but Second DropDownList Not Showing values
What I have tried:
created two tables 1) DepartmentMaster with two column DepartmentId (primary Key),DepartmentName 2)DesignationMaster with threecolumn Desid(Pk), DesigName, DepartmentId (foreign key)
created Procedure (1)
create procedure [dbo].[spBindDepartment]
as begin
select * from DepartmentMaster
End
Procedure (2)
Create procedure [dbo].[SpBindDesignation]
@departmentId int
as begin
select * from DesignationMaster where DepartmentId=@departmentId
End
(Data access function)
public DataSet BindDropDownDept()
{
SqlConnection con = new SqlConnection(Connection);
con.Open();
SqlCommand cmd = new SqlCommand("spBindDepartment",con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
return ds;
}
Business Logic Call
EmployeeDetailsDA objEmployeeDetailsDA = new EmployeeDetailsDA();
public DataSet GetDept()
{
return objEmployeeDetailsDA.BindDropDownDept();
}
Aspx.cs file databind
public void BindEmpDD()
{
EmployeeDetailsBL objEmployeeDetailsBL=new EmployeeDetailsBL();
DataSet ds = objEmployeeDetailsBL.GetDept();
ddlDepartment.DataSource = ds;
ddlDepartment.DataTextField = "DepartmentName";
ddlDepartment.DataValueField = "DepartmentId";
ddlDepartment.DataBind();
ddlDepartment.Items.Insert(0, new ListItem("---Select State---"));
}
(It Working fine)
Now for second Drop down
Data access Code
public DataSet BindDropDownDesignation()
{
SqlConnection con = new SqlConnection(Connection);
con.Open();
SqlCommand cmd = new SqlCommand("SpBindDesignation", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter Parameters = new SqlParameter();
cmd.Parameters.AddWithValue("@departmentId", SqlDbType.VarChar);
SqlDataAdapter da=new SqlDataAdapter(cmd);
DataSet ds=new DataSet();
da.Fill(ds);
return ds;
}
Business Logic Code
public DataSet GetDesignation()
{
EmployeeDetailsDA objEmployeeDetailsDA = new EmployeeDetailsDA();
return objEmployeeDetailsDA.BindDropDownDesignation();
}
Aspx.cs file Code is
public void BindEmpDesig()
{
EmployeeDetailsBL objEmployeeDetailsBL = new EmployeeDetailsBL();
DataSet ds = objEmployeeDetailsBL.GetDesignation();
ddlDesignation.DataSource = ds;
ddlDesignation.DataTextField = "DesignationName";
ddlDesignation.DataValueField = "DesignationId";
ddlDesignation.DataBind();
ddlDesignation.Items.Insert(0, new ListItem("---Select State---"));
}
And I have This Function on SelectedIndex changed Event of Fist Drop Down List
as
protected void ddlDepartment_SelectedIndexChanged(object sender, EventArgs e)
{
BindEmpDesig();
}