Question Using Database to Verify Password input by user

[bchaney]

Hello,

I'm trying to write vb code to verify a password input by a user to known table passwords in access database. The form has a drop down combo box that has userID's that are selectable. I'm trying to get the index number out of the combo box selection and use that to look up what the password should be. With that information i can then compare the stored password with the password typed into the text box below the combo box. All of the examples i've found that direct me show the code in regular Visual Basic. I'm using Visual Basic 2010 with the .net architecture.

Any help would be greatly appreciated. If this thread has already been posted and answered (or something like it) can someone point me to it? I just need to be pointed in the right direction.

Thanks.

 

[r3plica]

why would you want the index number of the combo box?
If you have a drop down list, then surely you want the text of the username to compare with the username in the database and match the password?

I hate access, so I am not going to provide a solution because I think access should be binned and real databases should be used, even if it is SQL Express, it is still better than Access imo.

 

[bchaney]

This is the first time i've tackled setting up any kind of database. The only thing i've been shown is Access. What database do you prefer? I'm willing to try anything that will accomplish what i need it to do which is store data entered into a form and easily be callable later on by a report generator. That's pretty much it.

As far as the question above is concerning, how would i use the userID to look up the password in the database? I'm just stuck on this part for now.

 

[r3plica]

For a Windows application you could set up and SQL database (VS 2010 and all VS come with SQL Express, but you will have to download SQL Management Studio to manage them).

What you should do is create a table called tUsers (for example) with a ID, Name, Password fields and any other bits of information you want to store.
Create a stored procedure that looks like this:

CREATE PROC ValidateUser
	@UserID INT,
	@Password VARCHAR(50),
	@Valid BIT OUTPUT
AS
BEGIN
	SELECT Username
	FROM tUsers
	WHERE ID = @UserID
	AND [Password] = @Password
	
	SET @Valid = @@ROWCOUNT
END
GO

Then create your code to run that SP like so:

Public Class UserAccessLayer
    Inherits BaseDataAccess

    Public Shared Function ValidateUser(ByVal iUser As Integer, ByVal sPassword As String) As Boolean
        Dim SQLCmd As New SqlCommand
        SetConnectionString = ConfigurationManager.ConnectionStrings("myConnection").ConnectionString
        SQLCmd.CommandType = CommandType.StoredProcedure
        SQLCmd.CommandText = "ValidateUser"
        SQLCmd.Parameters.Add("@UserID", SqlDbType.Int).Value = iUser
        SQLCmd.Parameters.Add("@Password", SqlDbType.VarChar, 50).Value = sPassword
        SQLCmd.Parameters.Add("@Valid", SqlDbType.Bit).Direction = ParameterDirection.Output
        ExecuteNonSelect(SQLCmd)
        Return CBool(SQLCmd.Parameters("@Valid").Value)
    End Function
End Class

you will need my base data access layer for this to work, just copy and paste the code into a new class called BaseDataAccess:

Imports System.Data
Imports System.Data.SqlClient

Public MustInherit Class BaseDataAccess
    Private Shared mstrConnectionString As String
    Private Shared mConn As SqlConnection

    Public Shared WriteOnly Property SetConnectionString() As String
        Set(ByVal value As String)
            mstrConnectionString = value
        End Set
    End Property

    Shared Function FillDataSet(ByVal pSQLCmd As SqlCommand) As DataSet
        Dim ds As New DataSet
        Dim da As New SqlDataAdapter
        If mstrConnectionString <> "" Then
            OpenConnection()
            pSQLCmd.Connection = mConn ' Set our connection
            da.SelectCommand = pSQLCmd ' Set the SelectCommand
            da.Fill(ds) ' Fill our DataSet
            da.Dispose() ' Dispose our Data Adapter
            CloseConnection() ' Call our Sub
        Else
            Throw New ApplicationException("Connection String has not been set")
        End If
        Return ds
    End Function

    Shared Sub ExecuteNonSelect(ByVal pSQLCmd As SqlCommand)
        If mstrConnectionString <> "" Then
            OpenConnection() ' Call our Sub
            pSQLCmd.Connection = mConn ' Set our connection
            pSQLCmd.ExecuteNonQuery() ' Execute
            CloseConnection() ' Call our Sub
        Else
            Throw New ApplicationException("Connection String has not been set")
        End If
    End Sub

    Private Shared Sub OpenConnection()
        mConn = New SqlConnection ' Create our connection
        mConn.ConnectionString = mstrConnectionString ' Set our ConnectionString
        mConn.Open() ' Open the connection
    End Sub

    Private Shared Sub CloseConnection()
        If mConn.State = ConnectionState.Open Then
            mConn.Close() ' Close
            mConn.Dispose() ' Dispose
        End If
    End Sub
End Class

and that should be it.
For the username dropdownlist user ddlUser.SelectedValue to get the userID, you should be binding it by setting the displaymember as Username and the valuemember to UserID.

Hope that helps.