Unable to pass string as a parameter to a stored procedure in ASP.NET MVC

Question

I am stuck with an issue of passing a string value as a parameter to a stored procedure in C# ASP.NET MVC. Any help is greatly appreciated.

I tried various options of setting the parameter, like

cmd.Parameters.AddWithValue("@P_ORG", pOrg);
cmd.Parameters.Add(pOrg);

Code snippet:

using (SqlConnection con = new SqlConnection(cs))
{
   con.Open();

   SqlParameter param = new SqlParameter()
   {
      ParameterName = "@P_ORG",
      Value = pOrg
   };

   SqlCommand cmd = new SqlCommand("getCCM", con);
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Parameters.Add(param);

   using (SqlDataReader rdr = cmd.ExecuteReader())
   {  
      //do something 
   }
}

where are you assigning the value of `pOrg` can you edit this and show all relevant code as it pertains to your issue..? — MethodMan, May 17 '17 at 15:33
@MaLiN2223 method signature would not make or break his case.. there is enough code here to determine what their intent is ..with the exception of the missing `pOrg` value and where it's being assigned.. it would be nice to see the actual Stored Procedure though — MethodMan, May 17 '17 at 15:34
May be adding parameter type along with name and value like `SqlDbType = SqlDbType.VarChar` will help. — Pankaj Kapare, May 17 '17 at 15:45
The pOrg value is coming from the view and I printed it our it is coming correctly. I also tried providing the the datatype as VarChar. SELECT distinct name FROM CC_ORG where P_ORG = @P_ORG — asreeram, May 17 '17 at 15:51
No errors, only its not retrieving any data form the procedure. — asreeram, May 17 '17 at 15:59
I hope you are using `while (rdr.Read()) {//your code}` inside using statement. — Pankaj Kapare, May 17 '17 at 16:10

score 1 · Answer 1 · edited May 23 '17 at 11:47

I hope that below example will solve your problem.

command.Parameters.AddWithValue("@parameterID", parameter1);

Official definition:

AddWithValue replaces the SqlParameterCollection.Add method that takes a String and an Object. The overload of Add that takes a string and an object was deprecated because of possible ambiguity with the SqlParameterCollection.Add overload that takes a String and a SqlDbType enumeration value where passing an integer with the string could be interpreted as being either the parameter value or the corresponding SqlDbType value. Use AddWithValue whenever you want to add a parameter by specifying its name and value.

Source: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396

Similar issue links with solution:

https://social.msdn.microsoft.com/Forums/vstudio/en-US/e600eb8d-6c06-4c9e-ad37-5a7538c9accf/unable-to-execute-stored-procedure-with-parameters-in-mvc?forum=csharpgeneral

Difference between Parameters.Add and Parameters.AddWithValue

Kindly let me know your thoughts or feedbacks

Thanks

Karthik MCP,MCSA

Hi Kathik, I started of initially with AddWithValue as I mentioned in my post. It isn't working. I am using MVC framework 4.5.2 and below is the definition of my Action if this help: public JsonResult getCCM(string performingOrg) — asreeram, May 17 '17 at 16:25
Just an fyi, when I hard code the query instead of calling the procedure in my application it works fine.But I don;t want to hard code the query. — asreeram, May 17 '17 at 16:30

score 0 · Answer 2 · answered May 17 '17 at 15:50

0

As said in comments by Pankaj Kapare, you have to add

SqlDbType = SqlDbType.VarChar

to your SqlParameter object.

answered May 17 '17 at 15:50

Jeff Sall

27
5

just tried this without any success: SqlParameter param = new SqlParameter() { SqlDbType = SqlDbType.VarChar, ParameterName = "@PERFORMING_ORG", Value = performingOrg }; – asreeram May 17 '17 at 16:06
Just an fyi, when I hard code the query instead of calling the procedure in my application it works fine.But I don;t want to hard code the query. – asreeram May 17 '17 at 16:30
What's the exception you are getting? – Jeff Sall May 17 '17 at 16:40
Unfortunately no exception. I am sure its executing the procedure but looks like the parameter value is not getting passed. I don;t know how to check the value of the parameter once its passed to the stored procedure. In my application before I call the stored procedure the value is correct. – asreeram May 17 '17 at 16:53
SSMS comes with a profiler that is pretty good. I've used it to debug things like this before as well. Just google around for SSMS SQL Server Profiler for how to use it. Also what is the data type of your local pOrg variable. Are you extra sure you are casting it correctly. – Jeff Sall May 17 '17 at 17:06
yes sir the action definition is public JsonResult getCCMgr(string pOrg) – asreeram May 17 '17 at 17:19

Unable to pass string as a parameter to a stored procedure in ASP.NET MVC

2 Answers2