Problem with stored procedure

Thomas Holste — Fri, 08 Mar 2013 12:33:40 GMT

Hi there,

I have a BO with a function to call a stored procedure which works, but I get a wrong return value (-1 instead of 1).

This is the scenario.

The BO has a fill-method to laod data from a view which consists of two tables. It connects items for invoices with customer data. Within this I want to delete items from the item-table and refresh the view.

I have a stored procedure which accepts an input value and return an outputvalue, the number of deleted records, which is allways 1.

[codesnippet]

USE [GALDATA]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[delete_from_tfliste]

@UID int,

@Anzahl Int Out

AS

BEGIN

SET NOCOUNT ON;

DELETE dbo.TEMPFAKT

FROM dbo.TEMPFAKT

WHERE

uid = @UID

select @Anzahl = @@ROWCOUNT

return @Anzahl

END

[/codesnippet]

I call this sp from the BO as mentioned above:

[codesnippet]

Public Function deletefromview(ByVal nUID As Integer) As Integer

Dim locommand As New SqlCommand("delete_from_tfliste")

locommand.CommandType = Data.CommandType.StoredProcedure

locommand.Parameters.Add("@uid", Data.SqlDbType.Int).Direction = Data.ParameterDirection.Input

locommand.Parameters("@uid").Value = nUID

locommand.Parameters.Add("@Anzahl", Data.SqlDbType.Int).Direction = Data.ParameterDirection.Output

Return ExecuteNonQuery(locommand)

End Function

[/codesnippet]

And the code to call the function is:

[codesnippet]

If Me.TfListeBO1.Count = 0 Then

Exit Sub

End If

Dim nRet As Integer = Me.TfListeBO1.deletefromview(Me.TfListeBO1.UID)

TfListeBO1.fillall()

[/codesnippet]

As I said above, it works but I get a return value (nRet) of -1 instead of 1, which is returned if I call it in Management Studio.

What have I done wrong?

Best regards

Thomas

RE: Problem with stored procedure

Thomas Holste — Fri, 08 Mar 2013 12:33:40 GMT

Thanks a lot, now it works fine.

Best regards

Thomas

RE: Problem with stored procedure

StrataFrame Team — Thu, 07 Mar 2013 14:02:36 GMT

It's the "SET NOCOUNT ON" line that is causing you problems. That tells SQL Server to stop counting the number of changed rows. If you remove the line the value will depend upon the default setting on the server, I think.

Change it to "SET NOCOUNT OFF" and you should be good. It feels awkward because it's a double-negative, but turning it "OFF" tells it to "don't not count rows," or turn on row counting.

StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Problem with stored procedure

Problem with stored procedure

RE: Problem with stored procedure

RE: Problem with stored procedure