0

Getting a "Procedure or function 'InsertAccount' expects parameter '@AccountId', which was not supplied.", however the stored procedure does have this parameter in its parameter list.

The C# code:

using (var connection = new SqlConnection(Settings.Default.InternalConnection))
            {
                connection.Open();
                using (var command = new SqlCommand { Connection = connection, CommandText = Settings.Default.AccountInsertRoutine, CommandType = CommandType.StoredProcedure, CommandTimeout = 120 })
                {
                    var parameter0 = new SqlParameter { ParameterName = "@AccountName", Value = Account.AccountName, SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Input };
                    command.Parameters.Add(parameter0);

                    var parameter1 = new SqlParameter { ParameterName = "@AccountKey", Value = Account.AccountKey, SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Input };
                    command.Parameters.Add(parameter1);

                    var parameter2 = new SqlParameter { ParameterName = "@AccountTypeId", Value = Account.AccountTypeId, SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input };
                    command.Parameters.Add(parameter2);

                    var parameter3 = new SqlParameter { ParameterName = "@FundId", Value = Account.FundId, SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input };
                    command.Parameters.Add(parameter3);

                    var parameter4 = new SqlParameter { ParameterName = "@Output", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output };
                    command.Parameters.Add(parameter4);

                    command.ExecuteNonQuery();

                    Account.AccountId = parameter4.Value.ToInt32Default(0);
                }
            }

Notes:

AccountInsertRoutine = dbo.InsertAccount

ToInt32Default(0); custom routine to convert value to int32, if error or null then 0

Account: class containing AccountId, AccountName, AccountKey and FundId properties.

The SQL Server stored procedure:

CREATE PROCEDURE [dbo].[InsertAccount] (@AccountName  nvarchar(200), @AccountKey nvarchar(200), @AccountTypeId int, @fundId int, @Output int OUTPUT)
AS
    SET NOCOUNT ON;

    IF EXISTS(SELECT accountID FROM dbo.tblAccount WHERE sourceID = @AccountKey AND fundID = @fundId)
    BEGIN
        SELECT @Output = accountID FROM dbo.tblAccount WHERE sourceID = @AccountKey AND fundID = @fundId
    END
    ELSE
    BEGIN

        INSERT INTO dbo.tblAccount (accountName, fundID, sourceID, inceptionDate, taxIDNumber, typeID, erisaID, masterContactID, accountStatusID) 
            VALUES (@AccountName, @FundId, @AccountKey, NULL, NULL, @AccountTypeId, NULL, NULL, 1)

        SELECT @Output = SCOPE_IDENTITY();
    END
GO

What am I missing here.

user2520489
  • 1
  • 1
  • 1
    Something is not lining up. Are you sure this is the most recent stored procedure and/or you're getting it from the right environment? There is no way `@AccountId` would be expected or known without it even being mentioned in the Stored Proc or the C# Param list. – Tyler Roper Sep 14 '16 at 16:00
  • @AccountID or @AccountTypeID? Is it a typo? – Steve Sep 14 '16 at 16:02
  • People, I feel stupid right now. The right database was the one named Internal, the routines used were in the Interface database. – user2520489 Sep 14 '16 at 16:19

0 Answers0