CODEWITHSUNDEEP
c#sql-serverasp.net-mvc
asreeram
asreeram

Reputation: 31

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

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 
   }
}

Upvotes: 0

Views: 1595

Answers (2)

Karthik Elumalai
Karthik Elumalai

Reputation: 1612

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

Upvotes: 1

Jeff Sall
Jeff Sall

Reputation: 27

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

SqlDbType = SqlDbType.VarChar

to your SqlParameter object.

Upvotes: 0

Related Questions