Solved

SQL Stored Procedure Return value rowcount not showing correctly

Posted on 2014-04-03
16
3,907 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
ID: 39976770
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
ID: 39976788
Note that I edited my post above because I had originally posted C# code instead of VB.
0
 

Author Comment

by:imstac73
ID: 39976803
Do I get rid of this line too then?

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

Expert Comment

by:knightEknight
ID: 39976824
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:Scott Pletcher
ID: 39976899
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
ID: 39977387
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
ID: 39978040
I tried adding -1 to my Catch block and it still returned 0 for the value.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:imstac73
ID: 39978075
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
ID: 39978103
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
ID: 39978155
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
ID: 39978234
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
ID: 39978278
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
ID: 39978282
BTW, the stored procedure works fine if I run it in SQL.
0
 

Author Comment

by:imstac73
ID: 39978291
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
ID: 39978503
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

863 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

18 Experts available now in Live!

Get 1:1 Help Now