CODEWITHSUNDEEP
sql-servervb.netstored-procedures
Kevin Fischer
Kevin Fischer

Reputation: 352

Getting the return value from stored procedure and using it in program

This is a follow up from this question where my stored procedure is this:

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Check_Previous_Passwords')
BEGIN
    PRINT 'Dropping Procedure Check_Previous_Passwords'
    DROP  Procedure  Check_Previous_Passwords
END
GO

PRINT 'Creating Procedure Check_Previous_Passwords'
GO

CREATE Procedure Check_Previous_Passwords
    @ua_pk uniqueidentifier,
    @IncomingPassword varchar(25)
AS
    DECLARE @Temp VARCHAR(25)
    DECLARE @IsSamePassword bit

    SET @Temp = (SELECT TOP 1 up_Password 
                 FROM User_Passwords 
                 WHERE ua_fk = @ua_pk 
                 ORDER BY up_PasswordDate DESC)


    IF (EXISTS (SELECT 1 
                FROM User_Passwords up 
                WHERE up.ua_fk = @ua_pk
                AND @IncomingPassword = up.up_Password))
        BEGIN
            SELECT @IsSamePassword = 1

        END
    ELSE
        BEGIN
            SELECT @IsSamePassword = 0

        END
GO

GRANT EXEC ON Check_Previous_Passwords TO WEB

GO

I'm really sure that the SQL associated is returning a value. But when I'm bringing it into my VB.Net solution and using it, it's stating that my "Expression does not produce a value". Well here's my VB code:

On button click:

If user_.Check_Previous_Passwords(user_) = True Then
    'throw error and set ResetPassword to true
End If

on the User.vb

Public Sub Check_Previous_Passwords(ByVal User As FoundationLibrary.User)
    Dim IsSamePassword_ As Integer
    Dim objCommand As New SqlCommand("Check_Previous_Passwords", DatabaseInterface_.Connection)
    objCommand.CommandType = CommandType.StoredProcedure
    objCommand.Parameters.AddWithValue("@ua_pk", ua_pk_)
    objCommand.Parameters.AddWithValue("@IncomingPassword", ua_Password_)
    DatabaseInterface_.Open()
    IsSamePassword_ = objCommand.ExecuteScalar
    DatabaseInterface_.Close()
    If IsSamePassword_ = 1 Then
        User.ua_ResetPassword_ = True
    Else
        User.ua_ResetPassword_ = False
    End If
End Sub

I have used Dim IsSamePassword As Boolean, then changing my If statement to equal True. Still throwing the same error.

I have this feeling is really easy.

Upvotes: 1

Views: 412

Answers (3)

T.S.
T.S.

Reputation: 19330

This is just to show you more capability and techniques. In fact, you can really use RETURN in your SP

. . . . . 
IF EXISTS (SELECT ... )
    RETURN  1
ELSE
    RETURN  0
. . . . .

With that in place you will need to modify VB call to include a return parameter in the end, using parameter direction 

p.Direction = ParameterDirection.ReturnValue

Upvotes: 1

Tamás Kecskeméti
Tamás Kecskeméti

Reputation: 242

Your stored procedure does not return any value. SELECT @IsSamePassword = 0 statement only let the value0 to @IsSamePassword variable. Write the SELECT @IsSamePassword statement to the end of your procedure or eliminate the @IsSamePassword variable.

CREATE Procedure Check_Previous_Passwords
@ua_pk uniqueidentifier,
@IncomingPassword varchar(25)
AS
DECLARE @Temp VARCHAR(25)
DECLARE @IsSamePassword bit

SET @Temp = (SELECT TOP 1 up_Password 
             FROM User_Passwords 
             WHERE ua_fk = @ua_pk 
             ORDER BY up_PasswordDate DESC)


IF (EXISTS (SELECT 1 
            FROM User_Passwords up 
            WHERE up.ua_fk = @ua_pk
            AND @IncomingPassword = up.up_Password))
    BEGIN
        SELECT  1

    END
ELSE
    BEGIN
        SELECT  0

    END

Or

CREATE Procedure Check_Previous_Passwords
@ua_pk uniqueidentifier,
@IncomingPassword varchar(25)  
AS
DECLARE @Temp VARCHAR(25)
DECLARE @IsSamePassword bit

SET @Temp = (SELECT TOP 1 up_Password 
             FROM User_Passwords 
             WHERE ua_fk = @ua_pk 
             ORDER BY up_PasswordDate DESC)


IF (EXISTS (SELECT 1 
            FROM User_Passwords up 
            WHERE up.ua_fk = @ua_pk
            AND @IncomingPassword = up.up_Password))
    BEGIN
        SELECT @IsSamePassword = 1

    END
ELSE
    BEGIN
        SELECT @IsSamePassword = 0

    END

 SELECT @IsSamePassword

Upvotes: 4

Andrew Morton
Andrew Morton

Reputation: 25013

The SQL is more complicated than it needs to be - you could use

CREATE Procedure Check_Previous_Passwords
    @ua_pk uniqueidentifier,
    @IncomingPassword varchar(25)
AS
    SELECT COUNT(*)
    FROM User_Passwords up 
    WHERE up.ua_fk = @ua_pk
        AND @IncomingPassword = up.up_Password

and the VB which uses it needs to be a function so that it can return a value:

Option Strict On
' ...'
Public Function Check_Previous_Passwords(ByVal User As FoundationLibrary.User) As Boolean
    Dim isSamePassword As Integer
    Dim objCommand As New SqlCommand("Check_Previous_Passwords", DatabaseInterface_.Connection)
    objCommand.CommandType = CommandType.StoredProcedure
    objCommand.Parameters.Add(New SqlParameter With {.ParameterName"@ua_pk", .SqlDbType = SqlDbType.UniqueIdentifier, .Value = ua_pk_})
    objCommand.Parameters.Add(New SqlParameter With {.ParameterName = "@IncomingPassword", .SqlDbType = SqlDbType.VarChar, .Size = 25, .Value = ua_Password_})
    DatabaseInterface_.Open()
    isSamePassword = CInt(objCommand.ExecuteScalar)
    DatabaseInterface_.Close()

    User.ua_ResetPassword_ = (isSamePassword = 1)

    Return User.ua_ResetPassword_

End Function

I changed the AddWithValue parts to a version which works reliably. You should avoid AddWithValue - it will only bring you misery eventually: Can we stop using AddWithValue() already?

Upvotes: 2

Related Questions