I am using a method with a stored procedure, but it's always returning false

stored procedure vs function in sql server performance
difference between stored procedure and function and view in sql server
how to call user defined function in stored procedure in sql server
how to call table-valued function in stored procedure in sql server
stored procedure and function with example
table-valued function vs stored procedure
stored procedures examples
stored procedure in dbms

I am using bool method with Visual Studio 2015 and SQL Server 2005.

When I am passing correct details and click loginButton, the code always returns false from the stored procedure.

This is my stored procedure in SQL Server 2005:

ALTER PROCEDURE [dbo].[UserCheckLoginDetails]
    (@IsLoginIdCorrect BIT OUTPUT,
     @IsPasswordCorrect BIT OUTPUT,
     @LoginID NVARCHAR(200),
     @Password NVARCHAR(20)
    )
AS
BEGIN
    SET @IsLoginIdCorrect = 0
    SET @IsPasswordCorrect = 0

    IF EXISTS (SELECT * FROM UserInfo 
               WHERE loginid = @LoginID AND password = @Password)
    BEGIN
        SET @IsLoginIdCorrect = 1
        SET @IsPasswordCorrect = 1
    END
    ELSE
        IF EXISTS (SELECT * FROM UserInfo WHERE loginid = @LoginID)
        BEGIN
            SET @IsLoginIdCorrect = 1
        END
END

This is my method returning True or False:

Private Sub GetIsUserLoginCorrect(IsLoginIdCorrect As Boolean, IsPasswordCorrect As Boolean)
    Using Conn As New SqlConnection(_SqlCon)
        Using cmd As New SqlCommand("UserCheckLoginDetails", Conn)
            cmd.CommandType = CommandType.StoredProcedure
            Conn.Open()

            'OutPut Parameters
            cmd.Parameters.Add("@IsLoginIdCorrect", SqlDbType.Bit).Direction = ParameterDirection.Output
            cmd.Parameters.Add("@IsPasswordCorrect", SqlDbType.Bit).Direction = ParameterDirection.Output

            'InPut Parameters
            cmd.Parameters.AddWithValue("@LoginID", LoginIDTextBox.Text)
            cmd.Parameters.AddWithValue("@Password", PasswordTextBox.Text)
            cmd.ExecuteNonQuery()

            ' Assign Parameters
            IsLoginIdCorrect = Convert.ToBoolean(cmd.Parameters("@IsLoginIdCorrect").Value)
            IsPasswordCorrect = Convert.ToBoolean(cmd.Parameters("@IsPasswordCorrect").Value)

        End Using
    End Using
End Sub

This is the Login button click event handler, even when I provide the correct values, it still always returns false:

Private Sub LoginButton_Click(sender As Object, e As EventArgs) Handles LoginButton.Click
    Try
        Dim IsLoginIdCorrect, IsPasswordCorrect As Boolean
        GetIsUserLoginCorrect(IsLoginIdCorrect, IsPasswordCorrect)

        If IsLoginIdCorrect And IsPasswordCorrect = True Then
            Me.Hide()
            ' User Information
            DeshBoard.MainUserIdLabel.Text = Me.MainUserIdLabel.Text
            DeshBoard.UserNameLabel.Text = Me.UserNameLabel.Text
            DeshBoard.UserLoginIdLabel.Text = Me.UserLoginIdLabel.Text
            DeshBoard.UserLevelLabel.Text = Me.UserLevelLabel.Text
            'Organanization Information
            DeshBoard.MainOrgIDLabel.Text = Me.MainOrgIDLabel.Text
            DeshBoard.OrgNameLabel.Text = Me.OrgNameLabel.Text
            DeshBoard.OrgTelLabel.Text = Me.OrgTelLabel.Text
            DeshBoard.OrgEmailLabel.Text = Me.OrgEmailLabel.Text
            DeshBoard.OrgAddressLabel.Text = Me.OrgAddressLabel.Text
            DeshBoard.Show()
        Else
            If IsLoginIdCorrect = False Then
                MessageBox.Show("Login ID is not correct...!!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                LoginIDTextBox.Clear()
                PasswordTextBox.Clear()
                LoginIDTextBox.Focus()
            Else
                MessageBox.Show("Password ID is not correct...!!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                PasswordTextBox.Clear()
                PasswordTextBox.Focus()
            End If
        End If

    Catch ex As ApplicationException
        MessageBox.Show("Error: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try
End Sub

Thank you very much.

You need to add ByRef to both arguments in Sub GetIsUserLoginCorrect(). To demonstrate, try the following with and without ByRef.

Private Sub ChangeBoolean(ByRef TorF As Boolean)
    TorF = True
End Sub
Private Sub OPCode2()
    Dim TorF As Boolean
    ChangeBoolean(TorF)
    Debug.Print(TorF.ToString) ' Result False without ByRef in ChangeBoolean
    'When ByRef is added result is True
End Sub

Calling a Function From a Stored Procedure in SQL Server 2012, Now I want to call this from a stored procedure. The example is developed in SQL Server 2012 using the SQL Server Management Studio. Calling a Stored Procedure as a Method on a Connection object. You can call a stored procedure as if it were a native method on the associated open Connection object. This is similar to calling a named command on the Connection object.

First off, a method can refer to either a sub or a function. A sub is a method that performs an action. A function is a method that calculates or retrieves one or more values.

A sub should not be called Getxxx, because its primary purpose should not be returning a value.

A function should be used to return values. Since you are trying to retrieve multiple values, if you were using 2017 I would suggest returning a named tuple with your two values, since you aren’t I would create an object that has the values and return that.

On a totally different note, you really can’t tell the difference between right user wrong password and wrong user right password and wrong user wrong password - so you shouldn’t tell someone you can. You just say login unsuccessful login, or invalid username/password combination.

Functions vs stored procedures in SQL Server, In a function, it is mandatory to use the RETURNS and RETURN arguments, whereas in a stored procedure is not necessary. In few words, a  To create a procedure, using: SQL Server Management Studio, Transact-SQL. Permissions. Requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created. How to Create a Stored Procedure. You can use one of the following: SQL Server Management Studio. Transact-SQL. Using SQL Server Management Studio

There's a lot wrong with your code.

Firstly, why are you using so much of SQL code ? Correct me if i am wrong : You are trying to build a log in system. So much of SQL code or even the stored procedure is worthless here. You can simply write the SQL statements in your code by using the SqlCommand class. Even though you are using the ALTER PROCEDURE statement, i can surely say that things can be simplified here.

You are also using the Me keyword. It's not C# where the use of this(same as Me in VB.Net) becomes compulsory. I assume it's a Windows Forms Application and if that's so, then using Me keyword to access it's child elements wouldn't result in any different if it's not used at all.

The next worth mentioning issues is your Name Conventions. Most or should i say all of your variables have the same name. For example : IsLoginIdCorrect - used both as a parameter of a method and also a variable inside a method.

The next issues is in these two lies :

Dim IsLoginIdCorrect, IsPasswordCorrect As Boolean
GetIsUserLoginCorrect(IsLoginIdCorrect, IsPasswordCorrect)

You are passing the boolean variables before they have been assigned any value. You are lucky it's not C# or this wouldn't even compile. Passing the boolean variables without assigning any value will, by default, set their values to False. So, literally, you are always passing the same value in which case, the outcome will always be the same.

The next issue is in your If statement inside your LoginButton_Click method aka LoginButton's click event handler's method :

If IsLoginIdCorrect And IsPasswordCorrect = True Then

The if statements, if described in simple words, means : If IsLoginIdCorrect and IsPasswordCorrect are true, then proceed.... So, in this case, IsPasswordCorrect = True doesn't affect much. However, this is not the best practice too. You should better follow the following coding rule while using If statements:

 If (IsLoginIdCorrect = True AndAlso IsPasswordCorrect = True) Then

AndAlso operators evaluates each side just like the And operator. The difference is that it would return False if the left side(IsLoginIdCorrect, in this case) returns False.

The next issues is the usage of ApplicationException. I don't understand why, in this era, you are using that class! This class is usually used to derive from and create exceptions. You can simply use Exception instead of ApplicationException.

Your Try-Catch block seems not useful as well. All of your codes inside the LoginButton_Click are in If conditions and they perform very basic operation. It is unlikely to ever throw any exception at all.

Your logics, for most part, are illogical(sorry to put it this way). In your GetIsUserLoginCorrect method, you are setting IsLoginIdCorrect and IsPasswordCorrect to either true or false but it wouldn't matter because they are parameters of the method itself. So even if you set their values, they will be reset when you call the method again. The reason why ByRef (according to Mary's answer) works is because ByRef, in short, means that you are pointing to original variable that you passed(not it's copy).

And finally, the solution you are looking for....

Even though i see you have marked Mary's answer as the answer, i would like to help you out a bit as-well.

Firstly, get rid of the stored procedure if possible and also if you are not using it anywhere else. I see you are using the If Exist condition inside your SQL queries. This is actually a nice move because according to performance reports, checking if data exists in a database/table using IF EXISTS yields the fastest output. So bravo for that one. But if you follow my advice and want to ditch the stored procedure, then you need to get rid of the IF EXISTS statement as well. Rather, you can simply use the SELECT statement itself, use the ExecuteScalar method of the SqlCommand class, convert it's value to Integer and check if the value of the Integer is 1 or not.

Example :

Dim cmd = New SqlCommand("SELECT COUNT(*) FROM UserInfo 
           WHERE loginid = @LoginID AND password = @Password")
Dim Exists = Convert.ToInt32(cmd.ExecuteScalar)
If Exists = 1 Then
''Code if data exists
End if

Note that i have used Convert.ToInt32 here. This will prevent null-reference exception as when ExecuteScalar returns Null, it will be converted to 0 integer value.

Also, why are you using GetIsUserLoginCorrect as method ? You can simply use it as a function and return required values. As you are returning multiple values, you can easily use the Tuple type as your function's type:

Private Function GetIsUserLoginCorrect(IsLoginIdCorrect As Boolean, IsPasswordCorrect As Boolean) As Tuple(of Boolean, Boolean)
....
....
return Tuple.Create(IsLoginIdCorrect, IsPasswordCorrect)
End Sub

Usage

 Dim IsLoginCorrect = GetIsUserLoginCorrect(first_boolean_variable,second_boolean_variable).Item1

  Dim IsPasswordCorrect = GetIsUserLoginCorrect(first_boolean_variable,second_boolean_variable).Item2

One last thing. As you are showing DeshBoard form after hiding the main form, make sure to call MainForm.Close on the Dashboard form's Closing/Closed event. This will ensure the application's exit(unless you have other plans for the main form, of course).

Hope this answer helps you.

Defining and Using Stored Procedures, This method also returns the procedure type: “function” or “query”. Defining Stored Procedures. As with most aspects of InterSystems SQL, there are two ways of  In this article, I am saving a record into the database using a stored procedure in a web service application. At first create a database and a stored procedure. Creating Database: I am creating a table "userdet" which has four columns as "userid", "username", "city" and "age". create table userdet (UserId int primary key, UserName varchar(30),

Using a stored procedure with a return status, Address table. In the following example, an open connection to the sample database is passed in to the function, and the execute method is used  For example, database admin will create a procedure, and multiple users will access the procedure from JAVA, C#, C++, Python, R etc. Instead of sending hundreds of lines of code, it is better to use stored procedure. So that we can call the single statement (stored procedure name),

Using a stored procedure with output parameters, If it did, the executeQuery method would be used. Stored procedures can return update counts and multiple result sets. The Microsoft JDBC Driver  How to: Retrieve Data With Stored Procedures. This topic provides basic examples of how to execute stored procedures by using the Context API and Telerik Data Access Commands. Generally, there are two approaches you can use to execute stored procedures. The first one is to use the OpenAccessContext.ExecuteQuery<T> method. It requires less code

Learn about Stored Procedures, Stored procedure features and command syntax are specific to the database engine. Each language, such as PHP or C#, has its specific methods for doing so. There are several benefits to using stored procedure. I'm here to help you​. Sometimes, you may use a Linq projection or even a stored procedure with the resulting object or object graph that has nothing to do with any EF virtual object on the model. You can't use any DBSet to get the results back.

Comments
  • Good to see that you passed me :)
  • Hi @zackraiyan. Good to see you. Did you get a job. I am guessing you are very busy.
  • @Mary , no i didn't get a job. I am working on my online project. I just did a search on SO for your name to see if you are still here, i was glad to find you !