Need help: Call server side Stored Procedure and get the return va

[Guest]

I have trouble with it.

I have created a sproc in SQL Server, called SearchClass, which returns a
searched key from a table. I have tested this procedure in Analyzer and it
works fine. However, in the client side, when I use ExecuteNonQuery as follows
---
Me.SqlConnection1.Open()
classKey = Me.SqlCommand2.ExecuteNonQuery()
Me.SqlConnection1.Close()
--
it always returns -1 rather than 5 that should be.
What is the problem?

Thank you for any help.

David

 

[bruce barker \(sqlwork.com\)]

ExecuteNonQuery does not return the return value of a sp, it returns rows
affected (addedd, deleted, changed). a query always returns -1

if you want a return values, you need to added a return value parameter:

SqlCommand cmd = new SqlCommand(spname,myConnection);
SqlParameter retparam = new SqlParameter("RV",SqlDbType.Int);
retparam.Direction= ParameterDirection.ReturnValue;
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
int returnValue = (int) retparam.Value;

-- bruce (sqlwork.com)

 

[Guest]

Thank you very much.
I will try it tomorrow in my office.

David

 

[Guest]

Hi, Bruce:

It does not work for me. I try either ExecuteNonQuery or ExecuteScalar, I
got 0 and -1 for ExecuteNonQuery, and 0 and 0 for ExecuteScalar. Now it
should be 11.

My Sproc code and VB.Net and output are in the following.

1. Stored procedure:
CREATE PROCEDURE SearchClassRecord2
(
@ModalityID int,
@PurposeID int,
@TechID int,
@SubtechID int,
@ContrastID int
)
AS
SET NOCOUNT ON

DECLARE @SearchedKey AS int

Select @SearchedKey = MAX(ClassKey)
FROM ClassDescription
WHERE ModalityID=@ModalityID and PurposeID=@PurposeID and TechID=TechID and
SubtechID=@SubtechID and ContrastAgentID=@ContrastID


RETURN @SearchedKey
GO

output:
Running dbo."SearchClassRecord2" ( @ModalityID = 1, @PurposeID = 1, @TechID
= 1, @SubtechID = 1, @ContrastID = 1 ).

No rows affected.
No more results.
(0 row(s) returned)
@RETURN_VALUE = 11
Finished running dbo."SearchClassRecord2".

2. VB.NET code:
Me.SqlCommand2.CommandText = "dbo.[SearchClassRecord2]"
Me.SqlCommand2.CommandType = System.Data.CommandType.StoredProcedure
Me.SqlCommand2.Connection = Me.SqlConnection1
Me.SqlCommand2.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
False, CType(0, Byte), CType(0, Byte), "",
System.Data.DataRowVersion.Current, Nothing))
Me.SqlCommand2.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ModalityID", System.Data.SqlDbType.Int,
4))
Me.SqlCommand2.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@PurposeID", System.Data.SqlDbType.Int,
4))
Me.SqlCommand2.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@TechID", System.Data.SqlDbType.Int, 4))
Me.SqlCommand2.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@SubtechID", System.Data.SqlDbType.Int,
4))
Me.SqlCommand2.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ContrastID", System.Data.SqlDbType.Int,
4))

Dim classKey As Integer
Dim psStatus As Integer

Me.SqlCommand2.Parameters("@ModalityID").Value =
ModalityDS1.Tables(0).Rows(ddlModality.SelectedIndex)("ModalityKey")
'CStr(ddlModality.SelectedValue)
Me.SqlCommand2.Parameters("@PurposeID").Value =
PurposeDS1.Tables(0).Rows(ddlPurpose.SelectedIndex)("ID")
'ddlPurpose.SelectedValue
Me.SqlCommand2.Parameters("@TechID").Value =
TechDS1.Tables(0).Rows(ddlTech.SelectedIndex)("ID") 'ddlTech.SelectedValue
Me.SqlCommand2.Parameters("@SubtechID").Value =
SubtechDS1.Tables(0).Rows(ddlSubtech.SelectedIndex)("ID")
'ddlSubtech.SelectedValue
Me.SqlCommand2.Parameters("@ContrastID").Value =
ContrastDS1.Tables(0).Rows(ddlContrast.SelectedIndex)("ID")
'ddlContrast.SelectedValue

Dim ClassKeyReturn As New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE", SqlDbType.Int)

ClassKeyReturn.Direction = ParameterDirection.ReturnValue


'test
lblInfo.Text &=
ModalityDS1.Tables(0).Rows(ddlModality.SelectedIndex)("ModalityKey") & ", " &
PurposeDS1.Tables(0).Rows(ddlPurpose.SelectedIndex)("ID") & ", " &
TechDS1.Tables(0).Rows(ddlTech.SelectedIndex)("ID") & ", " &
SubtechDS1.Tables(0).Rows(ddlSubtech.SelectedIndex)("ID") & ", " &
ContrastDS1.Tables(0).Rows(ddlContrast.SelectedIndex)("ID")

Me.SqlConnection1.Open()
'psStatus = Me.SqlCommand2.ExecuteNonQuery()
'classKey = CType(ClassKeyReturn.Value, Integer)
psStatus = CType(Me.SqlCommand2.ExecuteScalar(), Integer)
classKey = CType(ClassKeyReturn.Value, Integer)
Me.SqlConnection1.Close()


lblInfo.Text &= " classKey: " & classKey & " status: " & psStatus

output: 1, 1, 1, 1, 1 classKey: 0 status: 0


--------------------------