Dapper "Get" With Parameter Error: "...Expects Parameter...Which Was Not Supplied"

Question

User-1641868886 posted

Hi,

I am trying to run a stored_proc search of a table with a search parameter supplied through a textbox. My stored_proc takes a "ZipCode", compares with a join, and gets a "MetroArea" to produce search results. Problem is my controller is not "seeing" the value entered into the textbox.

Here is the View:

@model Docx_Site_Web.Models.ProvidersViewModel

@{
    ViewBag.Title = "Index";
}


<div id="ZipInputDiv" style="padding-bottom:40px">


<p><h2>Please enter a Zip/Postal Code for your search:</h2></p>
    <br/>
<p>
    
    <input id="ZipCode" type="text" />&nbsp;&nbsp;
    <input type="submit" id="Searchbtn" class="btn btn-primary" style="height: 30px" value="Search"/>
</p>

</div>

<div id="MetroLblDiv">
    <p><h2>Search Results For:  @Html.LabelFor(m => m.MetroArea)</h2></p>

</div>


<div id="MetroListDiv" class="col-md-9">

   <table class="table table-striped table-responsive">
       <tr>
           <th>Provider Name</th>
           <th>Address</th>
           <th>City</th>
           <th>State</th>
           <th>Zip/Postal Code</th>
       </tr>
       <tbody id="TblData">


       </tbody>

   </table>
    
</div>

Here is the PartialView that loads the table rows:

@model IEnumerable<Docx_Site_Web.Models.ProvidersViewModel>

@if (Model != null)
{

    foreach (var item in Model)
    {
        <tr>
            <td>@item.ProviderName</td>
            <td>@item.Address</td>
            <td>@item.City</td>
            <td>@item.State</td>
            <td>@item.ZipCode</td>
            <td><a href="#"><i class="glyphicon glyphicon-search"></i>Details</a></td>

        </tr>

    }
}

It is called by this JQuery:

$(document).ready(function () {
        $("#MetroLblDiv").hide();
        $("#MetroListDiv").hide();
        $("#Searchbtn").click(function () {

            var ZipCode = $("#ZipCode").val();
            debugger
            $.ajax({

                type: "Post",
                url: "/Providers/Get_ProvidersByMetro?ZipCode" + ZipCode,
                contentType: "html",
                success: function (response) {

                    $("TblData").html(response);
                    $("#MetroLblDiv").show();
                    $("#MetroListDiv").show();

                }
            })
        })

    })

...and finally, here is the associated Controller for the "Index" View and for the "Get" Action:

public ActionResult Index()
        {
            return View();
        }


 public ActionResult Get_ProvidersByMetro(string ZipCode)
        {

            
                DataAccess dataAccess = new DataAccess();
                DynamicParameters param = new DynamicParameters();
                param.Add("@ZipCode", ZipCode);

                DataAccess.ReturnList<ProvidersViewModel>("spProviders_GetByMetro", param);

                return PartialView("Get_ProvidersByMetro", dataAccess);
            

        }

This is a Dapper ORM operation. I have a "DataAccess" class to access the database and provide the instructions for the Action methods. I did not include it, because it works for "GetAll" and other CRUD methods (let me know if useful and I'll post it). My issue is when trying to do an otherwise "basic" get, but with a parameter (the "ZipCode" textbox) to limit/segregate the search results.

If anyone can tell me what is wrong, plz suggest. My error on debug stops after the "param.Add..." and it reads "spProviders_GetByMetro expects parameter '@ZipCode' which was not supplied." "ZipCode" is null on debug.

Thx much!

Reid C.

Answer 1

User-821857111 posted

<input id="ZipCode" type="text" />

You must provide a value for the "name" attribute on your form inputs. Model Binding works with the "name" values, not the "id":

<input id="ZipCode" name="ZipCode"  type="text" />

Answer 2

User-1641868886 posted

Mike, thanks for the response and the help. I did that change, now on my controller method I get the ZipCode I input on the page. 

So now my code breaks when I go to the next step. This is a revised method from the original I posted:

public ActionResult Get_ProvidersByMetro(string ZipCode)
        {

            
                DataAccess dataAccess = new DataAccess();
                DynamicParameters param = new DynamicParameters();
                param.Add("@ZipCode", ZipCode);

                return PartialView(DataAccess.ReturnList<ProvidersViewModel>("spProviders_GetByMetro", param));
            
        }

...and the previous iteration of the "Get_ProvidersByMetro" still broke at the "return partialView" line but does not provide an error msg.

Do you see anything in the last part of this method I might try to re-work? I've tried about 3-4 versions.

Thx much!

Answer 3

User-821857111 posted

This should really be a separate question, but try separating the return View statement and the creation of the model:

var model = DataAccess.ReturnList<ProvidersViewModel>("spProviders_GetByMetro", param);
return PartialView(model);

Run SqlProfiler to ensure that the procedure is being run and returns data as expected, and add a breakpoint to the return PartialView line so that you can examine the model.

Answer 4

User-1641868886 posted

Thanks much Mike. This modif. called the stored_proc, got the expected result set, and rendered the partial view on the page. I really appreciate the assist!

One last issue, and it may or may not be a simple oversight on my part. I included in the stored_proc the column "MetroArea" and I used this (below) code to try to show it on the main ("Index") page. I see now I did this incorrectly. I am trying to show it on the "Index" as I think I may have a problem showing it on the partial view where I declare an "IEnumberable" of my model.

<div id="MetroLblDiv">
    <p><h2>Search Results For:  @Html.LabelFor(m => m.MetroArea)</h2></p>

</div>

I am thinking I left off any reference to this Label/result in my "Get_ProvidersByMetro" controller method. Either that, or my jQuery needs to cover this element. I am still trying to learn to incorporate the three parts (method, view, and script) so I am not sure whether the method or the jQuery fills this label. Can you tell if I am on the right track looking at these-script and/or method-or does my tag look ill-conceived?

Thx again!

Answer 5

User-821857111 posted

You should only use the LabelFor helper if you want to render a <label> element (which is intended for use in forms), and you shouldn't wrap <h2> tags in <p> tags. Try the following:

<div id="MetroLblDiv">
    <h2>Search Results For:  @Model[0].MetroArea</h2>

</div>

I'm guessing that your ViewModel is basically an IEnumerable<something>, where the something has a MetroArea property. If that doesn't work, post the definition of your view model.

Answer 6

User-1641868886 posted

Mike, sorry for delay...day job & all!

I changed location of the "MetroLblDiv" to the PartialView which is fired by the button action--I need the result of the button action to give me the parameter's data anyway.

So...here is the complete PartialView "Get_ProvidersByMetro.cshtml" with the heading declaring the "@model. IEnumerable..." and I've also included the model class.

@model IEnumerable<Docx_Site_Web.Models.ProvidersViewModel>

@if (Model != null)
{


    <div id="MetroLblDiv">
        <h2>Search Results For:  @Model[0].MetroArea</h2>

    </div>

    foreach (var item in Model)
    {
        <tr>
            <td>@item.ProviderName</td>
            <td>@item.Address</td>
            <td>@item.City</td>
            <td>@item.State</td>
            <td>@item.ZipCode</td>
            <td><a href="#"><i class="glyphicon glyphicon-search"></i>Details</a></td>

        </tr>

    }
}

------------------
Model:

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

namespace Docx_Site_Web.Models

{
    public class ProvidersViewModel
    {

        public int Id { get; set; }

        [Required]
        [Display(Name = "Provider Name")]
        public string ProviderName { get; set; }

        [Required]
        [Display(Name = "Address")]
        public string Address { get; set; }

        [Required]
        [Display(Name = "City")]
        public string City { get; set; }

        [Required]
        [Display(Name = "State")]
        public string State { get; set; }

        [Required]
        [Display(Name = "Zip/Postal Code")]
        public string ZipCode { get; set; }

        public int MetrosId { get; set; }

        [Display(Name = "Metro Area")]
        public string MetroArea { get; set; } 
    
     }
}

On the PartialView, I have a syntax error under "@Model[0]" and it reads "cannot apply indexing with [] to an expression of type 'IEnumerable<ProvidersViewModel>'."

So to recap, I'm trying to place a label over the table on the page that will state:  "Search Results for:  XXX (Denver, Memphis, etc.)" using the "MetroArea" property. On a page with an "IEnumberable" of a model, I am getting persistent error on attempting to place a label with one of the properties as the variable.

This is surely not a high-level thing, so apologies that I'm in the learning phase here. Briefly, I started learning with several (datalist, jQuery datatable, etc.) examples of "Get" methods (using "IEnumberable" property to achieve a list result). None of the examples I've seen demonstrates use of a variable OUTSIDE the "for each" loop on an IEnumerable view.

Thanks again for looking at this, really appreciate your (or any others) suggestion.

UPDATE:  Further reading, I have seen references to using "First()" method of IEnumerable. Not sure how or where to use this, on PartialView, or in controller "Get" method?

Reid C.

Answer 7

User-1641868886 posted

Mike,

Hey sorry about the long note below. For once I did something on my own!

Using what was in the red "UPDATE" paragraph at the bottom of the following post, I used this revision:

var city = Enumerable.First(Model);
    <h2>Search Results For:&nbsp;&nbsp; @city.MetroArea Metro Area</h2>

...and this produced the heading for the table where the "MetroArea" variable corresponds to my search result.

Thanks again for your help and review. On to the next challenge!!

Thx,

Reid C.

Answer 8

User-1641868886 posted

Mike, if you (or someone else) sees this, I'd like to ask for one more assist. I have changed the page to a jquery dataTable. So the "Enumerable.First(Model)" method is no longer operable, as I'm not using the "IEnumerable" of my model, but rather a script tag and the jQuery is drawing the table. Here is the new controller method:

public ActionResult GetProvidersByMetro(string ZipCode)
        {

            DataAccess da = new DataAccess();
             
            DynamicParameters param = new DynamicParameters();
            param.Add("@ZipCode", ZipCode);

            var model = DataAccess.ReturnList<ProvidersViewModel>("spProviders_GetByMetro", param).ToList<ProvidersViewModel>();

            return Json(new { data = model }, JsonRequestBehavior.AllowGet);

        }

My table is fine, but my heading "@city.MetroArea" no longer works. I'm not sure if I need to have the script tag take care of this, or the controller method, like with a label control on the page--then there's the issue of an error if my label tries to parse before the "Searchbtn" click event in the script tag.

Any thoughts appreciated. Let me know if you need to see the script for the ("#Searchbtn").click()  event that runs the dataTable,