468,104 Members | 1,324 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,104 developers. It's quick & easy.

Unable to get a Return value after executing a stored procedure

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

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

If command.Parameters("@txt").Value = "Error" Then
x = "Error"
x = (command.Parameters("@txt").Value).ToString
End If

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

Return x

End Function

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



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


delete games where gamedesc = 'test'

IF @@ERROR = 0
commit tran
set @status = 6
set @user = 12
set @txt = 'Record committed'
set @status = 13
set @user = 15
Set @txt = 'Error'
Nov 7 '08 #1
4 2680
7,872 Expert 4TB
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
Nov 7 '08 #2
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
.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.
Mar 11 '09 #3
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

.CommandType = CommandType.StoredProcedure
StockQty = CType(cmd.Parameters("@dblQty").Value, Double)
Mar 11 '09 #4
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)
( 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

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


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


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

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


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


Mar 12 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Rhino | last post: by
5 posts views Thread by Sandy | last post: by
1 post views Thread by Solo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.