Calling a stored procedure with parameters in ASP.NET MVC
I have a working controller for another stored procedure in the database, but I am trying to test another.
When I ask for a url;
I am getting the following error, which is understandable, but I cannot figure out why this is happening:
The procedure or function 'esp_GetPlacesWithinGeoSpan' expects a '@MinLat' parameter that 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 in solving this problem is greatly appreciated.
+2
a source to share
2 answers
If you want to pass parameters using text type
CommandType.Text
then you have to pass parameters like this:
GetPlacesWithinGeoSpan @MinLat, @MaxLat, @MinLng, @MaxLng
Because the way you do it, like your pass parameters, but it doesn't match against anything, so it's ignored.
Hope it helps
+1
a source to share