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(ByVal procName As String, ByVal userno As String, ByRef dbCon As SqlClient.SqlConnection) As String
Dim status As Integer = 999
Dim command As SqlCommand = New SqlCommand

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

End With

Dim p1 As SqlParameter
p1 = command.Parameters.Add(New SqlClient.SqlParameter("@status", SqlDbType.NVarChar, 10, ParameterDirection.Output))
p1.Value = status.ToString

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

Dim txt As String = "hello"
Dim p3 As SqlParameter
p3 = command.Parameters.Add("@txt", SqlDbType.NVarChar, 8, ParameterDirection.Output)
p3.Value = txt.ToString


Dim x As String = Nothing
Try

command.ExecuteNonQuery()
If command.Parameters("@txt").Value = "Error" Then
x = "Error"
Else
x = (command.Parameters("@txt").Value).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_IDENTIFIER 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]

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("AvailItemQty", cn)

With sqlparam
.ParameterName = "@sntItemCode"
.SqlDbType = SqlDbType.SmallInt
.Value = intItemCode

.ParameterName = "@dblQty"
.SqlDbType = SqlDbType.Float
.Direction = ParameterDirection.Output

End With

With cmd
.Parameters.Add(sqlparam)
.CommandType = CommandType.StoredProcedure
StockQty = CType(cmd.Parameters("@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 = "@sntItemCode"
.SqlDbType = SqlDbType.SmallInt
.Value = intItemCode
End with

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

With cmd
.Parameters.Add(sqlparam1)
.Parameters.Add(sqlparam2)
.CommandType = CommandType.StoredProcedure
StockQty = CType(cmd.Parameters("@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.openTotQty+Res.RecTotQty - Res.IssTotQty)
from
( select 0 as OpenTotQty, RecDet.dblTotBaseQty as RecTotQty,
0 AS IssTotQty FROM tblReceiptMaster RecMas, tblReceiptDetails RecDet WHERE RecMas.intRecNo = RecDet.intRecNo and RecMas.blnDeleted = 0 and RecDet.sntItemCode = @sntItemCode

union
select 0 as OpenTotQty, InRetDet.dblTotBaseQty as RecTotQty, 0 AS IssTotQty FROM tblInhouseReturnMaster InRetMas, tblInhouseReturnDetails InRetDet WHERE InRetMas.intIRNo = InRetDet.intIRNo and InRetMas.blnDeleted = 0 AND InRetDet.sntItemCode = @sntItemCode

union

select 0 as OpenTotQty,0 as RecTotQty,RetDet.dblTotBaseQty as IssTotQty FROM tblReturnMaster RetMas, tblReturnDetails RetDet
WHERE RetMas.intRetNo = RetDet.intRetNo and RetMas.blnDeleted = 0 AND RetDet.sntItemCode = @sntItemCode

union

select 0 as OpenTotQty,0 AS RecTotQty,InIssDet.dblTotBaseQty as IssTotQty FROM tblInhouseIssueMaster InIssMas, blInhouseIssueDetails InIssDet WHERE InIssMas.intIINo = InIssDet.intIINo and InIssMas.blnDeleted = 0 AND InIssDet.sntItemCode = @sntItemCode
union

select 0 as OpenTotWt, 0 as RecTotWt, WastDet.dblTotBaseQty as IssTotWt FROM tblWastageMaster WastMas,tblWastageDetails WastDet
WHERE WastMas.intWNo = WastDet.intWNo and WastMas.blnDeleted = 0 AND WastDet.sntItemCode = @sntItemCode

union

select OpDet.dblTotBaseQty as OpenTotWt, 0 as RecTotWt,
0 as IssTotWt FROM tblOpeningMaster OpMas,tblOpeningDetails OpDet WHERE OpMas.intOpNo = OpDet.intOpNo and OpMas.blnDeleted = 0 and OpDet.sntItemCode = @sntItemCode

)Res


ganesh