How to Populate DropDownList from the Database in MVC

Question

I am new to ASP NET MVC

I need Populate a drop down list from values obtained from a database table using MySql database and view model, after checking if the current user is application enabled, using ASP NET MVC.

This is the tutorial

My code below return

Error: returns void, a return keyword must not be followed by an object expression

On this line

return items; 

Any help really appreciated.

Controller

public ActionResult Recovery()
{
      try
        {
            string cs = ConfigurationManager.ConnectionStrings["cnj"].ConnectionString;
    
            using (var connection =
                new MySqlConnection(cs))
            {
                string commandText = " SELECT cCountry FROM `dotable_user` " +
                    " WHERE cName = @Username; ";
    
                using (var command =
                    new MySqlCommand(commandText, connection))
                {
                    if (!String.IsNullOrEmpty(HttpContext.User.Identity.Name.ToString()))
                    {
                        command.Parameters.AddWithValue("@Username", HttpContext.User.Identity.Name.ToString());
                    }
    
                    connection.Open();
    
                    string cCountry = (string)command.ExecuteScalar();
    
                    if (String.IsNullOrEmpty(cCountry))
                    {
                        TempData["Message"] = "No user.";
                        ViewBag.Message = String.Format("No user.");
                    }
    
    
                    List<SelectListItem> items = new List<SelectListItem>();
                    using (MySqlConnection con = new MySqlConnection(cs))
                    {
                        string query = " SELECT cCountry FROM `dotable_countries` " +
                            " WHERE cCountry = '" + cCountry.ToString() + "' ";
    
                        using (MySqlCommand cmd = new MySqlCommand(query))
                        {
                            cmd.Connection = con;
                            con.Open();
                            using (MySqlDataReader sdr = cmd.ExecuteReader())
                            {
                                while (sdr.Read())
                                {
                                    items.Add(new SelectListItem
                                    {
                                        Text = sdr["cCountry"].ToString(),
                                        Value = sdr["cCountry"].ToString()
                                    });
                                }
                            }
                            connection.Close();
                        }
                    }
    
                    return items;                        
                }
            }
        }
        catch (Exception ex)
        {
            TempData["Message"] = "Login failed.Error - " + ex.Message;
        }
}

Update

I have tried with this code.

I have error

Error CS0103 The name 'cCountry' does not exist in the current context

  public ActionResult Recovery()
  {
      try
        {
            string cs = ConfigurationManager.ConnectionStrings["cnj"].ConnectionString;
    
            using (var connection =
                new MySqlConnection(cs))
            {
                string commandText = " SELECT cCountry FROM `dotable_user` " +
                    " WHERE cName = @Username; ";
    
                using (var command =
                    new MySqlCommand(commandText, connection))
                {
                    if (!String.IsNullOrEmpty(HttpContext.User.Identity.Name.ToString()))
                    {
                        command.Parameters.AddWithValue("@Username", HttpContext.User.Identity.Name.ToString());
                    }
    
                    connection.Open();
    
                    string cCountry = (string)command.ExecuteScalar();
    
                    if (String.IsNullOrEmpty(cCountry))
                    {
                        TempData["Message"] = "No user.";
                        ViewBag.Message = String.Format("No user.");
                    }   

                    TempData["Dates"] = PopulateDates();                 
                }
            }
        }
        catch (Exception ex)
        {
            TempData["Message"] = "Login failed.Error - " + ex.Message;
        }
   }


        private static List<SelectListItem> PopulateDates()
        {
            List<SelectListItem> items = new List<SelectListItem>();
            string cs = ConfigurationManager.ConnectionStrings["cnj"].ConnectionString; 
            
            using (MySqlConnection con = new MySqlConnection(cs))
            {
                string query = " SELECT cCountry FROM `dotable_countries` " +
                    " WHERE cCountry = '" + cCountry.ToString() + "'; ";

                using (MySqlCommand cmd = new MySqlCommand(query))
                {
                    cmd.Connection = con;
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            items.Add(new SelectListItem
                            {
                                Text = sdr["cCountry"].ToString(),
                                Value = sdr["cCountry"].ToString()
                            });
                        }
                    }

                    cmd.Connection.Close();
                }
            }

            return items;
        }

Answer 1

You are not passing cCountry value to populateDates.That's why you are getting this error. You can do something like below to get drop down populated. However it is not good idea to write Business Logic directly in controller. You should move it to model or Business layer.

private static List<SelectListItem> PopulateDates(string country)
        {
            List<SelectListItem> items = new List<SelectListItem>();
            string cs = ConfigurationManager.ConnectionStrings["cnj"].ConnectionString; 
            
            using (MySqlConnection con = new MySqlConnection(cs))
            {
                string query = " SELECT cCountry FROM dotable_countries WHERE cCountry = @country";

                using (MySqlCommand cmd = new MySqlCommand(query))
                {
                   cmd.Parameters.AddWithValue("@country",country);
                    cmd.Connection = con;
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            items.Add(new SelectListItem
                            {
                                Text = sdr["cCountry"].ToString(),
                                Value = sdr["cCountry"].ToString()
                            });
                        }
                    }

                    cmd.Connection.Close();
                }
            }

            return items;
        }

and while calling this method in Action pass country value to it like below

TempData["Dates"] = PopulateDates(cCountry);