Solved

SQL Stored Procedure Return value rowcount not showing correctly

Posted on 2014-04-03
16
3,853 Views
Last Modified: 2014-04-04
Hi,
I am running a stored procedure in my windows application and trying to display the return value of the procedure (which happens to be the rowcount) in a Message Box.

When I run the procedure in SQL the return value is correct.  When I run my procedure in my Windows application it returns 0.

Not sure what I'm doing wrong in my code.

Windows code:
'SQL Connection
        Dim conn As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("DARTS.My.MySettings.ACS_TARSConnectionString").ConnectionString)
        Dim cmd As SqlCommand = conn.CreateCommand
        Dim rowsupdated As Integer
        Dim rowcountParameter As New SqlParameter("@rowcount", SqlDbType.Int)
        Dim loginuser As String
        loginuser = My.User.Name


        Try
            conn.Open()

            'Call stored procedure
            'Command text is set to stored procedure
            cmd.CommandText = "ClaimsAutoAssignTopPaidClaims"
            cmd.CommandType = CommandType.StoredProcedure

            'Add Parameters
            cmd.Parameters.Add(New SqlParameter("@toppaidamt", mtxtboxamt.Text))
            cmd.Parameters.Add(New SqlParameter("@executedby", loginuser))
            cmd.Parameters.Add("@rowcount", SqlDbType.Int)
            cmd.Parameters("@rowcount").Direction = ParameterDirection.Output

            cmd.ExecuteNonQuery()

            rowsupdated = CInt(rowcountParameter.Value)

            'Return total number of claims updated
            Dim returnvalue As String
            returnvalue = "Total Claims AutoAssigned:" & CStr(rowsupdated)
            MessageBox.Show(returnvalue, "Success", MessageBoxButtons.OK)
            'Close Connection
            conn.Close()

            ' Exception Catch
        Catch ex As SqlException
            MessageBox.Show(ex.Message)
        Catch ex As InvalidOperationException
            MessageBox.Show(ex.Message)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            If Not conn Is Nothing Then conn.Dispose()
        End Try

        Me.Close()

Open in new window



SQL Stored Procedure:
ALTER PROCEDURE [dbo].[ClaimsAutoAssignTopPaidClaims] 
	-- Add parameter for top paid amount
	@toppaidamt money,
	@executedby varchar(56),
	@rowcount int output
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON

Declare
			@RowsInsertedMsg VARCHAR(1000)
            ,@ErrorMsg VARCHAR(1000)
            ,@ProcessText VARCHAR(1000)

--====================================
--Update Claims
--====================================
	BEGIN TRY
            BEGIN TRANSACTION
	
	
	Update dbo.claims
		Set Tracking = 1, followupuser = 'kristina.webb', followupdate = getdate()
		Where (tracking = 0 or tracking is null) and followupuser is null and followupdate is null and completedate is null and completeuser is null and tmhppaid > @toppaidamt

		Select @ROWCOUNT = @@rowcount

		 --PRINT 'Total Claims AutoCompleted : ' + CONVERT(VARCHAR(10), @@ROWCOUNT)
                
          SET @RowsInsertedMsg = 'Total Claims AutoAssigned : ' + CONVERT(VARCHAR(10), @@ROWCOUNT)

	Insert into  dbo.exec_storedprocedure_log (sqlprocedure,executed_by, recordsupdatedcount)
	values('AutoAssignTopPaidClaims', @executedby, @rowcount)


            COMMIT
 
      END TRY
		BEGIN CATCH
            IF @@ERROR != 0 
            BEGIN
                  --PRINT 'Unexpected error occurred! Changes will be rolled back.'
                  SET @ErrorMsg = 'Unexpected error occurred! Changes will be rolled back.'
                  ROLLBACK TRANSACTION
                  --PRINT 'Process Failed'
                  SET @ProcessText = 'Process Failed'
            END
      END CATCH
 
      SELECT
            @RowsInsertedMsg AS RowsInsertedMsg
            ,@ErrorMsg AS ErrorMsg
            ,@ProcessText AS ProcessText
END

Open in new window

0
Comment
Question by:imstac73
  • 8
  • 3
  • 2
  • +2
16 Comments
 
LVL 33

Expert Comment

by:knightEknight
Comment Utility
Instead of this:

   
cmd.Parameters.Add("@rowcount", SqlDbType.Int)

Open in new window

Try this:
   
Dim parameter As OdbcParameter = cmd.Parameters.Add("@rowcount",SqlDbType.Int)
parameter.Direction = ParameterDirection.Output

Open in new window

0
 
LVL 33

Expert Comment

by:knightEknight
Comment Utility
Note that I edited my post above because I had originally posted C# code instead of VB.
0
 

Author Comment

by:imstac73
Comment Utility
Do I get rid of this line too then?

  Dim rowcountParameter As New SqlParameter("@rowcount", SqlDbType.Int)
0
 
LVL 33

Expert Comment

by:knightEknight
Comment Utility
Sorry, I didn't see that you already are doing this in your next line with:

   cmd.Parameters("@rowcount").Direction = ParameterDirection.Output

At quick glance I thought you were just adding another parameter here.  It's just a syntax I'm not used to seeing, since I use declared parameter variables for this.

I'm sure your declared rowcountParameter parameter is fine, but I'm wondering if you can add it directly to the command, something like this:

rowcountParameter.Direction = ParameterDirection.Output
cmd.Parameters.Add(rowcountParameter,SqlDbType.Int)

Open in new window

0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Did you get an error of some type in the proc?  In that case the output variable is not being set.  You may want to set it to -1 in the CATCH block so you can tell that a value was returned.
0
 
LVL 27

Expert Comment

by:Ark
Comment Utility
Not sure but if your server collation is _CS_ instead of _CI_ then different cases (ROWCOUNT vs rowcount) make sence.
0
 

Author Comment

by:imstac73
Comment Utility
I tried adding -1 to my Catch block and it still returned 0 for the value.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:imstac73
Comment Utility
I read an article that stated that you cannot have Set NOCOUNT ON if you want to display the rowcount in your application.  So I removed the SET NOCOUNT ON from my SQL stored procedure.

In my stored procedure I am inserting the rowcount along with some other information into a log table.  What's weird is that when I run the stored procedure from my Windows application the row count inserts correctly in my log table but doesn't show correctly in my message box.  

This is driving me crazy...lol.
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
Comment Utility
why don't you use the return in the stored procedure right after the last select. You don't need the parameter then.

So the proc will look like this:
ALTER PROCEDURE [dbo].[ClaimsAutoAssignTopPaidClaims] 
	-- Add parameter for top paid amount
	@toppaidamt money,
	@executedby varchar(56)
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON

Declare
			@RowsInsertedMsg VARCHAR(1000)
            ,@ErrorMsg VARCHAR(1000)
            ,@ProcessText VARCHAR(1000)

			-- moved the prameter here
			,@rowcount int

--====================================
--Update Claims
--====================================
	BEGIN TRY
            BEGIN TRANSACTION
	
	
	Update dbo.claims
		Set Tracking = 1, followupuser = 'kristina.webb', followupdate = getdate()
		Where (tracking = 0 or tracking is null) and followupuser is null and followupdate is null and completedate is null and completeuser is null and tmhppaid > @toppaidamt

		Select @ROWCOUNT = @@rowcount

		 --PRINT 'Total Claims AutoCompleted : ' + CONVERT(VARCHAR(10), @@ROWCOUNT)
                
          SET @RowsInsertedMsg = 'Total Claims AutoAssigned : ' + CONVERT(VARCHAR(10), @@ROWCOUNT)

	Insert into  dbo.exec_storedprocedure_log (sqlprocedure,executed_by, recordsupdatedcount)
	values('AutoAssignTopPaidClaims', @executedby, @rowcount)


            COMMIT
 
      END TRY
		BEGIN CATCH
            IF @@ERROR != 0 
            BEGIN
                  --PRINT 'Unexpected error occurred! Changes will be rolled back.'
                  SET @ErrorMsg = 'Unexpected error occurred! Changes will be rolled back.'
                  ROLLBACK TRANSACTION
                  --PRINT 'Process Failed'
                  SET @ProcessText = 'Process Failed'
            END
      END CATCH
 
      SELECT
            @RowsInsertedMsg AS RowsInsertedMsg
            ,@ErrorMsg AS ErrorMsg
            ,@ProcessText AS ProcessText
		
	RETURN @rowcount
END

Open in new window

In your application code remove everything in regards with @rowcount parameter and when you execute the stored procedure instead of
 ...
           cmd.ExecuteNonQuery()

            rowsupdated = CInt(rowcountParameter.Value)
...

Open in new window

just use something like:
...
rowsupdated=cmd.ExecuteNonQuery()
...

Open in new window

I am not sure about this syntax but the idea is that the RETURN statement allows a storeprocedure to actually return a value from its execution line.
0
 

Author Comment

by:imstac73
Comment Utility
I don't want the rowcount from the insert statement.  The insert is just inserting information in a log table.  I need the rowcount for the update statement.  Will this return the correct value if I move the return statement right after the update statement?
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
As you can see in the RETURN is the @rowcount and not @@ROWCOUNT.

The variable with one @ is local to the sp and is a user variable you declare. The one with 2 @ is the internal SQL global variable that returns the number of rows affected by the last statement, whatever that was.

Just after update you stored the @@ROWCOUNT value to @rowcount variable (actually the upper/lower cases don't matter), which then is returned by the RETURN statement.

By the way you can name that @return variable as you want, i.e. @update_rowcount.
0
 

Author Comment

by:imstac73
Comment Utility
Okay, I made the changes and I am getting a number in  my messagebox now; however
the record count was incorrect.  I did a select query to determine how many records should have been updated by stored procedure and it should have only been 330.  I got a result of 991.

Also,
The update statement ran correctly; however my insert statement did not run (this is statement that inserts data into a log table)
0
 

Author Comment

by:imstac73
Comment Utility
BTW, the stored procedure works fine if I run it in SQL.
0
 

Author Comment

by:imstac73
Comment Utility
Okay, weird I ran it again and this time it did post to my log table; however the rowcount is still incorrect.  It showed 1067 and the actual count was 362.

Not sure why it is showing the wrong amount.
0
 

Author Comment

by:imstac73
Comment Utility
Yay, I got it working.  I just used a different method to run the stored procedure in my code.  Ultimately, I think changing from using an output parameter to a return value resolved most of the issue.

I've posted my final code just in case someone else has this same issue.

ALTER PROCEDURE [dbo].[ClaimsAutoAssignTopPaidClaims] 
	-- Add parameter for top paid amount
	@toppaidamt money,
	@executedby varchar(56)
	
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON

Declare
		--	@RowsInsertedMsg VARCHAR(1000)
           -- ,@ErrorMsg VARCHAR(1000)
          --  ,@ProcessText VARCHAR(1000)
			@rowcount int
			
--====================================
--Update Claims
--====================================
	BEGIN TRY
            BEGIN TRANSACTION
	
	
	Update dbo.claims
		Set Tracking = 1, followupuser = 'kristina.webb', followupdate = getdate()
		Where (tracking = 0 or tracking is null) and followupuser is null and followupdate is null and completedate is null and completeuser is null and tmhppaid > @toppaidamt

		Select @rowcount = @@ROWCOUNT
		

		 --PRINT 'Total Claims AutoCompleted : ' + CONVERT(VARCHAR(10), @@ROWCOUNT)
                
         -- SET @RowsInsertedMsg = 'Total Claims AutoAssigned : ' + CONVERT(VARCHAR(10), @@ROWCOUNT)

	Insert into  dbo.exec_storedprocedure_log (sqlprocedure,executed_by, recordsupdatedcount,claimamtentered)
	values('AutoAssignTopPaidClaims', @executedby, @rowcount,@toppaidamt)


            COMMIT
 
      END TRY
		BEGIN CATCH
            IF @@ERROR != 0 
            BEGIN
                  --PRINT 'Unexpected error occurred! Changes will be rolled back.'
             --     SET @ErrorMsg = 'Unexpected error occurred! Changes will be rolled back.'
                  ROLLBACK TRANSACTION
                  --PRINT 'Process Failed'
              --    SET @ProcessText = 'Process Failed'
            END
      END CATCH
 
      --SELECT
          --  @RowsInsertedMsg AS RowsInsertedMsg
           -- ,@ErrorMsg AS ErrorMsg
          --  ,@ProcessText AS ProcessText
Return @rowcount
END

Open in new window


 Private Sub btnRun_Click(sender As System.Object, e As System.EventArgs) Handles btnRun.Click
        'Declare variables
        Dim SQLCon As New SqlClient.SqlConnection
        Dim sqlcmd As New SqlClient.SqlCommand
        Dim loginuser As String = My.User.Name
        Dim rowsupdated As Integer = 0
        Dim rowsupdatedmsg As String

        'Set SQL Connection
        SQLCon.ConnectionString = My.Settings.ACS_TARSConnectionString

        Try

            'Open SQL Connection
            SQLCon.Open()

            'Assign values to variables
            sqlcmd.CommandText = "ClaimsAutoAssignTopPaidClaims"
            sqlcmd.CommandType = CommandType.StoredProcedure
            sqlcmd.Connection = SQLCon
            sqlcmd.Parameters.AddWithValue("@toppaidamt", mtxtboxamt.Text)
            sqlcmd.Parameters.AddWithValue("@executedby", loginuser)
            sqlcmd.Parameters.AddWithValue("ReturnValue", 0)
            sqlcmd.Parameters("ReturnValue").Direction = ParameterDirection.ReturnValue

            'Run Procedure
            sqlcmd.ExecuteNonQuery()

            'Return total number of claims updated
            rowsupdated = CInt(sqlcmd.Parameters("ReturnValue").Value)

            rowsupdatedmsg = "Total Claims AutoAssigned: " & CStr(rowsupdated)

            MessageBox.Show(rowsupdatedmsg, "Success", MessageBoxButtons.OK)

            

            ' Exception Catch
        Catch ex As SqlException
            MessageBox.Show(ex.Message)
        Catch ex As InvalidOperationException
            MessageBox.Show(ex.Message)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            'Close connections
           
            If Not SQLCon Is Nothing Then
                sqlcmd.Parameters.Clear()
                sqlcmd.Dispose()
                SQLCon.Dispose()
            End If

        End Try

        Me.Close()

    End Sub

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now