ASP.NET Core Assign database values to a dropdown list for Edit action

Question

I have a controller with CRUD autogenerated and I am trying to fill values fetched from database to a dropdown. My model class is like below

using Microsoft.AspNetCore.Mvc.Rendering;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;

namespace ERPCustomerSupplier.Models
{
    public class Customer
    {
        [Display(Name = "ID")]
        public int ID { get; set; }
        [Display(Name = "User")]
        public string REQ_USERNAME { get; set; }

        [Display(Name = "Date")]
        public string REQ_DATE { get; set; }

        [Display(Name = "Status")]
        public string STATUS { get; set; }
        [Display(Name = "Customer Type")]
        public string CUSTOMER_TYPE { get; set; }
        [Display(Name = "Number")]
        public string CUSTOMER_NUMBER { get; set; }
        [Display(Name = "Name")]
        public string ORGANIZATION_NAME { get; set; }
        [Display(Name = "Country")]
        public string COUNTRY { get; set; }
        [Display(Name = "Address")]
        public string ADDRESS_LINE { get; set; }
        [Display(Name = "City")]
        public string CITY { get; set; }
        [Display(Name = "State")]
        public string STATE { get; set; }
        [Display(Name = "Operating Unit")]
        public string OPERATING_UNIT { get; set; }
        [Display(Name = "Currency")]
        public string CURRENCY { get; set; }
        [Display(Name = "Credit Limit")]
        public string CREDIT_LIMIT { get; set; }
        [Display(Name = "Credit Terms")]
        public string CREDIT_TERMS { get; set; }
        [Display(Name = "Bill To")]
        public string BILL_TO { get; set; }
        [Display(Name = "Ship To")]
        public string SHIP_TO { get; set; }
        [Display(Name = "Contact Person")]
        public string CT_PERSON { get; set; }
        [Display(Name = "Job Title")]
        public string CT_JOB_TITLE { get; set; }
        [Display(Name = "Department")]
        public string CT_DEPARTMENT { get; set; }
        [Display(Name = "Mobile")]
        public string CT_MOBILE { get; set; }
        [Display(Name = "Email")]
        public string CT_EMAIL { get; set; }
        [Display(Name = "Phone")]
        public string CT_PHONE_NUMBER { get; set; }
        [Display(Name = "Ext")]
        public string CT_PHONE_EXT { get; set; }
        [Display(Name = "Fax")]
        public string CT_FAX_NUMBER { get; set; }
        [Display(Name = "Ext")]
        public string CT_FAX_EXT { get; set; }
        [Display(Name = "Assigned To")]
        public string ASSIGNEE_USERNAME { get; set; }
        [Display(Name = "Viewed Status")]
        public string VIEWED { get; set; }
        [Display(Name = "Territory")]
        public string TERRITORY_CODE { get; set; }
        [Display(Name = "Profile Class")]
        public string PROFILE_CLASS_ID { get; set; }
        [Display(Name = "Payment Terms")]
        public string PAYMENT_TERMS_NAME { get; set; }
        [Display(Name = "Payment Terms ID")]
        public string PAYMENT_TERMS_ID { get; set; }
        [Display(Name = "Profile Class")]
        public string PROFILE_CLASS_NAME { get; set; }
        [Display(Name = "Account Type")]
        public string ACCOUNT_TYPE { get; set; }


         public List<CurrencyList> currencyList { get; set; }

    }

    public class CurrencyList
    {
        public string CurrencyCode { get; set; }
        public string CurrencyName { get; set; }
    }
}

and the Action Method as below

public ActionResult Edit(int id)
        {
            CustomerDataAccessLayer customer = new CustomerDataAccessLayer();
            Customer customer1 = new Customer {
                currencyList = customer.GetAllCurrencies()
            };
 
            ModelState.Clear();
  
            return View(customer.GetAllCustomers().Find(Customer => Customer.ID == id));
         }

        // POST: CustomerController/Edit/5
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Edit(int id, IFormCollection collection)
        {
            try
            {
                return RedirectToAction(nameof(Index));
            }
            catch
            {
                return View();
            }
        }

I populate the List using a Data Access layer

  public List<CurrencyList> GetAllCurrencies()
        {
            //throw new NotImplementedException();
            List<CurrencyList> CurrenciesList = new List<CurrencyList>();

            string Orasql = @"Select CURRENCY_CODE, DESCRIPTION CURRENCY_NAME, PRECISION DECIMAL_POINTS  
                                from FND_CURRENCIES_VL where enabled_flag = 'Y'
                                ORDER BY 1";
            OracleConnection conn = new OracleConnection(connectionString);

            OracleCommand cmd = new OracleCommand(Orasql, conn);

            //OracleDataReader dr = new OracleDataReader();
            cmd.CommandType = CommandType.Text;

            OracleDataAdapter da = new OracleDataAdapter(cmd);
            DataTable dt = new DataTable();

            conn.Open();
            da.Fill(dt);
            conn.Close();
            foreach (DataRow dr in dt.Rows)
            {
                CurrenciesList.Add(
                    new CurrencyList
                    {
                        CurrencyCode=Convert.ToString(dr["CURRENCY_CODE"]),
                        CurrencyName=Convert.ToString(dr["CURRENCY_NAME"])
                    }
                    );
            }
               
            return CurrenciesList;

        }

Finally the view is like following:

@model ERPCustomerSupplier.Models.Customer


@{
    ViewData["Title"] = "Edit";
    Layout = "~/Views/Shared/_SiteMaster.cshtml";
}

<h1>Edit</h1>

<h4>Customer</h4>
<hr />
<div class="row">
    <div class="col-md-4">
        <form asp-action="Edit">
            <div asp-validation-summary="ModelOnly" class="text-danger"></div>
            <table>
                <tr>
                    <td>
                        <label asp-for="CURRENCY" class="control-label"></label>
                    </td>
                    <td>
                       <select asp-for="CURRENCY" class="form-control form-control-sm" asp-items="Model.currencyList" />
                       <span asp-validation-for="CURRENCY" class="text-danger"></span>
                    </td>

and I get the error error "CS0266: Cannot implicitly convert type 'System.Collections.Generic.List<ERPCustomerSupplier.Models.CurrencyList>' to 'System.Collections.Generic.IEnumerable<Microsoft.AspNetCore.Mvc.Rendering.SelectListItem>'. An explicit conversion exists (are you missing a cast?)"

This is my 1st attempt with .Net Core & all helps are welcome.

Answer 1

Customer class modified from plain List item to List of SelectListItem

public List<SelectListItem> CurrencyList { get; set; }

List population modified with List object

 public List<SelectListItem> GetAllCurrencies()
        {
            //throw new NotImplementedException();
            List<SelectListItem> CurrencyList = new List<SelectListItem>();
            string Orasql = @"Select CURRENCY_CODE, DESCRIPTION CURRENCY_NAME, PRECISION DECIMAL_POINTS  
                                from FND_CURRENCIES_VL where enabled_flag = 'Y'
                                ORDER BY 1";
            OracleConnection conn = new OracleConnection(connectionString);
            OracleCommand cmd = new OracleCommand(Orasql, conn);
            cmd.CommandType = CommandType.Text;
            OracleDataAdapter da = new OracleDataAdapter(cmd);
            DataTable dt = new DataTable();

            conn.Open();
            da.Fill(dt);
            conn.Close();
            foreach (DataRow dr in dt.Rows)
            {
                CurrencyList.Add(new SelectListItem
                {
                    Text = dr["CURRENCY_NAME"].ToString(),
                    Value = dr["CURRENCY_CODE"].ToString()
                });
            }
            return CurrencyList;
        }

Modified Edit action as below:

 public ActionResult Edit(int id)
        {

            CustomerDataAccessLayer customerdataaccesslayer = new CustomerDataAccessLayer();
            Customer customer1 = customerdataaccesslayer.GetCustomerData(id);
            customer1.CurrencyList = customerDataAccessLayer.GetAllCurrencies();
            ModelState.Clear();
            //return View(customerdataaccesslayer.GetAllCustomers().Find(Customer => Customer.ID == id)); 
            return View(customer1); 
         }

Modified Edit.cshtml like following:

<td>
<label asp-for="CURRENCY" class="control-label"></label>
</td>
<td>
<select asp-for="CURRENCY" class="form-control form-control-sm" asp-items="Model.CurrencyList"></select>
<span asp-validation-for="CURRENCY" class="text-danger"></span>

Hope this helps few others.

Answer 2

The error message means you must use List SelectListItem rather than List CurrencyList,

you should use SelectListItem instead to generate a dropdownlist,following is a demo (database is sqlserver, you can change to yours):

public class Customer
{
    [Display(Name = "ID")]
    public int ID { get; set; }

    [Display(Name = "Currency")]
    public string CURRENCY { get; set; }
    //....
    [NotMapped]   //add this
    public List<SelectListItem> currencyList { get; set; }  //change to SelectListItem
}

And I choose ViewBag to pass the selectListitem, it will be easier if you also want to

get the other properties in Customer Model:

public IActionResult Index()
    {
        //Fill data from database to currentList, and use ViewBag to pass.
        ViewBag.currencyList = _context.CurrencyLists.Select(c => new SelectListItem { Value = c.CurrencyCode, Text = c.CurrencyName }).ToList();            
        return View();
    }

View,and change the form too, your form will not show result:

<select asp-for="CURRENCY" class="form-control form-control-sm" asp-items="@(List<SelectListItem>)ViewBag.currencyList" >
     <option>-Please select-</option>
</select>

Result: