How to display/use the data in stored procedure query using VB.NET

Question

My problem is I have a select query that gather all the data. And right now I want to display if it is ADMIN or EMPLOYEE. This is my stored procedure

ALTER PROCEDURE [dbo].[sp_selectusers]
    @username varchar(50),
    @password varchar(50),
    @result int output
AS 
BEGIN
    IF EXISTS (SELECT * FROM tbl_credentials 
               WHERE username LIKE @username AND password LIKE @password)
        SET @result = 1
    ELSE
        SET @result = 0

    RETURN @result
END

And this is my VB code

            cm = New SqlCommand("sp_selectusers", cn)
            With cm
                .CommandType = CommandType.StoredProcedure
                .Parameters.AddWithValue("@username", TextBox1.Text)
                .Parameters.AddWithValue("@password", TextBox2.Text)
                .Parameters.Add("@result", SqlDbType.Int).Direction = ParameterDirection.Output
                .ExecuteScalar()


                If CInt(.Parameters("@result").Value = 1) Then
                    MsgBox("Welcome " & .Parameters("@username").Value, MsgBoxStyle.Information)

               "If then 
                        Me.Hide()
                        Form_Admin.Show()
                    ElseIf
                        Me.Hide()
                        Form_Employee.Show()
                    End If" this code is incomplete

                Else
                    MsgBox("Account doesn't exists", MsgBoxStyle.Critical)
                End If

            End With

I want to add a condition ("MISSING CODE") here that if the query is ADMIN proceed to admin form else EMPLOYEE form but I don't know how to call the data in stored procedure.

Answer 1

Just add @role output variable

ALTER PROCEDURE [dbo].[sp_selectusers]
-- Add the parameters for the stored procedure here
@username varchar(50),
@password varchar(50),
@result int output,
@role varchar( 30 ) output,
AS BEGIN

set @role = NULL
set @role = ( select TOP 1 Role FROM tbl_credentials WHERE username = @username and password = @password order by id )
set @result = ( case when not @role is null then 1 else 0 end )

return @result
END

In .NET code add a corresponding parameter as well.

You should also follow @AlwaysLearning suggestions in comments, namely:

DO NOT store plain text passwords. You have to either hash them (How to hash a password) or encrypt them if you really must have an ability to decrypt the password.

And fix AddWithValue code.