Stored procedure return value 2

[vasu2005]

Hello all,

I have a vb.net code which executes the stored procedure talking one parameter if the parameter value already exists then return -1 else insert the record in the table and return 0

But after the application is run and if the new value comes then it insert into the table but returns 1 instead of 0. When I execute the procedure in SQL it retruns 0.

Can anyone tell me why am I getting the wrong return value.

VB.NET code:

Private Function GetDataSet() As DataSet

Dim prodstr As String
Dim returnval As Integer

Try
prodstr = Me.ListBox2.SelectedValue
Dim cmd As New SqlCommand("zcheckprod " & "'" & prodstr & "'", cn)

If cn.State = ConnectionState.Closed Then
cn.Open()
End If

returnval = cmd.ExecuteNonQuery()

Catch ex As Exception
MsgBox(ex.ToString)
End Try
MessageBox.Show(returnval)

End Function

--
The SQL stored procedure:

CREATE PROC zcheckadmin
@prod char(5)
as

Declare @i as int

Select @i = count(*) from table1 where
comproduct=@prod
If Isnull(@i,0) = 0
Begin
Insert Into table1 (comproduct,measure)
Select @prod, 0
return 0
End
ELSE
print 'exists'
return -1

Thanks,

-V

 

[gmmastros]

.executenonquery returns the number of records affected, not the return value of the stored procedure.

http://msdn.microsoft.com/library/d...clientsqlcommandclassexecutenonquerytopic.asp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vasu2005]

George, Thanks for you reply. You are right the executenonquery returns the records got affected. But then how would I get the return value by stored procedure in vb.net code.

Thanks,

-V

 

[gmmastros]

vasu2005,

To be honest, I don't know. I'm a vb6 programmer beginning to learn vb.net. It aint easy.

If I understand correctly, you want a -1 returned if a record is NOT inserted, and a 0 when a record is inserted.

That really isn't much different than 0 representing that a record is NOT inserted and a non-zero when a record is inserted.

Truth is, I'm hoping someone else replies to this thread because I'd like to know how to get the return value also.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jebenson]

Try this:

Dim cmd As New SqlCommand("zcheckprod", cn)

With cmd
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@prod", SqlDbType.Char, 5) 'matches @prod parameter in SP
.Parameters.Add("retValue", SqlDbType.Int)
.Parameters("retValue").Direction = ParameterDirection.ReturnValue
End With
Then:

If cn.State = ConnectionState.Closed Then
cn.Open()
End If

cmd.Parameters("@prod").Value = prodstr

cmd.ExecuteNonQuery()

returnval = cmd.Parameters("retValue").Value

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[vasu2005]

Jebenson,

Thanks a bunch. Your code works just great. Since I am very new to .NET your help is really appreciated.

Thanks again,

-V