Failed to convert parameter value from a SqlParameter to a String

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.

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

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

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

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

select * from Person.Contact where LastName = @ln

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

Parameter name is @lastname not @ln

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

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

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

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

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

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

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

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);

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

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);

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

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.

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

go through this link

http://msdn.microsoft.com/en-us/library/dd140044.aspx