Failed to convert parameter value from a SqlParameter to a String RSS

• • Reply
  

  balajime

  0 Points

  6 Posts

  Failed to convert parameter value from a SqlParameter to a String

  Apr 13, 2010 01:33 AM|LINK

  I am using VS2008 with asp.net 3.5 and enterprise library 4.1. I am getting the following error when I try to connect to database.
  "Failed to convert parameter value from a SqlParameter to a String"
  
  Here is my code

  <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">Database db = DatabaseFactory.CreateDatabase("SQLSRVR");</div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste"> </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">string myStoredProcedure = "usp_GetPersonContact";</div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste"> </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">SqlParameter[] SQLParameters = new SqlParameter[1];</div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">SQLParameters[0] = new SqlParameter("@ln", SqlDbType.NVarChar, 50);            </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">SQLParameters[0].Value = "Smith"; </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">SQLParameters[0].Direction = ParameterDirection.Input;</div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste"> </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">DataSet resultDataSet = null;</div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">resultDataSet = db.ExecuteDataSet(myStoredProcedure, SQLParameters);     </div>

  Database db = DatabaseFactory.CreateDatabase("SQLSRVR");

  string myStoredProcedure = "usp_GetPersonContact";

  SqlParameter[] SQLParameters = new SqlParameter[1];

  SQLParameters[0] = new SqlParameter("@lastname", SqlDbType.NVarChar, 50);            

  SQLParameters[0].Value = "Smith"; 

  SQLParameters[0].Direction = ParameterDirection.Input;

  DataSet resultDataSet = null;

  resultDataSet = db.ExecuteDataSet(myStoredProcedure, SQLParameters);     

  
  

  I already tried conversion but seems nothing is working. Please let me know what i am doing wrong. Thanks.

  • Moved by mbanavige on Apr 13, 2010 01:44 AM
• • Reply
  

  ammd

  Participant

  1349 Points

  257 Posts

  Re: Failed to convert parameter value from a SqlParameter to a String

  Apr 13, 2010 02:52 AM|LINK

  Seems fine, you sure your issue is not in the sp? 

  If this fixed your issue then please 'Mark as Answer'
• • Reply
  

  balajime

  0 Points

  6 Posts

  Re: Failed to convert parameter value from a SqlParameter to a String

  Apr 13, 2010 03:00 AM|LINK

  I think so. 

  
  

  here is the code for SP

  
  

  <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">ALTER Procedure [dbo].[usp_GetPersonContact](@ln varchar(50))   </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">as </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">select * from Person.Contact where LastName = @ln</div>

  ALTER Procedure [dbo].[usp_GetPersonContact](@ln varchar(50))   

  as 

  select * from Person.Contact where LastName = @ln

  
  

  
  

• • Reply
  

  balajime

  0 Points

  6 Posts

  Re: Failed to convert parameter value from a SqlParameter to a String

  Apr 13, 2010 03:01 AM|LINK

  Parameter name is @lastname not @ln

• • Reply
  

  ammd

  Participant

  1349 Points

  257 Posts

  Re: Failed to convert parameter value from a SqlParameter to a String

  Apr 13, 2010 02:57 PM|LINK

  As mentioned the parameter name is @In and also the sqldbtype should be varchar, not nvarchar. 

  If this fixed your issue then please 'Mark as Answer'
• • Reply
  

  balajime

  0 Points

  6 Posts

  Re: Failed to convert parameter value from a SqlParameter to a String

  Apr 13, 2010 07:19 PM|LINK

  Thanks for your time and help.
  
  Still is not working. Here is the latest code
  
  C# 
  
  Database db = DatabaseFactory.CreateDatabase("SQLSRVR");
  
  string myStoredProcedure = "usp_GetPersonContact";
  
  SqlParameter[] SQLParameters = new SqlParameter[1];
  
  SQLParameters[0] = new SqlParameter("@lastname", SqlDbType.VarChar, 50, "LastName"); 
  
  SQLParameters[0].Value = "Smith"; 
  
  SQLParameters[0].Direction = ParameterDirection.Input;
  
  DataSet resultDataSet = null;
  
  resultDataSet = db.ExecuteDataSet(myStoredProcedure, SQLParameters);
  
  Storeprocedure
  
  ALTER Procedure [dbo].[usp_GetPersonContact]@lastname varchar(50) 
  as 
  select * from Person.Contact where LastName = @lastname

• • Reply
  

  balajime

  0 Points

  6 Posts

  Re: Failed to convert parameter value from a SqlParameter to a String

  Apr 20, 2010 06:29 AM|LINK

  Any help folks? My requirement is to pass stored procedure with SQL parameters together

• • Reply
  

  sathish_juk

  Participant

  1777 Points

  361 Posts

  Re: Failed to convert parameter value from a SqlParameter to a String

  Apr 20, 2010 06:46 AM|LINK

  Database db = DatabaseFactory.CreateDatabase("SQLSRVR");
  
  string myStoredProcedure = "usp_GetPersonContact";
  
  SqlParameter[] SQLParameters = new SqlParameter[1];

  
  
  SQLParameters[0] = new SqlParameter("@lastname", "Ur LastName Value"); 
  
  
  
  DataSet resultDataSet = null;
  
  resultDataSet = db.ExecuteDataSet(myStoredProcedure, SQLParameters);
   

  Thanks and Regards,
  Sathish Kumar
  
  Mark this post as "ANSWER", if it helped you..
  • Edited by sathish_juk on Apr 20, 2010 07:10 AM
• • Reply
  

  Omkar Lale

  Participant

  1288 Points

  223 Posts

  Re: Failed to convert parameter value from a SqlParameter to a String

  Apr 20, 2010 08:08 AM|LINK

  Hi,

  try this

  First add references

     using System.Data.Common;

     using Microsoft.Practices.EnterpriseLibrary.Data;

   

  Then your code will be:

      Database db = DatabaseFactory.CreateDatabase("SQLSRVR");

      string myStoredProcedure = "usp_GetPersonContact";

      DbCommand cmd = db.GetStoredProcCommand(myStoredProcedure);

      SqlParameter param = new SqlParameter("lastname",SqlDbType.NVarChar);
      param.Value = "Smith";
      param.Direction = ParameterDirection.Input;

      cmd.Parameters.Add(param);

      DataSet resultDataSet = null;

      resultDataSet = db.ExecuteDataSet(cmd);

  Thanks & Regards,
  
  Omkar A. Lale
  Keep your words soft and arguments hard.
  
  ~Do Mark as Answer if it solves your query~
  
• • Reply
  

  balajime

  0 Points

  6 Posts

  Re: Failed to convert parameter value from a SqlParameter to a String

  Apr 27, 2010 10:20 PM|LINK

  Thanks your time and help Sathish Kumar and Omkar lale. 

  
  

  Sathish kumar -- i tried your approach but no luck. I am getting the same error. thanks.

  
  

  Omkar lale - Currently, I am implemented your code only. But the issue in this approach is we need to

  add all sql parameters into command object and execute it.