CODEWITHSUNDEEP
sqlvb.netreturn-value
Skitzafreak
Skitzafreak

Reputation: 1917

Getting Error with SQL Return Value (Missing Parameter)

I am trying to program a connection between my SQL database and a VB.Net application. I would like to setup the stored procedures in my Database such that if there are querying errors, I can transmit some specifics about what is wrong with the query back to the user of the VB.Net app.

To set this up I tried putting together a simple little "Login" Procedure, as well as the code to run in on VB.

SQL Procedure:

CREATE PROCEDURE dbo.AuthenticateLogin(
    @Username VARCHAR(100),
    @Password VARCHAR(100),
    @response NVARCHAR(500) OUTPUT
)AS
BEGIN
    DECLARE @UserID INT
    IF(EXISTS(SELECT username FROM users WHERE username = @Username))
    BEGIN
        SET @UserID = (SELECT users_id FROM users WHERE username = @Username AND user_password = @Password)
        IF @UserID IS NULL
            SET @response = 'Incorrect Password'
        ELSE
            SET @response = 'Success'
    END
    ELSE
        SET @response = 'Invalid Login'
END
GO

VB.Net Code:

Public Function AuthenticateLogin(ByVal username As String, ByVal password As String) As Boolean
    Dim CMD As New SqlCommand("AuthenticateLogin")
    CMD.Parameters.Add("@UserName", SqlDbType.VarChar).Value = username
    CMD.Parameters.Add("@Password", SqlDbType.VarChar).Value = password
    AuthenticateLogin = ExecuteCMDWithReturnValue(CMD)
End Function

Public Function ExecuteCMDWithReturnValue(ByRef CMD As SqlCommand) As Boolean
    Try
        OpenDBConnection()
        CMD.Parameters.Add("@response", SqlDbType.NVarChar).Direction = ParameterDirection.ReturnValue
        CMD.Connection = DB_CONNECTION
        CMD.CommandType = CommandType.StoredProcedure
        CMD.ExecuteNonQuery()
        Dim result As Object = CMD.Parameters("@response").Value
        Return If(Convert.ToInt32(result) = 1, False, True)
    Catch ex As Exception
        Throw New Exception("Database Error: " & ex.Message)
        Return False
    Finally
        CloseDBConnection()
    End Try
End Function

Now when I run the application, I am getting an exception thrown when I am trying to run my AuthenticateLogin() method. The error I am getting is:

"Procedure or function 'AuthenticateLogin' expects parameter '@response', which was not supplied."

I don't understand why I am getting this error, because I am supplying it in the VB.Net code. I tried running this through the Debugger, and the line is it specifically throwing the exception at is:

CMD.ExecuteNonQuery()

So what is it I am doing wrong here, and how do I fix it?

EDIT: Accidentally had the wrong line of code in as to where the exception was being thrown.

Upvotes: 1

Views: 940

Answers (1)

the_lotus
the_lotus

Reputation: 12748

You should use a different Direction since it is an output and nto a return value.

CMD.Parameters.Add("@response", SqlDbType.NVarChar).Direction = ParameterDirection.Output

Upvotes: 3

Related Questions