SQL Stored Procedure Return value rowcount not showing correctly

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

imstac73Asked:
Who is Participating?
 
ZberteocCommented:
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
 
knightEknightCommented:
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
 
knightEknightCommented:
Note that I edited my post above because I had originally posted C# code instead of VB.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
imstac73Author Commented:
Do I get rid of this line too then?

  Dim rowcountParameter As New SqlParameter("@rowcount", SqlDbType.Int)
0
 
knightEknightCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
ArkCommented:
Not sure but if your server collation is _CS_ instead of _CI_ then different cases (ROWCOUNT vs rowcount) make sence.
0
 
imstac73Author Commented:
I tried adding -1 to my Catch block and it still returned 0 for the value.
0
 
imstac73Author Commented:
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
 
imstac73Author Commented:
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
 
ZberteocCommented:
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
 
imstac73Author Commented:
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
 
imstac73Author Commented:
BTW, the stored procedure works fine if I run it in SQL.
0
 
imstac73Author Commented:
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
 
imstac73Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.