Click here to Skip to main content
15,904,416 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
How to load data employee on view GetAllEmpDetails.cshtml based on drop down selected index changed?



I work on MVC web application using c# by ado.net I face issue I can't display employee data based on drop down selected index changed .

so

if user select Pending Request from drop down list it will select employee that have select employee status 1 .

if user select Done Request from drop down list it will select employee that have select employee status 2 .

my code as below :

Table structure
SQL
CREATE TABLE [dbo].[Employee](
    [EmployeeId] [int] NOT NULL,
    [EmployeeName] [nvarchar](100) NULL,
    [EmployeeStatus] [int] NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
    [EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Employee] ([EmployeeId], [EmployeeName], [EmployeeStatus]) VALUES (1211, N'ahmed', 1)
INSERT [dbo].[Employee] ([EmployeeId], [EmployeeName], [EmployeeStatus]) VALUES (1222, N'eslam', 1)
INSERT [dbo].[Employee] ([EmployeeId], [EmployeeName], [EmployeeStatus]) VALUES (1223, N'adel', 2)
INSERT [dbo].[Employee] ([EmployeeId], [EmployeeName], [EmployeeStatus]) VALUES (1224, N'mohamed', 2)
INSERT [dbo].[Employee] ([EmployeeId], [EmployeeName], [EmployeeStatus]) VALUES (1225, N'mosh', 2)
INSERT [dbo].[Employee] ([EmployeeId], [EmployeeName], [EmployeeStatus]) VALUES (1227, N'ali', 1)

stored procedures have logic
SQL
create Procedure [dbo].[LoadDropDownEmployee] 
@EmployeeStatus int
as  
begin  
   select * from Employee  where EmployeeStatus=@EmployeeStatus
End 

create Procedure [dbo].[GetEmployees]  
as  
begin  
   select *from Employee  
End

so How to get data on view GetAllEmpDetails when drop down statusselect selected index changed ?

What I have tried:

Employee Model
C#
public class EmpModel
{
    public int EmployeeId { get; set; }
    public string EmployeeName { get; set; }
    public int EmployeeStatus { get; set; }
}

Employee Repository have functions for controller
C#
public List<EmpModel> GetAllEmployees()
{
    connection();
    List<EmpModel> EmpList = new List<EmpModel>();

    SqlCommand com = new SqlCommand("GetEmployees", con);
    com.CommandType = CommandType.StoredProcedure;
    SqlDataAdapter da = new SqlDataAdapter(com);
    DataTable dt = new DataTable();

    con.Open();
    da.Fill(dt);
    con.Close();   
    foreach (DataRow dr in dt.Rows)
    {

        EmpList.Add(
            new EmpModel
            {
                EmployeeId = Convert.ToInt32(dr["EmployeeId"]),
                EmployeeName = Convert.ToString(dr["EmployeeName"]),
                EmployeeStatus = Convert.ToInt32(dr["EmployeeStatus"])
            }
            );
    }

    return EmpList;
}

public List<EmpModel> LoadDropDownLists(int EmployeeStatus)
{
    connection();
    List<EmpModel> EmpList = new List<EmpModel>();

    SqlCommand com = new SqlCommand("LoadDropDownEmployee", con);
    com.CommandType = CommandType.StoredProcedure;
    com.Parameters.Add("@EmployeeStatus", SqlDbType.VarChar, 50);
    com.Parameters["@EmployeeStatus"].Value = EmployeeStatus;
    SqlDataAdapter da = new SqlDataAdapter(com);
    DataTable dt = new DataTable();

    con.Open();
    da.Fill(dt);
    con.Close();  
    foreach (DataRow dr in dt.Rows)
    {
        EmpList.Add(
            new EmpModel
            {
                EmployeeId = Convert.ToInt32(dr["EmployeeId"]),
                EmployeeName = Convert.ToString(dr["EmployeeName"])
            }
            );
    }

    return EmpList;
}

controller Employee
C#
public class EmployeeController : Controller
{
    public ActionResult LoadDropDownList(int EmployeeStatus)
    {
        EmpRepository EmpRepo = new EmpRepository();
        return View();
    }
    public ActionResult GetAllEmpDetails()
    {
        EmpRepository EmpRepo = new EmpRepository();
        ModelState.Clear();
        return View(EmpRepo.GetAllEmployees());
    }
}

view GetAllEmpDetails.cshtml
Razor
@model IEnumerable<Ado.netMvc.Models.EmpModel>

@{
    ViewBag.Title = "GetAllEmpDetails";
}

<h2>GetAllEmpDetails</h2>

<th>
    <select class="form-control" id="statusselect" name="statusselectName">
        <option>Select Status</option>
        <option>Pending Request</option>
        <option>All requests </option>
    </select>
</th>

<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.EmployeeId)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.EmployeeName)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.EmployeeStatus)
        </th>
        <th></th>
    </tr>

    @foreach (var item in Model)
    {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.EmployeeId)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.EmployeeName)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.EmployeeStatus)
            </td>
        </tr>
    }
</table>
Posted
Updated 6-Feb-23 4:31am
v2

1 solution

Split up your views:

GetAllEmpDetails.cshtml
Razor
@model IEnumerable<Ado.netMvc.Models.EmpModel>

@{
    ViewBag.Title = "GetAllEmpDetails";
}

<h2>GetAllEmpDetails</h2>

<div>
    <select class="form-control" id="statusselect" name="statusselectName">
        <option value="0" selected>All requests</option>
        <option value="1">Pending Request</option>
        <option value="2">Done Request</option>
    </select>
</div>
<div id="employeeList">
    @Html.Partial("EmployeeList")
</div>
EmployeeList.cshtml
Razor
@model IEnumerable<Ado.netMvc.Models.EmpModel>

<table class="table">
<thead>
<tr>
    <th scope="col">@Html.DisplayNameFor(m => m.FirstOrDefault().EmployeeId)</th>
    <th scope="col">@Html.DisplayNameFor(m => m.FirstOrDefault().EmployeeName)</th>
    <th scope="col">@Html.DisplayNameFor(m => m.FirstOrDefault().EmployeeStatus)</th>
</tr>
</thead>
<tbody>
@foreach (var item in Model)
{
    <tr>
        <td>@Html.DisplayFor(_ => item.EmployeeId)</td>
        <td>@Html.DisplayFor(_ => item.EmployeeName)</td>
        <td>@Html.DisplayFor(_ => item.EmployeeStatus)</td>
    </tr>
}
</tbody>
</table>

Change your controller method to return a partial view in response to an AJAX request:
C#
public ActionResult LoadDropDownList(int EmployeeStatus)
{
    EmpRepository EmpRepo = new EmpRepository();
    
    var model = EmployeeStatus == 0
        ? EmpRepo.GetAllEmployees()
        : EmpRepo.LoadDropDownList(EmployeeStatus);
    
    if (Request.IsAjaxRequest()) return PartialView("EmployeeList", model);
    return View("GetAllEmpDetails", model);
}

Add a script to the GetAllEmpDetails.cshtml view to update the employee list when the drop-down list changes:
Razor
...
@section Scripts {
    <script>
    $("#statusselect").change(function(){
        $("#employeeList").load('@Url.Action("LoadDropDownList")?EmployeeStatus=' + encodeURIComponent(this.value));
    });
    </script>
}
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900