Question Stored procedure return wrong output

[manibest]

Below are my stored procedure to change password and the VB.net code to call it. The Sored Procedure executes properly and returns correct values when executed from SQL Server directly, but it does not check its conditions and returns -1 when called from VB.net code. please help me in this regard. Code is given below:

Stored Procedure

USE [test]
GO
/****** Object:  StoredProcedure [dbo].[spAdminChangePassword]    Script Date: 07/22/2010 10:05:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spAdminChangePassword]
	(
		@userName		varchar(20),
		@oldPass		varchar(20),
		@newPass		varchar(20)
	)
AS
BEGIN
set nocount on
	declare @ok int
	
	if exists (	SELECT [userName] 
				FROM [test].[dbo].[tblAdmin] 
				WHERE [userName] = @userName and
					  [password] = @oldPass)
		begin
			UPDATE	[test].[dbo].[tblAdmin]
			SET		[password]  = @newPass,
					[dateUpdate]= GETDATE()
			WHERE	[userName]	= @userName
			set @ok = 5
		end
	else
		begin
			set @ok = -4
		end
	return @ok
set nocount off
END

vb.net code to call the above procedure

Dim con As New SqlConnection(strConnString)

        Try
            con.Open()

            Dim ok As Integer = 2
            Dim comm As SqlCommand

            comm = New SqlCommand("spAdminChangePassword", con)
            comm.CommandType = CommandType.StoredProcedure

            comm.Parameters.AddWithValue("@userName", Session("AdminUser"))
            comm.Parameters.AddWithValue("@oldPass", oldPass.Text)
            comm.Parameters.AddWithValue("@newPass", newPass.Text)
            ok = comm.ExecuteNonQuery()

            If ok = 5 Then
                MessageHandling("2")
            ElseIf ok = -4 Then
                MessageHandling("1")
            Else
                lbl.Text = "dont know"
            End If

        Catch ex As Exception
            Console.WriteLine(ex.Message)
            lbl.Text = ex.ToString
            Throw
        Finally
            con.Close()
        End Try

thanks in advance

 

[manibest]

oo yes... it is checking the condition and changing the password as required. now the problem is
How to get the return value of @ok from stored procedure into vb.net variable "ok"??

 

[cjard]

comm.Parameters.AddWithValue("@ret", 0).Direction = ReturnValue

Then to check, after execute
ok = DirectCast(comm.Parameters("@ret").Value, Integer)

 

[cjard]

Incidentally, I really, really don't recommend doing this way.. Apart from the fact that youre using "magic numbers" (numbers like -4 or 5 in your code which are MEANINGLESS), youre ignoring a very good exception handling mechanism.. If there is an error updating, raise an error in the T-SQL so the vb program expereinces an exception, and then handle the exception..

TSQL: Error handling with Try Catch from a .NET perspective « Mehroz’s Experiments