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

[indian143]

Hi All, I have a stored procedure as below and I am calling that stored procedure by using ExecuteQuery in asp.net application, I am calling passing the out parameter value but still it is throwing exception. I am not understanding where am I missing, I am debugging and searching too, still can't find any answer. Can anybody please help me in this any suggestion link or code snippet would be helpful, thanks in advance. Here is Stored Procedure parameter structure I avoided putting all stored procedure logic here to increase readability, but if you want I can put it here

ALTER PROCEDURE [dbo].[StaffSearch_sel]
(
@OverrideSecurity BIT = 0,
@UserId INT,
@SchoolYearId INT,
@Name VARCHAR(500) = NULL,
@ContractorId INT = NULL,
@SubcontractorId INT = NULL,
@SiteId INT = NULL,
@OrganizationRelationshipIds VARCHAR(500) = NULL,
@RoleTypeIds VARCHAR(200) = NULL,
@StaffMemberNeedsAllRoles BIT = 0,
@Page INT = 1,
@RowsPerPage INT = 20,
@TotalRows INT OUT,
@SortExpression VARCHAR(50) = 'LastName',
@SortDirection VARCHAR(15) = 'Ascending'
)
AS
BEGIN
---logic-----
END

And my C# code here

    public static List Search(bool staffMemberNeedsAllRoles, bool overrideSecurity, string name, int schoolYearId, int? contractorId, int? subcontractor, int? siteId,
        int\[\] roleTypeIds, int\[\] organizationRelationshipIds, int userId, int pageNumber, int rowsPerPage, out int totalRows, string sortExpression, string sortDirection)
    {
        using (var context = new ELMSContext())
        {
            var outputParm = new SqlParameter("TotalRows", SqlDbType.Int) { Direction = ParameterDirection.Output };

            List pocos =
                context.ExecuteStoreQuery("exec StaffSearch\_sel @OverrideSecurity, @UserId, @SchoolYearId, @Name, @ContractorId, @SubcontractorId, @SiteId, @OrganizationRelationshipIds, @RoleTypeIds, @StaffMemberNeedsAllRoles",
                        new SqlParameter("@OverrideSecurity", overrideSecurity),
                        new SqlParameter("@UserId", userId),
                        new SqlParameter("@SchoolYearId", schoolYearId),
                        new SqlParameter("@Name", name ?? (object)DBNull.Value),
                        new SqlParameter("@ContractorId", contractorId ?? (object)DBNull.Value),
                        new SqlParameter("@SubcontractorId", subcontractor ?? (object)DBNull.Value),

[Sascha Lefevre]

Looks like you simply forgot the @

var outputParm = new SqlParameter("@TotalRows", SqlDbType.Int) { Direction = ParameterDirection.Output };
^^^

[indian143]

Even after putting @ also it was giving me error then I tried with this. I have put it like this

            List pocos =
                context.ExecuteStoreQuery("exec StaffSearch\_sel @OverrideSecurity, @UserId, @SchoolYearId, @Name, @ContractorId, @SubcontractorId, @SiteId, @OrganizationRelationshipIds, @RoleTypeIds, @StaffMemberNeedsAllRoles",
                        new SqlParameter("@OverrideSecurity", overrideSecurity),
                        new SqlParameter("@UserId", userId),
                        new SqlParameter("@SchoolYearId", schoolYearId),
                        new SqlParameter("@Name", name ?? (object)DBNull.Value),
                        new SqlParameter("@ContractorId", contractorId ?? (object)DBNull.Value),
                        new SqlParameter("@SubcontractorId", subcontractor ?? (object)DBNull.Value),
                        new SqlParameter("@SiteId", siteId ?? (object)DBNull.Value),
                        new SqlParameter("@OrganizationRelationshipIds", organizationRelationshipIds == null || organizationRelationshipIds.Count() == 0 ? (object)DBNull.Value : string.Join(",", organizationRelationshipIds)),
                        new SqlParameter("@RoleTypeIds", roleTypeIds == null ? (object)DBNull.Value : string.Join(",", roleTypeIds)),
                        new SqlParameter("@StaffMemberNeedsAllRoles", staffMemberNeedsAllRoles),
                        new SqlParameter("@Page", pageNumber),
                        new SqlParameter("@RowsPerPage", rowsPerPage),
                        //outputParm,
                        new SqlParameter("@TotalRows", SqlDbType.Int) { Direction = ParameterDirection.Output },
                        new SqlParameter("@SortExpression", sortExpression ?? (object)DBNull.Value),
                        new SqlParameter("@SortDirection", sortDirection ?? (object)DBNull.Value)
                        ).ToList();

            totalRows = ((outputParm != null) && !DBNull.Value.Equals(outputParm)) ? int.Parse(outputParm.Value.ToString()) : 0;

And still giving the following error

Procedure or function 'StaffSearch_sel' expects parameter '@TotalRows', which was not supplied.

"There is enough hatred in the world, lets spread love compassion and affection."

[Sascha Lefevre]

Please try it with this query-string:

"exec StaffSearch_sel @OverrideSecurity,
@UserId, @SchoolYearId, @Name, @ContractorId, @SubcontractorId,
@SiteId, @OrganizationRelationshipIds, @RoleTypeIds,
@StaffMemberNeedsAllRoles, @TotalRows out"
^^^^^^^^^^