Unable to get a Return value after executing a stored procedure

[jleeie]

Can someone help me, I'm going round in circles with this and my head is cabbaged !

I am using visual studio 2005 & VB & MS SQL 2005

I am trying to execute a stored procedure from within a program. I want to return values. I can get a 0 or 1 returned. I don't seem to be able to get any other value returned.

below is my VB code and the stored procedure. I would really appreciate if someone would have a look that knows how to do this. Thanks if you help.

Public Function ExSPretInt(ByVa l procName As String, ByVal userno As String, ByRef dbCon As SqlClient.SqlCo nnection) As String
Dim status As Integer = 999
Dim command As SqlCommand = New SqlCommand

dbcon.Open()
With command
.Connection = dbCon
.CommandText = procName
.CommandType = CommandType.Sto redProcedure

End With

Dim p1 As SqlParameter
p1 = command.Paramet ers.Add(New SqlClient.SqlPa rameter("@statu s", SqlDbType.NVarC har, 10, ParameterDirect ion.Output))
p1.Value = status.ToString

Dim p2 As SqlParameter
p2 = command.Paramet ers.Add("@user" , SqlDbType.Int, 6)
p2.Value = CInt(userno)

Dim txt As String = "hello"
Dim p3 As SqlParameter
p3 = command.Paramet ers.Add("@txt", SqlDbType.NVarC har, 8, ParameterDirect ion.Output)
p3.Value = txt.ToString


Dim x As String = Nothing
Try

command.Execute NonQuery()
If command.Paramet ers("@txt").Val ue = "Error" Then
x = "Error"
Else
x = (command.Parame ters("@txt").Va lue).ToString
End If

Catch ex As SqlException
Dim exerror As String = "exception error"
End Try

Return x

End Function

*************** *************** *************** *************** *************** ***************

STORED PROCEDURE



set ANSI_NULLS ON
set QUOTED_IDENTIFI ER ON
go

ALTER PROCEDURE [dbo].[deleteUser] ( @status int output, @user int, @txt nvarchar(8) )
AS

BEGIN TRAN

delete games where gamedesc = 'test'

IF @@ERROR = 0
begin
commit tran
set @status = 6
set @user = 12
set @txt = 'Record committed'
return
end
else
begin
ROLLBACK TRAN
set @status = 13
set @user = 15
Set @txt = 'Error'
return
end

[Plater]

Hmm well you do some curious behavior here.
According to your stored procedure, the only value that should "return" anything is the @status value.
For some reason you apply an initial value to that parameter, not sure why.

Then after you execute your stored procedure, you don't even look at your only output parameter ( "@status" ).

I think once you sort yourself out and get correct program flow happening, you will get your values back

[ganeshvkl]

How to get Output value from stored Procedure thru VB.net 2003

hi all,
i'm using vb.net 2003 with sql server 2000 , i write one stored procedure with one input parameter and one output parameter when i try to execute in Query analyser it's working fine, where as thru VB.net code i got wrong results, my code is below.

dim intItemcode as integer = 308

cmd = New SqlCommand("Ava ilItemQty", cn)

With sqlparam
.ParameterName = "@sntItemCo de"
.SqlDbType = SqlDbType.Small Int
.Value = intItemCode

.ParameterName = "@dblQty"
.SqlDbType = SqlDbType.Float
.Direction = ParameterDirect ion.Output

End With

With cmd
.Parameters.Add (sqlparam)
.CommandType = CommandType.Sto redProcedure
StockQty = CType(cmd.Param eters("@dblQty" ).Value, Double)
End With
i got result 308 instead of 1200. i don't know where is the problem?

Thanks in Adv.
Ganesh

[prasad599]

I think this should work

With sqlparam1
.ParameterName = "@sntItemCo de"
.SqlDbType = SqlDbType.Small Int
.Value = intItemCode
End with

With sqlParam2
.ParameterName = "@dblQty"
.SqlDbType = SqlDbType.Float
.Direction = ParameterDirect ion.Output
End With

With cmd
.Parameters.Add (sqlparam1)
.Parameters.Add (sqlparam2)
.CommandType = CommandType.Sto redProcedure
StockQty = CType(cmd.Param eters("@dblQty" ).Value, Double)

[ganeshvkl]

hi ,
Thanx for u'r reply.
It's not working.. i got result as 0 instead of 1200.

Stored procedure code is below

create proc AvailItemQty @sntItemCode smallint , @dblQty float output AS

select @dblQty = sum(Res.openTot Qty+Res.RecTotQ ty - Res.IssTotQty)
from
( select 0 as OpenTotQty, RecDet.dblTotBa seQty as RecTotQty,
0 AS IssTotQty FROM tblReceiptMaste r RecMas, tblReceiptDetai ls RecDet WHERE RecMas.intRecNo = RecDet.intRecNo and RecMas.blnDelet ed = 0 and RecDet.sntItemC ode = @sntItemCode

union
select 0 as OpenTotQty, InRetDet.dblTot BaseQty as RecTotQty, 0 AS IssTotQty FROM tblInhouseRetur nMaster InRetMas, tblInhouseRetur nDetails InRetDet WHERE InRetMas.intIRN o = InRetDet.intIRN o and InRetMas.blnDel eted = 0 AND InRetDet.sntIte mCode = @sntItemCode

union

select 0 as OpenTotQty,0 as RecTotQty,RetDe t.dblTotBaseQty as IssTotQty FROM tblReturnMaster RetMas, tblReturnDetail s RetDet
WHERE RetMas.intRetNo = RetDet.intRetNo and RetMas.blnDelet ed = 0 AND RetDet.sntItemC ode = @sntItemCode

union

select 0 as OpenTotQty,0 AS RecTotQty,InIss Det.dblTotBaseQ ty as IssTotQty FROM tblInhouseIssue Master InIssMas, blInhouseIssueD etails InIssDet WHERE InIssMas.intIIN o = InIssDet.intIIN o and InIssMas.blnDel eted = 0 AND InIssDet.sntIte mCode = @sntItemCode
union

select 0 as OpenTotWt, 0 as RecTotWt, WastDet.dblTotB aseQty as IssTotWt FROM tblWastageMaste r WastMas,tblWast ageDetails WastDet
WHERE WastMas.intWNo = WastDet.intWNo and WastMas.blnDele ted = 0 AND WastDet.sntItem Code = @sntItemCode

union

select OpDet.dblTotBas eQty as OpenTotWt, 0 as RecTotWt,
0 as IssTotWt FROM tblOpeningMaste r OpMas,tblOpenin gDetails OpDet WHERE OpMas.intOpNo = OpDet.intOpNo and OpMas.blnDelete d = 0 and OpDet.sntItemCo de = @sntItemCode

)Res


ganesh