Grabbing data from database based on dropdownlist selection

Question

I am porting a windows desktop application to an ASP.NET MVC application. Currently I have a table in the database that stores a bunch of race events and I want to display the list of registrants for that event. I have managed to display all the registrants by hard coding the specific event title from the database. I have managed to load the dropdownlist from the database table. Now I want to display the registrants based on a dropdownlist selected item, but I don't really know how to convert the selectlist as string so the where clause can match the content.

this is the controller

private raceEntities db = new raceEntities();
    // GET: Registrants
    public ActionResult Index()
    {


        ViewBag.RaceEvents = new SelectList(db.raceevents, "RaceEventID", "Name");

        var registrants = (from per in db.people
                           join personorgrole in db.personorganizationroles on per.PersonID equals personorgrole.PersonID
                           join personeve in db.personevents on personorgrole.PersonOrganizationRoleID equals personeve.PersonOrganizationRoleID
                           join eventcla in db.eventclasses on personeve.RaceEventID equals eventcla.RaceEventID
                           join personeventcla in db.personeventclasses on eventcla.EventClassID equals personeventcla.EventClassID
                           join raceeve in db.raceevents on eventcla.RaceEventID equals raceeve.RaceEventID
                           join organizationrolety in db.organizationroletypes on personorgrole.OrganizationRoleTypeID equals organizationrolety.OrganizationRoleTypeID
                           //where raceeve.Name == "Ruapehu Gravity Festival" //hardcoded event title
                           //where raceeve.Name == RaceEvents  raceeve.Name matches the selectlist above 

                           select new Registrants { LastName = per.LastName, FirstName = per.FirstName, RoleType = organizationrolety.Name }).Distinct().OrderBy( per => per.LastName);

        return View(registrants);
    }

this is the view

@model IEnumerable<IDFWebApp.Models.Custom.Registrants>
@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>
@using (Html.BeginForm())
{
@Html.DropDownList("RaceEvents")

<table style="width:100%">
    <tr>
        <th>First Name</th>
        <th>Last Name</th>
        <th>Role Type</th>
    </tr>
    @foreach (var person in Model)
    {
        <tr>
            <td>@person.LastName</td>
            <td>@person.FirstName</td>
            <td>@person.RoleType</td>
        </tr>
    }
</table>

}

You need to handle the .change() event of the dropdownlist with javascript/jquery, then post the selected value to a controller method that returns a partial view (or json) of the filtered list and update the DOM. Although if you are initially displaying all items in the collection you could just filter the list on the client by showing/hiding the appropriate items — user3559349
– user3559349, Commented May 5, 2015 at 0:44
I have edited your title. Please see, "Should questions include “tags” in their titles?", where the consensus is "no, they should not". — John Saunders
– John Saunders, Commented May 5, 2015 at 1:47

John Saunders · Accepted Answer · 2015-05-05 01:43:38Z

You have a few issues. The first is you are missing is how to construct the model your view uses. You need something to hold the data the user is passing back to the controller. I added the RaceEvent property. You also need a property to hold the results of the query. That is the Registrants property. I added the RegistrantsModel which is renamed, but similar to the model you are using now.

namespace IDFWebApp.Models.Custom
{
    public class RegistrantsModel
    {
        public string LastName { get; set; }
        public string FirstName { get; set; }
        public organizationrolety RoleType { get; set; }
    }

    public class RegistrantsIndexModel
    {
        public string RaceEvent { get; set; }
        public IEnumerable<IDFWebApp.Models.Custom.RegistrantsModel> Registrants { get; set; }
    }
}

You also need a post method in your controller:

// Post: Registrants
[HttpPost]
public ActionResult Index(IDFWebApp.Models.Custom.RegistrantsModel model)
{
    model.Registrants = (
            from per in db.people
            join personorgrole in db.personorganizationroles on per.PersonID equals personorgrole.PersonID
            join personeve in db.personevents on personorgrole.PersonOrganizationRoleID equals personeve.PersonOrganizationRoleID
            join eventcla in db.eventclasses on personeve.RaceEventID equals eventcla.RaceEventID
            join personeventcla in db.personeventclasses on eventcla.EventClassID equals personeventcla.EventClassID
            join raceeve in db.raceevents on eventcla.RaceEventID equals raceeve.RaceEventID
            join organizationrolety in db.organizationroletypes on personorgrole.OrganizationRoleTypeID equals organizationrolety.OrganizationRoleTypeID
            where raceeve.Name == model.RaceEvents
            select new Registrants { LastName = per.LastName, FirstName = per.FirstName, RoleType = organizationrolety.Name }).Distinct().OrderBy( per => per.LastName);

    return View(model);
}

That probably isn't everything, but it should get you farther.

Collectives™ on Stack Overflow

Grabbing data from database based on dropdownlist selection

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