Stored Procedure - Object must implement IConvertible.

[virlinz]

Hi..
I have a problem with implementing a stored procedure which returns a value to the caller..

I want to verify whether the given username and email address exist in the database. The return value of the stored procedure will determine whether the input matches.

This is my stored procedure

CREATE PROCEDURE dbo.RetrievePassword

(
@Username varchar(25),
@EMail varchar(40)
)

AS
DECLARE @intValid int
SELECT @intValid = COUNT(*)
FROM Profile where Username = @Username AND EMail = @EMail

Return @intValid


This is my C# codes under the button click function

SqlConn = new SqlConnection(ConfigurationSettings.AppSettings["SqlServerSite"]);
cmdRetrieve = new SqlCommand("RetrievePassword", SqlConn);
cmdRetrieve.CommandType = CommandType.StoredProcedure;

cmdRetrieve.Parameters.Add("@Username", SqlDbType.VarChar, 25).Value = txtUsername;

cmdRetrieve.Parameters.Add("@EMail", SqlDbType.VarChar, 40).Value = txtEmailAdd;

SqlParameter PrmReturn = new SqlParameter("@intReturn", SqlDbType.Int);
PrmReturn.Direction = ParameterDirection.ReturnValue;

SqlConn.Open();
cmdRetrieve.ExecuteNonQuery();
SqlConn.Close();

int intValid = Convert.ToInt32(PrmReturn.Value);

if(intValid != 0)
Response.Write("EMail has been sent");

else
Response.Write("Details not matched");


The error is Object must implement IConvertible[/COLOR]. The source error is at the cmdRetrieve.ExecuteNonQuery() line.

Where did I do wrong? I followed an example in VB.NET (and made some changes)on passing value from stored procedure I found on the Internet. Please shed some light for me. Thanks in advance.

[coolbiz]

What are txtUsername and txtEmailAdd? Are those TEXTBOXes? If so, you need to use the .TEXT property to assign the values in them to the SP.

cmdRetrieve.Parameters.Add("@Username", SqlDbType.VarChar, 25).Value = txtUsername.Text;

cmdRetrieve.Parameters.Add("@EMail", SqlDbType.VarChar, 40).Value = txtEmailAdd.Text;

[virlinz]

Thank You!
I was focusing so much on cmdRetrieve.ExecuteNonQuery() and the SP I didn't even check the Textboxes properties. Thanks very much :blush:

[dj.rock]

hi coolbiz

I saw your solution for above problem.but i am getting error like
"Object Must Implement IConvertible"..and i have done what u said but still i am getting this error.

And i dont know from which part of my code this error is coming ....following is my code

just have a look and tell me solution ....

SqlConnection sqlcon = new SqlConnection(ConnectionString);
SqlCommand sqlEInsertCommand = new SqlCommand();
sqlEInsertCommand.Connection = sqlcon;
sqlEInsertCommand.Connection.Open();
EmpPer = update Statement;
sqlEInsertCommand.CommandType = CommandType.Text;
sqlEInsertCommand.CommandText = EmpPer;

sqlEInsertCommand.Parameters.Add("@variable",SqlDbType.Decimal ,9,"dbcolumn");
if(txtbox.Text=="")
{
sqlEInsertCommand.Parameters["@variable"].Value = sqlnull;
}
else
{
sqlEInsertCommand.Parameters["@variable"].Value = txtbox.Text;
}
sqlEInsertCommand.ExecuteNonQuery();


Please tell me whats wrong in this code
Thanks in advance