Procedure or function expects parameter which was not supplied.

Question

_{Thursday, July 16, 2020 12:05 PM}

Hi developpers;

I am using a stored procedure I wrote for a SQL localDB database. It works fine when executed from sever explorer.

When using it from a C# function, I get the foolowing Error:

System.Data.SqlClient.SqlException: 'Procedure or function 'PR_ADDBENEFICIARYTYPE' expects parameter '@BenefTypeName', which was not supplied.'

Databse table definition

CREATE TABLE [dbo].[BeneficiaryType] (
    [Id]            INT           IDENTITY (1, 1) NOT NULL,
    [BenefTypeName] NVARCHAR (50) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Stored procedure

CREATE PROCEDURE PR_ADDBENEFICIARYTYPE
    @BenefTypeName varchar(50)
AS
    INSERT INTO [dbo].[BeneficiaryType] 
        ([BenefTypeName]        ) 
    VALUES 
        (@BenefTypeName     )

C# code

public void Excute(string store, SqlParameter[] pr)
        {
            command.Parameters.Clear();
            command.Connection = con;
            command.CommandText = store;
            command.CommandType = CommandType.StoredProcedure;
            
            if (pr != null)
            {
                command.Parameters.AddRange(pr); 
            }
            command.ExecuteNonQuery();
        }

 public void AddBeneficiaryType(string BenefTypeName)
        {
            DAL.Open();
            DataTable dt = new DataTable();
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@BenefTypeName, SqlDbType.NVarChar);
            pr[0].Value = BenefTypeName;
            DAL.Excute("PR_ADDBENEFICIARYTYPE", pr);
            DAL.Close();
        }

I am stuck. I tried many solution but I did not sole the problem.

All replies (3)

_{Thursday, July 16, 2020 12:58 PM ✅Answered}

I see few more things - first of all, why do you create a new datatable if you're not using it? Secondly, the name of the method should probably be Execute or I would be more specific and say ExecuteStoredProcedure. Also, stored procedure name parameter should also probably be spelled out, e.g. storedProcedureName.

Also, when you're dealing with VarChar /Char, NVarChar or VarBinary columns or other columns with variable length I prefer to give size parameter in the declaration of SqlParameter.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

_{Thursday, July 16, 2020 12:41 PM}

I see only two minor glitches:

The data table is not required and the parameter name should be enclosed in quotation marks. And use the schema:

public void AddBeneficiaryType(string BenefTypeName)
{
    DAL.Open();            
    SqlParameter[] pr = new SqlParameter[1];
    pr[0] = new SqlParameter("@BenefTypeName", SqlDbType.NVarChar);
    pr[0].Value = BenefTypeName;
    DAL.Excute("dbo.PR_ADDBENEFICIARYTYPE", pr);
    DAL.Close();
}

The data type is NVARCHAR in the table as well as in the C# parameter declaration, but not in your stored procedure:

CREATE PROCEDURE dbo.PR_ADDBENEFICIARYTYPE
    @BenefTypeName NVARCHAR(50)
AS
    INSERT INTO dbo.BeneficiaryType ( [BenefTypeName] )
    VALUES ( @BenefTypeName );

_{Saturday, July 18, 2020 9:54 AM}

Thanks for your support..