How to load data of employee if drop down list selected changed ?

Question

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

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

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 
Employee Model

public class EmpModel
        {

            public int EmployeeId { get; set; }


            public string EmployeeName { get; set; }


            public int EmployeeStatus { get; set; }

          

        }
Employee Repository have functions for controller

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

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

@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> 
so How to get data on view GetAllEmpDetails when drop down statusselect selected index changed ?

Answer 1

Hi @Ahmed Salah Abed Elaziz,

Your problem is simply to add an "input" control to the View page to pass data to the "controller" and write an overloaded method.

At the same time, in order to achieve better results, you can write a javascript method: use the input control once every time the select control option changes. At the same time, hide the input control to achieve better results.

Code:

[HttpGet]
        public ActionResult GetAllEmpDetails()
        {
            EmpRepository EmpRepo = new EmpRepository();
           
            
            ModelState.Clear();
            
            return View(EmpRepo.GetAllEmployees());
        }
        [HttpPost]
        public ActionResult GetAllEmpDetails(string MovieType)
        {
            EmpRepository EmpRepo = new EmpRepository();
            ModelState.Clear();
            int A = Convert.ToInt32(MovieType);
            return View("GetAllEmpDetails", EmpRepo.LoadDropDownLists(A));
        }
        public ActionResult CategoryChosen(string MovieType)
        {
            ViewBag.messageString = MovieType;
            
            return View(MovieType,"GetAllEmpDetails");
        }

@{
    ViewBag.Title = "GetAllEmpDetails";
}
<h2>GetAllEmpDetails</h2>
<script type="text/javascript">
    function Onchange() {
        document.getElementById("up").click();
    }
</script>
<form action="/Employee/GetAllEmpDetails" method="post"  >
    <select id="MovieType" name="MovieType" onchange="Onchange()" >
        <option value=""></option>
        <option value="1">Pending Request</option>
        <option value="2">Done Request</option>
    </select>
    <input id="up" type="submit" value="submit" hidden="hidden" />
</form>
<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>
<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>

Result

This is the more commonly used form method for asp.net MVC. Of course, you can use ajax to achieve better results. If you need, please leave a message and I will provide it to you.

Best Regards

Qi You

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.