MVC 5: Binding data from SQL to DropDownList

Hi

Im new to MVC and I am struggeling with binding data from SQL to a DropDownList.

Here is my code so far:

Controller:
using GbizzVirk_dk.Models;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace GbizzVirk_dk.Controllers
{
  public class VisitListController : Controller
  {
    // GET: VisitList
    public ActionResult ViewVisitList()
    {
      List<RecyclePoints> PointDDL = new List<RecyclePoints>();

      using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["gbizz"].ConnectionString))
      {
        con.Open();
        SqlCommand sqlCom;
        SqlDataReader sqlReader;

        sqlCom = new SqlCommand("RecyclePointGetInformation", con);
        sqlCom.CommandType = CommandType.StoredProcedure;
        sqlCom.Parameters.Add("@MunicipalityNo", SqlDbType.Int).Value = 630;
        sqlCom.Parameters.Add("@IsVirkDK", SqlDbType.Int).Value = 1;

        sqlReader = sqlCom.ExecuteReader();

        if (sqlReader.HasRows)
        {
          while (sqlReader.Read())
          {
            RecyclePoints pDDL = new RecyclePoints();
            pDDL.PointId = Convert.ToInt32(sqlReader["RecyclingPointId"].ToString());
            pDDL.PointName = sqlReader["PointName"].ToString();
            PointDDL.Add(pDDL);
          }
        }

        sqlReader.Close();
        sqlReader.Dispose();
        sqlCom.Dispose();
        con.Close();
        con.Dispose();
      }
      return View(PointDDL);
    }
  }
}

Open in new window


Model:
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace GbizzVirk_dk.Models
{
  //DropDownList til RecyclePoint Dropdown
  public class RecyclePoints
  {
    public int PointId { get; set; }
    public string PointName { get; set; }
  }

  public class ModelVisitList
  {
    [Display(Name="Fra den:")]
    public DateTime FromDate { get; set; }

    [Display(Name = "Til den:")]
    public DateTime ToDate { get; set; }

    private readonly List<RecyclePoints> _Points;

    [Display(Name = "Vælg genbrugsplads")]
    public int SelectPointId { get; set; }

    public IEnumerable<SelectListItem> PointItems
    {
      get { return new SelectList(_Points, "PointId", "PointName"); }
    }

    public RecyclePoints RecPoint { get; set; }  

    public ModelVisitList()
    {
      RecPoint = new RecyclePoints();
    }
  }

Open in new window


View:
@model GbizzVirk_dk.Models.ModelVisitList

@{
  Layout = "~/Views/Shared/_Layout.cshtml";
}

<h3>Sorter besøgsliste</h3>

<fieldset>
  <legend>Besøgsoversigt</legend>
  <div class="row">
    <div class="col-sm-4">
      <div class="input-append date" id="dp3" data-date="12-02-2012" data-date-format="dd-mm-yyyy">
        <div class="form-group">
          @Html.LabelFor(m => m.FromDate)
          <input type="date" id="startdato" value="12-02-2012" class="form-control" />
          <div class="description">Datoen skal indtastes i formatet dd/mm/åååå</div>
        </div>
      </div>
    </div>
    <div class="col-sm-4">
      <div class="input-append date" id="dp3" data-date="12-02-2012" data-date-format="dd-mm-yyyy">
        <div class="form-group">
          @Html.LabelFor(m => m.ToDate)
          <input type="date" id="startdato" value="12-02-2012" class="form-control" />
          <div class="description">Datoen skal indtastes i formatet dd/mm/åååå</div>
        </div>
      </div>
    </div>
  </div>
  <div class="row">
    <div class="col-sm-4">
      <div class="form-group">
        @Html.LabelFor(m => m.SelectPointId)
        @Html.DropDownListFor(m => m.SelectPointId, Model.PointItems)

      </div>
    </div>
  </div>
</fieldset>

Open in new window


When I run the site I get the following error:

The model item passed into the dictionary is of type 'System.Collections.Generic.List`1[GbizzVirk_dk.Models.RecyclePoints]', but this dictionary requires a model item of type 'GbizzVirk_dk.Models.ModelVisitList'.

So I suspect that I am not sending the correct Model to the view, or I haven't included the Model containing the data for the DropDownList in the main Model correctly.

Can anyone help me with this? I have tried all day to get my head round this, and I feel like I'm going in circles now...

Thanks in advance.
LVL 1
poultarpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ioannis ParaskevopoulosCommented:
Hi,

Your problem is that in your view you define the model being:
@model GbizzVirk_dk.Models.ModelVisitList

Open in new window


But your controller passes a model of type
return View(PointDDL);

Open in new window


Which is of type:
List<RecyclePoints> PointDDL = new List<RecyclePoints>();

Open in new window


Giannis
0
poultarpAuthor Commented:
Hello Giannis

Thank you for your answer. I am how ever still unable to get this dropdownlist to work (as mentioned I am a complete noob when it comes to MVC and I struggle to wrap my head around this dropdownlist thing).

In the CONTROLLER I have tried changing the
//List<RecyclePoints> PointDDL = new List<RecyclePoints>();

Open in new window

to
List<ModelVisitList> PointDDL = new List<ModelVisitList>();

Open in new window

but that means that in my SqlDataReader While loop:
while (sqlReader.Read())
          {
            RecyclePoints pDDL = new RecyclePoints();
            pDDL.PointId = Convert.ToInt32(sqlReader["RecyclingPointId"].ToString());
            pDDL.PointName = sqlReader["PointName"].ToString();
            PointDDL.Add(pDDL);
          }

Open in new window

I can't add the pDDL to the PointDDL.

I have been unable to find any examples online that shows how to populate a dropdownlist from a Stored Procedure, and I also struggle with creating a MODEL which contains both strings for Textbox's and List's for dropdownlist's.

Any help - or links to code examples online will be much appreciated.

/Søren
0
Ioannis ParaskevopoulosCommented:
Hi Søren,

In general a controller must return an object or class that will be of the exact same type as the view expects, or if you prefer, the view must have a Model of the type the controller sends. This is what the MVC principal is about, the controller binds the Model to the View.

Now in your case, it is obvious that your view should expect a model of type
GbizzVirk_dk.Models.ModelVisitList

Open in new window


This means that your controller should return such a model.

In my opinion you should make some modifications in your Model. First your constructor should accept a List<RecyclePoints> as an argument and then populate the _Points property. Then i think that what you currently have in your constructor has not an actual meaning, so i would remove it.

Having said that, i think your Model should look like this:

using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace GbizzVirk_dk.Models
{
  //DropDownList til RecyclePoint Dropdown
  public class RecyclePoints
  {
    public int PointId { get; set; }
    public string PointName { get; set; }
  }

  public class ModelVisitList
  {
    [Display(Name="Fra den:")]
    public DateTime FromDate { get; set; }

    [Display(Name = "Til den:")]
    public DateTime ToDate { get; set; }

    private readonly List<RecyclePoints> _Points;

    [Display(Name = "Vælg genbrugsplads")]
    public int SelectPointId { get; set; }

    public IEnumerable<SelectListItem> PointItems
    {
      get { return new SelectList(_Points, "PointId", "PointName"); }
    }

    public RecyclePoints RecPoint { get; set; }  

    public ModelVisitList(List<RecyclePoints> points)
    {
      _Points = points;
      //RecPoint = new RecyclePoints();
    }
  }                             

Open in new window


Your Controller also needs some modification:

using GbizzVirk_dk.Models;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace GbizzVirk_dk.Controllers
{
  public class VisitListController : Controller
  {
    // GET: VisitList
    public ActionResult ViewVisitList()
    {

      List<RecyclePoints> PointDDL = new List<RecyclePoints>();

      using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["gbizz"].ConnectionString))
      {
        con.Open();
        SqlCommand sqlCom;
        SqlDataReader sqlReader;

        sqlCom = new SqlCommand("RecyclePointGetInformation", con);
        sqlCom.CommandType = CommandType.StoredProcedure;
        sqlCom.Parameters.Add("@MunicipalityNo", SqlDbType.Int).Value = 630;
        sqlCom.Parameters.Add("@IsVirkDK", SqlDbType.Int).Value = 1;

        sqlReader = sqlCom.ExecuteReader();

        if (sqlReader.HasRows)
        {
          while (sqlReader.Read())
          {
            RecyclePoints pDDL = new RecyclePoints();
            pDDL.PointId = Convert.ToInt32(sqlReader["RecyclingPointId"].ToString());
            pDDL.PointName = sqlReader["PointName"].ToString();
            PointDDL.Add(pDDL);
          }
        }

        sqlReader.Close();
        sqlReader.Dispose();
        sqlCom.Dispose();
        con.Close();
        con.Dispose();
      }

      //Here i declare a new model of the expected type and then i will return the View passing this model
      var model = new ModelVisitList(PointDDL);

      return View(model );
    }
  }
}

Open in new window


Let me know if this helps you solve your problem, and i will also be happy to help you if you need any more clarification.

Giannis
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
poultarpAuthor Commented:
Hi Giannis

That worked perfectly! Thank you very very much indeed :-)

Getting my head round this MVC stuff is proving a challange, so I really appreciate the help from experts like you.

/Søren
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.