SqlCommand not accepting SqlParameter

[Schol-R-LEA]

I have an event handler that is failing with an SqlException '{"Procedure or function 'SetClientEmail' expects parameter '@OldEmailAddress', which was not supplied."}' However, the code for the SqlParameter '@OldEmailAddress' is no different than that for the other SqlParameters I am using, both for this command and for others I have been able to run successfully.

protected void ChangeAddress(object sender, EventArgs e)
    {
        string newEmail = NewEmailTextBox.Text;
        if (newEmail == ConfirmTextBox.Text)
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ServicePro"].ConnectionString);

            SqlCommand command = new SqlCommand("SetClientEmail", conn);
            command.CommandType = CommandType.StoredProcedure;
            SqlParameter key = new SqlParameter("@AccountKey", SqlDbType.Int);
            key.Value = accountKey;
            command.Parameters.Add(key);
            SqlParameter oldEmailAddress = new SqlParameter("@OldEmailAddress", SqlDbType.Text);
            oldEmailAddress.Value = email;
            command.Parameters.Add(oldEmailAddress);
            SqlParameter newEmailAddress = new SqlParameter("@NewEmailAddress", SqlDbType.Text);
            newEmailAddress.Value = newEmail;
            command.Parameters.Add(newEmailAddress);

            conn.Open();
            command.ExecuteNonQuery();
            conn.Close();
            Response.Redirect("~/Start.aspx");
        }
        else
        {
            ConfirmTextBox.Text = "";
            Validate();
        }
    }

I can only conclude that I am missing something important, but I cannot determine what. Any advice on this matter would be appreciated.

Have you tried running the stored procedure in SSMS (SQL Server Management Studio) to see if it is the stored procedure?

[Schol-R-LEA]

Have you tried running the stored procedure in SSMS (SQL Server Management Studio) to see if it is the stored procedure?

Yes. The SP seems to work correctly when run manually; I confirmed this before writing the event handler.

USE [ServicePro]
GO
/****** Object:  StoredProcedure [dbo].[SetClientEmail]    Script Date: 05/26/2011 17:10:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		J Osako
-- Create date: 2011-05-25
-- Description:	Change the email address of the given 
-- account, and log the change and old email address
-- =============================================
ALTER PROCEDURE [dbo].[SetClientEmail]
    @AccountKey INT,
    @OldEmailAddress TEXT,
	@NewEmailAddress TEXT

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	INSERT INTO tblEmailChangeLog (AccountKey, OldEmailAddress, NewEmailAddress)
	VALUES (@AccountKey, @OldEmailAddress, @NewEmailAddress);

    -- Insert statements for procedure here
	UPDATE tblMainAccounts SET EMailAddress = @NewEmailAddress
	WHERE AccountKey = @AccountKey;
END

The problem appears to be in passing the parameters. Since the parameters do show up (apparently correctly) in the SQlCommand object when I watch it in the debugger, and the names in both the SP and the event handler match (I even cut and pasted the problem one out of the SP to make sure), it is hard to see how this could be.

As I said, there are other queries in which the parameters are matching correctly. The biggest difference between this one and the others (well, aside from the differing stored procedures and parameters) is that this one is an insert and an update rather than a select, and run using ExecuteNonQuery() rather than ExecuteReader() .

[aspproject]

you missed writing number of parameters ... write code like
SqlParameter[] ISqlPara = new SqlParameter[2];
ISqlPara[0] = new SqlParameter("@NewsID", NewsID);
ISqlPara[1] = new SqlParameter("@newsname", Newsname);

[Schol-R-LEA]

I realize that this is what the error message is asserting, but if you look at my code, you'd see that the parameter in question is in fact being added to the SqlCommand.

I don't see how using an array would make the code any clearer, as it actually loses some information about the parameters (the name of the SqlParameter, which relates it to the parameter it represents). Could you elucidate further on why that would be a better approach?

[NETProgrammer]

You can try something like this

command.Parameters.Add("@AccountKey", SqlDbType.Int).Value = accountKey;

Repeat this for all of your parameters.