imstac73
asked on
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 Stored Procedure:
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()
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
Note that I edited my post above because I had originally posted C# code instead of VB.
ASKER
Do I get rid of this line too then?
Dim rowcountParameter As New SqlParameter("@rowcount", SqlDbType.Int)
Dim rowcountParameter As New SqlParameter("@rowcount", SqlDbType.Int)
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:
cmd.Parameters("@rowcount"
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)
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.
Not sure but if your server collation is _CS_ instead of _CI_ then different cases (ROWCOUNT vs rowcount) make sence.
ASKER
I tried adding -1 to my Catch block and it still returned 0 for the value.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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.
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.
ASKER
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)
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)
ASKER
BTW, the stored procedure works fine if I run it in SQL.
ASKER
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.
Not sure why it is showing the wrong amount.
ASKER
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.
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
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
Try this:Open in new window