populate dropdown from dapper query

Question

Hello,

I am using strored procedure that returns empcode and name .

public  List<SelectListItem> GetEmpcodes()  
        {  
            using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.ConVal("constring")))  
            {  
                var output = connection.Query<SelectListItem>("get_eCode", new { }, commandType: CommandType.StoredProcedure).ToList();  
               
                return output;  
  
  
            }  
        }  

and calling that and store as a selectlist in viewbag

 public ActionResult newEed()  
        {  
            try  
            {  
                DataAccess Db = new DataAccess();  
                var items = new SelectList(Db.GetEmpcodes(), "empCode", "name");  
                 
                    ViewBag.codes= items;  
                  
                return View();  
            }  
            catch(Exception ex)  
            {  
                Console.Write(ex.Message);  
                return View();  
            }  
             
  
              
        }  

in view

 @Html.DropDownListFor(model => model.empCode, (SelectList)ViewBag.codes, new { @class = "form-control" })  

gives me error

System.Web.HttpException: 'DataBinding: 'System.Web.Mvc.SelectListItem' does not contain a property with the name 'empCode'.'

i am new to asp.net mvc, someone please guide

thanks for any help

Answer 1

the GetEmpCodes is wrong. I assume the table has name and empCode columns you want to map to a select list:

         public  List<SelectListItem> GetEmpcodes()   
         {   
             using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.ConVal("constring")))   
             {   
                 var output = connection.Query<dynamic>("get_eCode", new { }, commandType: CommandType.StoredProcedure)  // return rows    
                        .Select(r => new SelectListItem. // map row to SelectListItem   
                        {   
                             Value = r.empCode,   // use correct column name   
                             Text = r.name             // use correct column name   
                        }   
                        .ToList();   
                    
                 return output;   
             }   
         }   

then to use:

              DataAccess Db = new DataAccess();   
              ViewBag.codes= Db.GetEmpcodes();   
                       

the other option is to define a new class for GetEmpcode, and the current code will work:

pubic class EmpcodeModel   
{   
    public string empCode {get; set;}   
    public string name {get; set;}   
}   
public  List<EmpcodeModel> GetEmpcodes()   
{   
             using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.ConVal("constring")))   
             {   
                 var output = connection.Query< EmpcodeModel>("get_eCode", new { }, commandType: CommandType.StoredProcedure).ToList();   
                    
                 return output;   
             }   
}   

Answer 2

Hi @Jayannet-2659 ,
You bind SelectListItem to DropDownList, so SelectListItem doesn't contain properties like empCode and name. It contains Text, Value and Selected as properties.
You can directly get the value of GetEmpcodes() without new DataAccess().
You can refer to the following examples:
Controller:

 public static List<SelectListItem> GetKeywords()  
        {  
            var keyword = new List<SelectListItem>();  
            keyword.Add(new SelectListItem { Value = "TEST 1", Text = "Market Cap" });  
            keyword.Add(new SelectListItem { Value = "TEST 2", Text = "Revenue" });  
            return keyword;  
              
        }  
          
        public ActionResult Index()  
        {  
              
            ViewBag.codes = new SelectList(GetKeywords(), "Value", "Text");  
            return View();  
        }  

Model

public class TEST  
    {  
        public string empCode { get; set; }  
        public string name { get; set; }  
  
    }  

cshtml

@model  WebApplication1.Models.TEST  
@Html.DropDownListFor(model => model.empCode, (SelectList)ViewBag.codes, new { @class = "form-control" })  

Best regards,
Lan Huang

---

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.