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
– MethodMan, Commented May 17, 2017 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
– MethodMan, Commented May 17, 2017 at 15:34
May be adding parameter type along with name and value like SqlDbType = SqlDbType.VarChar will help. — Pankaj Kapare
– Pankaj Kapare, Commented May 17, 2017 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
– asreeram, Commented May 17, 2017 at 15:51

Community · Accepted Answer · 2017-05-23 11:47:21Z

1

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

edited May 23, 2017 at 11:47

CommunityBot

11 silver badge

answered May 17, 2017 at 16:15

Karthik Elumalai

1,6121 gold badge13 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

asreeram Over a year ago

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 Over a year ago

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.

Jeff Sall · Accepted Answer · 2017-05-17 15:50:29Z

0

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

SqlDbType = SqlDbType.VarChar

to your SqlParameter object.

answered May 17, 2017 at 15:50

Jeff Sall

275 bronze badges

6 Comments

asreeram Over a year ago

just tried this without any success: SqlParameter param = new SqlParameter() { SqlDbType = SqlDbType.VarChar, ParameterName = "@PERFORMING_ORG", Value = performingOrg };

asreeram Over a year ago

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.

Jeff Sall Over a year ago

What's the exception you are getting?

asreeram Over a year ago

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.

Jeff Sall Over a year ago

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.

|

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related