ASP.NET MVC - Populating a dropdown list with values from a database table

Question

Hi.  I am setting up a user application using ASP.NET MVC .NET6.0.  I am having trouble displaying values from a database table in a dropdown list on my cshtml razor page.

My Controller:

public IActionResult Index(bool ShowMessage = false, string Message = "", int profileOption = 1)
        {
            if (_SharedFunctions.IntNullCheck(contxt.HttpContext!.Session?.GetInt32("userID")))
            {
                object user = new User(contxt.HttpContext!.Session?.GetInt32("userID"));
                ViewBag.User = user;

                if (profileOption == 2)
                {
                    object userMsgOptions = new UserMsgOptions(contxt.HttpContext!.Session?.GetInt32("userID"));
                    List<FormValues_MsgIntervals> msgInts = _UserSettingsFunctions.GetMsgIntervals();
                    List<TextMsgUser> txtMsgUsers = _UserSettingsFunctions.GetTextMsgUsers(contxt.HttpContext!.Session?.GetInt32("userID"));

                    ViewBag.MessagingOptions = userMsgOptions;
                    ViewBag.MsgIntervals = msgInts;
                    ViewBag.TxtMessageUsers = txtMsgUsers;
                }

                ViewBag.showMessage = ShowMessage;
                ViewBag.message = Message;
                ViewBag.ProfileOption = profileOption;

                return View("UserProfile");
            }
            else
            {
                return Redirect("https://www.aggoraonline.co.uk/dev/Login");
            }
        }

Above you can see that the values required are returned from the database by calling a method:

List<FormValues_MsgIntervals> msgInts = _UserSettingsFunctions.GetMsgIntervals();

And passed to the view using ViewBag:

ViewBag.MsgIntervals = msgInts;

GetMsgIntervals method:

public List<FormValues_MsgIntervals> GetMsgIntervals()
    {
        string sql = $@"SELECT ID, Model, FormItemOrderRef, FormItem 
                        FROM [IOT_DB].[dbo].[tblFormValues]
                        WHERE Model = 'FormValues_MsgIntervals'
                        ORDER BY FormItemOrderRef";
        DataTable dt = _DBSharedFunctions.DataTable_Required(sql);
        List<FormValues_MsgIntervals> list = new();

        foreach (DataRow row in dt.Rows)
        {
            list.Add(new FormValues_MsgIntervals(
                Convert.ToInt32(row["ID"]),
                Convert.ToString(row["Model"]),
                Convert.ToInt32(row["FormItemOrderRef"]),
                Convert.ToString(row["FormItem"])
            ));
        }

        list.Insert(0, new FormValues_MsgIntervals(0, "FormValues_MsgIntervals", 0, "--- Select Value ---"));

        return list;
    }

As you can see, I am not using Entity Framework and simply using standard SQL to return a DataTable which I iterate through to create a list of objects instantiated from the class FormValues_MsgIntervals:

public class FormValues_MsgIntervals
    {
        private readonly DBSharedFunctions _DBSharedFunctions = new();

        public int ID
        {
            get { return id; }
            set { id = value; }
        }

        public string? Model
        {
            get { return model; }
            set { model = value; }
        }

        public int FormItemOrderRef
        {
            get { return formItemOrderRef; }
            set { formItemOrderRef = value; }
        }

        public string? FormItem
        {
            get { return formItem; }
            set { formItem = value; }
        }

        private int id;
        private string? model;
        private int formItemOrderRef;
        private string? formItem;

        public FormValues_MsgIntervals(int ID, string? Model, int FormItemOrderRef, string? FormItem)
        {
            this.id = ID;
            this.model = Model;
            this.formItemOrderRef = FormItemOrderRef;
            this.formItem = FormItem;
        }
    }

The database table:

I am however finding it hard to display this as a drop-down list in the front end.  Reading around this I have tried two approaches:

<select class="form-control" asp-items="@(new SelectList(ViewBag.MsgIntervals, "FormItem"))"></select>

Which resulted in:

AND:

@Html.DropDownList("MsgInterval", new SelectList(ViewBag.MsgIntervals, "FormItem"))

Which resulted in pretty much the same:

What I want displayed in the dropdown is the values in the database in the column ‘FormItem’.

I think that my backend is sound enough, I think I am just not quite getting the code in the View correct.  Could anyone help?

Answer 1

What happens if you try this?:

@Html.DropDownList("MsgInterval", new SelectList(ViewBag.MsgIntervals, "ID", "FormItem"))

Note I've added an extra argument to the SelectList constructor which specifies the property to use as the <option> value.

It looks like you're calling this constructor, where the second argument is the "selected value":

https://learn.microsoft.com/en-us/dotnet/api/system.web.mvc.selectlist.-ctor?view=aspnet-mvc-5.2#system-web-mvc-selectlist-ctor(system-collections-ienumerable-system-object)