Retrieve Messages from SQL Stored Procedure in VB.Net

Question

I am working on created a login form for an application I am working on. I have my application set up to properly connect to the database and run stored procedures and queries on the database as well.

However I am unsure how to send messages from the database to my VB.Net Application. Right now I have essentially two methods that execute code for my database:

Public Function ExecuteCMD(ByRef CMD As SqlCommand) As DataTable
    Dim DS As New DataSet()
    Try
        OpenDBConnection()
        CMD.Connection = DB_CONNECTION
        If CMD.CommandText.Contains(" ") Then
            CMD.CommandType = CommandType.Text
        Else
            CMD.CommandType = CommandType.StoredProcedure
        End If
        Dim adapter As New SqlDataAdapter(CMD)
        adapter.SelectCommand.CommandTimeout = 300
        adapter.Fill(DS)
    Catch ex As Exception
        Throw New Exception("Database Error: " & ex.Message)
    Finally
        CloseDBConnection()
    End Try
    Return DS.Tables(0)
End Function

Public Function ExecuteCMDWithReturnValue(ByRef CMD As SqlCommand) As Boolean
    Try
        OpenDBConnection()
        CMD.Connection = DB_CONNECTION
        CMD.Parameters.Add("@ret", SqlDbType.Int).Direction = ParameterDirection.ReturnValue
        CMD.CommandType = CommandType.StoredProcedure
        CMD.ExecuteNonQuery()
        Dim result As Object = CMD.Parameters("@ret").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

These functions honestly work fine, but they are horrible for error processing.

For example I'd like to be able to set up my Store Procedure for logging in to the application to return a "Username not found" or "Password incorrect" message so that I can display to my user exactly what the problem is, as opposed to just returning a generic "Login information incorrect" message from only returning true or false on the logging in going through or not.

I unfortunately do not know exactly how to do this on either end. I don't know what to set up on the SQL Server side to have it spit out messages in procedures, and I don't know hot to receive those messages in VB.Net.

Answer 1

You can verify your user right in VB. I don't think it is a good idea to tell the user If the password or user name is wrong (or if both are wrong). If this data is password protected then it should be protected from malicious logins. It would help a hacker to know what was wrong.

Private Function VerifyPassword(pword As String, uname As String) As Boolean
    Using cn As New SqlConnection(My.Settings.UsersConnectionString)
    Dim cmd As New SqlCommand("Select Count(*) From Users Where UserName = @UserName And UserPassword = @Password;", cn)
    cmd.Parameters.Add("@UserName", SqlDbType.VarChar, 100).Value = uname
    cmd.Parameters.Add("@Password", SqlDbType.VarChar, 100).Value = pword

        Try
            cn.Open()
            Dim i As Integer = CInt(cmd.ExecuteScalar())
            If i > 0 Then Return True
            Return False
        Catch ex As Exception
            Throw
        Finally
            cn.Close()
            cmd.Dispose()
        End Try
    End Using
End Function

Of course the password is stored hashed with a salt.