2

I have a working controller for another stored procedure in the database, but I am trying to test another.

When I request the URL;

http://host.com/Map?minLat=0&maxLat=50&minLng=0&maxLng=50

I get the following error message, which is understandable but I can't seem to find out why it occurs;

Procedure or function 'esp_GetPlacesWithinGeoSpan' expects parameter '@MinLat', which was not supplied.

This is the code I am using.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.Mvc;
    using System.Web.Mvc.Ajax;
    using System.Data;
    using System.Text;
    using System.Data.SqlClient;

namespace prototype.Controllers
{
    public class MapController : Controller
    {
        //Initial variable definitions
        //Array with chars to be used with the Trim() methods
        char[] lastComma = { ',' };

        //Minimum and maximum lat/longs for queries
        float _minLat;
        float _maxLat;
        float _minLng;
        float _maxLng;


        //Creates stringbuilder object to store SQL results
        StringBuilder json = new StringBuilder();

        //Defines which SQL-server to connect to, which database, and which user
        SqlConnection con = new SqlConnection(...connection string here...);

        // 
        // HTTP-GET: /Map/ 

        public string CallProcedure_getPlaces(float minLat, float maxLat, float minLng, float maxLng)
        {
            con.Open();

            using (SqlCommand cmd = new SqlCommand("esp_GetPlacesWithinGeoSpan", con))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@MinLat", _minLat);
                cmd.Parameters.AddWithValue("@MaxLat", _maxLat);
                cmd.Parameters.AddWithValue("@MinLng", _minLng);
                cmd.Parameters.AddWithValue("@MaxLng", _maxLng);


                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        json.AppendFormat("\"{0}\":{{\"c\":{1},\"f\":{2}}},", reader["PlaceID"], reader["PlaceName"], reader["SquareID"]);
                    }
                }
                con.Close();
            }
            return "{" + json.ToString().TrimEnd(lastComma) + "}";
        }


        //http://host.com/Map?minLat=0&maxLat=50&minLng=0&maxLng=50
        public ActionResult Index(float minLat, float maxLat, float minLng, float maxLng)
        {
            _minLat = minLat;
            _maxLat = maxLat;
            _minLng = minLng;
            _maxLng = maxLng;

            return Content(CallProcedure_getPlaces(_minLat, _maxLat, _minLng, _maxLng));
        }
    }
}

Any help on resolving this problem would be greatly appreciated.

cc0
  • 1,960
  • 7
  • 40
  • 57
  • 3
    As an aside, you really shouldn't be performing data access in your controllers. This is best achieved by using another layer, such as a repository. – Dan Diplo Apr 22 '10 at 16:26
  • Could you expand on that? Or perhaps show me an example? I'm not very familiar with asp.net at all, I just figured this was a simple and therefore decent way of doing it. I'd love to learn. – cc0 Apr 22 '10 at 23:55
  • @cc0 Here's a great and perfectly simple example of using ADO.net (SqlConnection) with a Repository in MVC: http://stackoverflow.com/a/6694195/555798 – MikeTeeVee May 21 '14 at 05:57

2 Answers2

3

Your CommandType is wrong. It should be:

cmd.CommandType = CommandType.StoredProcedure;

Since you're using CommandType.Text, my guess would be that ADO.NET is trying to map parameters into the text of the query rather than generating the proper call to the Stored Procedure.

Justin Niessner
  • 242,243
  • 40
  • 408
  • 536
  • Awesome! Lol. I can't believe I missed that! I was using the text to test before I had created the procedure. Thanks! Mental hiccup there. – cc0 Apr 22 '10 at 16:19
1

If you wish to pass the parameters using the text type

CommandType.Text

then you should pass the parameters like this:

GetPlacesWithinGeoSpan @MinLat, @MaxLat, @MinLng, @MaxLng

Because the way your doing it its like your passing parameters but it don't map to anything so it's ignored.

Hope it helps

Gabriel Guimarães
  • 2,724
  • 3
  • 27
  • 41