My current project is MVC based. I am stuck at trying to populate a select element from my SQL Server database table. Controller is receiving database data in a DataTable object. This data needs to be bound to a HTML select element at page load.

Any help regarding this is appreciated.

Controller code GET method action

 // GET: SearchPopulate
 [HttpGet]
 public ActionResult SearchHome()
 {
        SearchPopulateMaster empPopulate = new SearchPopulateMaster();
        empPopulate.emp = PopulateEmps();
        return View();
 }

 private static List<SelectListItem> PopulateEmps() 
 {
        List<SelectListItem> items = new List<SelectListItem>();
        DataTable localdt = new DataTable();
        localdt = DALAcess.getDataFromSql("SELECT id,AgentName from [atmcolle_COMM_U88].[dbo].ATM_M_empMaster");
        return items;
 }

View code .cshtml file

<form asp-controller="SearchHome" asp-action="SearchHome">
    <!--Non-hardcoded retrieved from database-->
    <select name="selEmp" id="selEmp">
        <option>Please select one</option>
    </select>

    <input type="submit" />
    <!--Non-hardcoded retrieved from database-->
</form>

CodePudding user response：

Seems you are trying to bind your Data into HTML dropdown (HTML Select Tag) which is coming using entity framework from your database.

You can try below steps:

Model:

Lets say I have model like this:

public class Agent
    {
        [Key]
        public int Id { get; set; }
        public string AgentName { get; set; }
    }

Controller:

    public ActionResult BindHtmlSelectElement()
    {
        //Bind Dropdown
        List<Agent> agent = PopulateEmps();
        ViewBag.Agent = new SelectList(agent, "Id", "AgentName");
        return View();
    }

View:

   @model MVCApps.Models.Agent
    @{
        ViewData["Title"] = "BindHtmlSelectElement";
    }
    
    <h4><strong>BindHtmlSelectElement</strong> </h4>
    <hr />
    <div >
        <label  >Agent</label>
        <div >
            <select asp-items="ViewBag.Agent" value=""   asp-for="Id"></select>
        </div>
    </div>

Output:

Note: In your PopulateEmps method return your agent list. And be conscious about the convension as well I made the id as Id be carefull while replace that to get rid of any error.

Update: You can also try access your Agent query like below

public List<Agent> QueryBuilder()
        {
            using (var connection = new SqlConnection("Server=ServerName;Database=YouDatabaseName;Trusted_Connection=True;MultipleActiveResultSets=true"))
            {

                List<Agent> viewModeList = new List<Agent>();
                await connection.OpenAsync();
                var command = connection.CreateCommand();
                command.CommandText = "SELECT id,AgentName from [atmcolle_COMM_U88].[dbo].ATM_M_empMaster";
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Agent viewModel = new Agent();
                    viewModel.Id= reader["id"].ToString(); // Remember to convert property in correct format
                    viewModel.AgentName = reader["AgentName"].ToString();
                    viewModeList.Add(viewModel);

                }
                reader.Close();
                command.Dispose();
                connection.Close();
                return viewModeList;
            }

        }

Update The Controller Like Below:

public ActionResult BindHtmlSelectElement()
    {
        //Bind Dropdown
        List<Agent> agent = QueryBuilder();
        ViewBag.Agent = new SelectList(agent, "Id", "AgentName");
        return View();
    }

Hope above steps guide you accordingly to achieve your goal.