Return Value Woes

asherman86 · #1 (**permalink**) 12-06-11, 17:10

I have this very simple looking thing going on:

This VB code does its thing:
(I'm sure it looks ugly, but bear with me.)

Code:

With cmd
            .Connection = conn
            .CommandType = Data.CommandType.StoredProcedure
            .CommandText = "proc_checkAnswer"
        End With

        param = cmd.Parameters.Add(New SqlParameter("@UserID", Data.SqlDbType.Int))
        param.Direction = Data.ParameterDirection.Input
        param.Value = UserID

        param = cmd.Parameters.Add(New SqlParameter("@SecAnswer", Data.SqlDbType.NVarChar, 30))
        param.Direction = Data.ParameterDirection.Input
        param.Value = Answer
        'also, don't worry, the connection is already open here.
        'so that isn't a problem.
        Dim result As Integer = cmd.ExecuteNonQuery()

... which talks to this bad boy:

Code:

CREATE PROCEDURE proc_checkAnswer
	@UserID Int,
	@SecAnswer Nvarchar(30)

AS
	IF (SELECT SecAnswer FROM tblUsers WHERE UserID = @UserID) = @SecAnswer 
		RETURN 1
	ELSE
		RETURN 2

And yet. I pass it everything it needs to survive, and it always returns -1. There must be something awry here.

Wim · #2 (**permalink**) 12-06-11, 20:02

Have you tried just returning a 1?

Code:

CREATE PROCEDURE proc_checkAnswer
	@UserID Int,
	@SecAnswer Nvarchar(30)
AS
	RETURN 1
GO

When that works, check the intermediate values:

Code:

CREATE PROCEDURE proc_checkAnswer
	@UserID Int,
	@SecAnswer Nvarchar(30)
AS
	DECLARE @TheAnswer Nvarchar(30)
	SELECT @TheAnswer = SecAnswer FROM tblUsers WHERE UserID = @UserID
	
	PRINT @TheAnswer
	PRINT @SecAnswer
	
	IF @TheAnswer = @SecAnswer 
		RETURN 1
	ELSE
		RETURN 2
GO

Only start calling the SP from your program once you know the SP itself functions correctly.

asherman86 · #3 (**permalink**) 12-06-11, 20:16

^_^; Thank you for the input. I guess I'll do some more testing.

Internet Services

Web Development

Digital Marketing

Consumer Tech