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

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"??

ALTER PROCEDURE [dbo].[spAdminChangePassword]
(
@userName varchar(20),
@oldPass varchar(20),
@newPass varchar(20),

@ok int  OUTPUT

)

and drop declare @ok int;


Hope, that it will work.

-- tesu

(I am really astonished that over 15-year old transact SQL from Sybase company is still alive in modern Microsoft SQL Server today.)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.