Click here to Skip to main content
15,569,768 members
Home / Discussions / Database
   

Database

 
AnswerRe: VB.NET MYSQL loop through records while adding Pin
Pauls Pauls13-Apr-15 11:48
Pauls Pauls13-Apr-15 11:48 
AnswerRe: VB.NET MYSQL loop through records while adding Pin
Mycroft Holmes13-Apr-15 15:21
professionalMycroft Holmes13-Apr-15 15:21 
GeneralRe: VB.NET MYSQL loop through records while adding Pin
Pauls Pauls13-Apr-15 21:09
Pauls Pauls13-Apr-15 21:09 
GeneralRe: VB.NET MYSQL loop through records while adding Pin
Mycroft Holmes13-Apr-15 21:39
professionalMycroft Holmes13-Apr-15 21:39 
GeneralRe: VB.NET MYSQL loop through records while adding Pin
Pauls Pauls14-Apr-15 2:47
Pauls Pauls14-Apr-15 2:47 
QuestionPICTUREBOX IMAGE IN VB.NET Pin
Pauls Pauls13-Apr-15 1:18
Pauls Pauls13-Apr-15 1:18 
AnswerRe: PICTUREBOX IMAGE IN VB.NET Pin
Corporal Agarn13-Apr-15 1:35
professionalCorporal Agarn13-Apr-15 1:35 
QuestionSystem.Data.SqlClient.SqlException: Procedure or function 'StaffSearch_sel' expects parameter '@TotalRows', which was not supplied. Pin
indian1437-Apr-15 16:22
indian1437-Apr-15 16:22 
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<StaffMemberPOCO> 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<StaffMemberPOCO> pocos =
            context.ExecuteStoreQuery<StaffMemberPOCO>("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;
        return pocos;
    }
}
And executestorequery is throwing the following exception
System.Data.SqlClient.SqlException: 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."

AnswerRe: System.Data.SqlClient.SqlException: Procedure or function 'StaffSearch_sel' expects parameter '@TotalRows', which was not supplied. Pin
Sascha Lefèvre7-Apr-15 17:42
professionalSascha Lefèvre7-Apr-15 17:42 
GeneralRe: System.Data.SqlClient.SqlException: Procedure or function 'StaffSearch_sel' expects parameter '@TotalRows', which was not supplied. Pin
indian1437-Apr-15 20:59
indian1437-Apr-15 20:59 
GeneralRe: System.Data.SqlClient.SqlException: Procedure or function 'StaffSearch_sel' expects parameter '@TotalRows', which was not supplied. Pin
Sascha Lefèvre7-Apr-15 21:54
professionalSascha Lefèvre7-Apr-15 21:54 
QuestionA data source instance has not been supplied for the data source Pin
indian1436-Apr-15 13:55
indian1436-Apr-15 13:55 
QuestionHow Do I Insert Only Recently Updated Row From Table1 To 2 in MySQL Pin
RevathySanthanam2-Apr-15 5:10
RevathySanthanam2-Apr-15 5:10 
Rant[REPOST] How Do I Insert Only Recently Updated Row From Table1 To 2 in MySQL Pin
Richard Deeming2-Apr-15 5:41
mveRichard Deeming2-Apr-15 5:41 
Questionmultiple joins, works in sql manager but not in asp.net Pin
jkirkerx1-Apr-15 13:47
professionaljkirkerx1-Apr-15 13:47 
Answer[solved] Pin
jkirkerx1-Apr-15 14:02
professionaljkirkerx1-Apr-15 14:02 
SuggestionRe: multiple joins, works in sql manager but not in asp.net Pin
Richard Deeming2-Apr-15 3:30
mveRichard Deeming2-Apr-15 3:30 
GeneralRe: multiple joins, works in sql manager but not in asp.net Pin
jkirkerx2-Apr-15 13:09
professionaljkirkerx2-Apr-15 13:09 
Questionimage in database Pin
vinay dahiya1-Apr-15 12:40
vinay dahiya1-Apr-15 12:40 
GeneralRe: image in database Pin
PIEBALDconsult1-Apr-15 13:08
professionalPIEBALDconsult1-Apr-15 13:08 
GeneralRe: image in database Pin
vinay dahiya1-Apr-15 13:15
vinay dahiya1-Apr-15 13:15 
GeneralRe: image in database Pin
PIEBALDconsult1-Apr-15 13:22
professionalPIEBALDconsult1-Apr-15 13:22 
GeneralRe: image in database Pin
vinay dahiya1-Apr-15 13:59
vinay dahiya1-Apr-15 13:59 
GeneralRe: image in database Pin
Richard MacCutchan1-Apr-15 22:22
mveRichard MacCutchan1-Apr-15 22:22 
GeneralRe: image in database Pin
vinay dahiya2-Apr-15 2:59
vinay dahiya2-Apr-15 2:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.