none
Execution of stored procedure thru code and directly results different

Answers

  • http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx

    ExecuteScaler returns "The first column of the first row in the result set, or a null reference (Nothingin Visual Basic) if the result set is empty. Returns a maximum of 2033 characters".

    You are using RETURN to return the output. I guess that should be the issue.

    Use Select Instead with the same C# code.

    ALTER PROCEDURE [dbo].[GetMAAValue] 
    @CommentId nvarchar(36), 
    @UserId nvarchar(36) 
    AS 
    DECLARE @MAAValue int 
    Declare @uCommentID uniqueidentifier  
    Declare @uUserID uniqueidentifier  
     
    select   @uCommentID = convert(uniqueidentifier,@CommentId) 
    select   @uUserID = convert(uniqueidentifier,@UserId) 
     
    SET @MAAValue = (SELECT [MAAValue]  
            FROM [WebSparkDB].[dbo].[CommentVotes] 
            WHERE [CommentId] =   @uCommentID  AND  
                      [UserId]  = @uUserID  
            ) 
     
    SELECT @MAAValue 

    Alternatively, with above sp in database, you can use dataset to catch the dataset with executing the command using ExecuteNonQuery. You can then use

    int commentTtlMaaVal =Convert.ToInt32(ds.Tables[0].Rows[0][0]);

    to get the appropriate value.

    This should solve your problem. Let me know the result.


    Planet Earth is at risk. Global warming is on a high tide.
    Be Responsible. Plant Trees. Keep your City Clean and Green.

    Mark Helpful Posts and Close your Threads. Keep the Forum Green.
     - Arun Kumar Allu

    • Edited by arun.passioniway Wednesday, February 15, 2012 7:53 PM minor typo edit
    • Marked as answer by Mahesha999 Wednesday, February 15, 2012 8:41 PM
    Wednesday, February 15, 2012 7:43 PM
  • You mixed 2 different ways of SQL / C# interactions. First is result sets, other is in/out parameters. If you return parameter, then you need to define this paramete in C# cmd parameters.

    see http://stackoverflow.com/questions/6210027/c-sharp-calling-sql-server-stored-procedure-with-return-value


    Serg

    • Marked as answer by Mahesha999 Wednesday, February 15, 2012 8:41 PM
    Wednesday, February 15, 2012 8:27 PM

All replies

  • http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx

    ExecuteScaler returns "The first column of the first row in the result set, or a null reference (Nothingin Visual Basic) if the result set is empty. Returns a maximum of 2033 characters".

    You are using RETURN to return the output. I guess that should be the issue.

    Use Select Instead with the same C# code.

    ALTER PROCEDURE [dbo].[GetMAAValue] 
    @CommentId nvarchar(36), 
    @UserId nvarchar(36) 
    AS 
    DECLARE @MAAValue int 
    Declare @uCommentID uniqueidentifier  
    Declare @uUserID uniqueidentifier  
     
    select   @uCommentID = convert(uniqueidentifier,@CommentId) 
    select   @uUserID = convert(uniqueidentifier,@UserId) 
     
    SET @MAAValue = (SELECT [MAAValue]  
            FROM [WebSparkDB].[dbo].[CommentVotes] 
            WHERE [CommentId] =   @uCommentID  AND  
                      [UserId]  = @uUserID  
            ) 
     
    SELECT @MAAValue 

    Alternatively, with above sp in database, you can use dataset to catch the dataset with executing the command using ExecuteNonQuery. You can then use

    int commentTtlMaaVal =Convert.ToInt32(ds.Tables[0].Rows[0][0]);

    to get the appropriate value.

    This should solve your problem. Let me know the result.


    Planet Earth is at risk. Global warming is on a high tide.
    Be Responsible. Plant Trees. Keep your City Clean and Green.

    Mark Helpful Posts and Close your Threads. Keep the Forum Green.
     - Arun Kumar Allu

    • Edited by arun.passioniway Wednesday, February 15, 2012 7:53 PM minor typo edit
    • Marked as answer by Mahesha999 Wednesday, February 15, 2012 8:41 PM
    Wednesday, February 15, 2012 7:43 PM
  • Changing the query to the one (with SELECT @MAAValue) you specified worked.

    But I did not get what was the problem with the earlier one. I am not SQL Server Pro and am less experienced in it. Hence nothing sense wrong to me with the earlier query. Can you please elaborate what was going wrong earlier. Why return @MAAValue did not make ExecuteScalar return it as a first row-first column. Please enlighten me.

    You are great by the way. So correct guess. Thanks a lot!

     
    Wednesday, February 15, 2012 8:06 PM
  • You mixed 2 different ways of SQL / C# interactions. First is result sets, other is in/out parameters. If you return parameter, then you need to define this paramete in C# cmd parameters.

    see http://stackoverflow.com/questions/6210027/c-sharp-calling-sql-server-stored-procedure-with-return-value


    Serg

    • Marked as answer by Mahesha999 Wednesday, February 15, 2012 8:41 PM
    Wednesday, February 15, 2012 8:27 PM
  • Gr8 that it solved your problem. Vote/Mark helpful posts and close the thread.

    1. A comment in this post "sqlSelectCommand1.ExecuteScalar()  does not return the return value from your stored procedure                                " @ http://bytes.com/topic/net/answers/835961-c-executescalar-not-returning-value-stored-procedure

    2. Conclusion
    In this article we examined three ways to pass back scalar data from a stored procedure, along with the necessary code to process the returned value. You can use a SELECT statement, output parameter, or return value (assuming you want to pass back an integer value). When returning a scalar value via a SELECT statement you can read the resulting value using the ExecuteScalar() method. For output parameters and return values you need to create a parameter object with the proper Direction property value. Then, after you call the stored procedure, you can access the retrieved value through the parameter's Value property.

    @ http://www.4guysfromrolla.com/articles/062905-1.aspx

    Mark the answers in the ASP.NET forums too. & specify your solutions there as well.

    Hope this answers your question.


    Planet Earth is at risk. Global warming is on a high tide.
    Be Responsible. Plant Trees. Keep your City Clean and Green.

    Mark Helpful Posts and Close your Threads. Keep the Forum Green.
     - Arun Kumar Allu

    Wednesday, February 15, 2012 8:29 PM