[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MVC 5: Binding data from SQL to DropDownList

Posted on 2014-07-17
4
Medium Priority
?
3,290 Views
Last Modified: 2014-07-22
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.
0
Comment
Question by:poultarp
  • 2
  • 2
4 Comments
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 40203891
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
 
LVL 1

Author Comment

by:poultarp
ID: 40210870
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
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 2000 total points
ID: 40210939
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
 
LVL 1

Author Closing Comment

by:poultarp
ID: 40211009
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A publishing tool, a Version Control System, or a Collaboration Platform! These can be some of the defining words for the two very famous web-hosting Git repositories: Bitbucket and Github. Git is widely used amongst the programmers and developers f…
Without even knowing it, most of us are using web applications on a daily basis.  In fact, Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We generally confuse these web applications to…
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.
Suggested Courses

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question