1
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
