Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2011
    Location
    Houston, TX
    Posts
    32

    Question Unanswered: Return Value Woes

    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.
    Last edited by asherman86; 12-06-11 at 18:14.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Nov 2011
    Location
    Houston, TX
    Posts
    32

    Thanks!

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •