How do I fix an empty dropdownlist when binding ASP.NET MVC using Dapper?

Question

I am attempting to bind a dropdownlist to values in an SQL table using dapper, but the dropdown is empty. Is shows the correct number of rows, but they are all empty. In the debug values from the dapper call I see that it is returning the correct number of rows (3) and that the Id's are correct. - So I think that the data is retrieved correctly but not shown. I am new to MVC and ASP, so any help is very much appreciated.

Controller.cs

public ActionResult AddPilot()
    {
        ViewBag.Message = "Add new pilot.";
        PilotModel program = new PilotModel();
        program.ProgramList = new SelectList(LoadDropDown("SELECT [Id], [Programs] FROM tbl_ProgOverview"), "Id", "Program");            
        return View(program);
    }

PilotModel.cs

    public class PilotModel
{
    public int Id { get; set; }
    public string WingID { get; set; }
    public string Pilot { get; set; }
    public bool Ship4 { get; set; }
    public bool Ship2 { get; set; }
    public bool Wing { get; set; }
    public bool IP { get; set; }
    public bool ME { get; set; }
    public bool Supervisor { get; set; }
    public string Program { get; set; }
    public string Program2 { get; set; }
    public DateTime? PrgmStart { get; set; }
    public DateTime? PrgmEnd { get; set; }
    public DateTime? Prgm2Start { get; set; }
    public DateTime? Prgm2End { get; set; }
    public bool ExtSync { get; set; }
    public SelectList ProgramList { get; set; }
}

}

View.cthtml

<div class="form-group">
    @Html.LabelFor(model => model.Program, htmlAttributes: new { @class = "control-label col-md-2" })
    <div class="col-md-10">            
        @Html.DropDownListFor(model => model.Program, Model.ProgramList, "Select")
        @Html.ValidationMessageFor(model => model.Program, "", new { @class = "text-danger" })
    </div>
</div>

Helper.cs

public static IEnumerable<PilotModel> LoadDropDown(string sql)
    {
        return SqlDataAccess.GetDropDown<PilotModel>(sql);
    }

DataAccess.cs

public static IEnumerable<PilotModel> GetDropDown<PilotModel>(string sql)
    {
        using (IDbConnection cnn = new SqlConnection(GetConnectionString()))
        {
            return cnn.Query<PilotModel>(sql);
        }
    }

The table "tbl_ProgOverview" has only 2 columns ("Id", "Programs"), and all I am trying to achieve is to have the dropdown in model.Program, show a list of the values in the "Programs" column of this table. Thank you!

Normally, I would say "avoid screenshots", but in this case: the screenshot genuinely helped me identify the problem, i.e. I can see that the Program member is null. So: :thumbsup: — Marc Gravell
– Marc Gravell, Commented May 24, 2022 at 13:10
Knew the problem was there, just didn’t know why;-) thank you! — Sapster77
– Sapster77, Commented May 25, 2022 at 14:20

Marc Gravell · Accepted Answer · 2022-05-24 13:07:00Z

Dapper: cares about names.

You have the SQL:

SELECT [Id], [Programs] FROM tbl_ProgOverview

but your c# property is Program (or at least, I presume that's what you intend to map it to). Dapper is going to ignore the Programs column and not set Program, hence why it is null in the screen-shot.

Try:

SELECT [Id], [Programs] as Program FROM tbl_ProgOverview

This re-maps the projected column name in the SQL to Program, to match what Dapper is looking for. Alternatively, you could renaming the C# property to Programs.

As a side note: when you use literals like "Id" or "Program" in your C# code, it makes it hard to rename those properties later if you need to. You might want to try nameof(PilotModel.Id) / nameof(PilotModel.Program), which avoids hard-coding those literals. If you rename them: it will fix your binding code automatically (or at least tell you that no such member exists).

Collectives™ on Stack Overflow

How do I fix an empty dropdownlist when binding ASP.NET MVC using Dapper?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related