How do I replace the username and password of software users in an excel database after they have been verified by an administrator in vb.net

Question

I am trying to implement code associated with clicking the reset button in the administrative module of my application such that once a user has already been granted administrative access at the time the user was added to the excel database, and said username and password associated with the user are requested for and verified when the reset button is clicked, the application replaces the old username and password respectively of the said user in the excel database when that information is requested after verification. The relevant code block is as below;

    Dim processes() As Process = Process.GetProcesses
    For p As Integer = processes.Count - 1 To 0 Step -1
        If processes(p).ProcessName = "EXCEL" Then
            processes(p).Kill()
        End If
    Next
    oExcel = CreateObject("Excel.Application")
    Dim Path As String = "C:\Users\Vince\Desktop\Research\Current Research\Automating the GSRS\GSRS 
    with horizontal curve v 1.0\bin\Debug\Usernames.xlsx"
    oBook = oExcel.Workbooks.Open(Path)
    oSheet = oBook.Worksheets(1)
    oSheet.activate()
    sheetCount = oSheet.Cells(oSheet.Rows.Count, "A").End(XlDirection.xlUp).row
    initialusername = "admin"
    initialpassword = "password"
    adminusername = InputBox("Please Enter admin username", "Alert")
    adminpassword = InputBox("Please Enter admin password", "Alert")
    usernameverify = InputBox("Please Enter new admin username", "Alert")
    passwordverify = InputBox("Please Enter new admin password", "Alert")
    Do While String.IsNullOrEmpty(adminusername) = True And String.IsNullOrEmpty(adminpassword) = 
    True
        MsgBox("You are not authorized to reset the login credentials!",, "Alert")
        Me.txtusername.Text = ""
        Me.txtpassword.Text = ""
        Exit Sub
    Loop
    Do While String.IsNullOrEmpty(adminusername) = False And String.IsNullOrEmpty(adminpassword) = 
    False
        For j = 2 To sheetCount
            ReDim Preserve usernames(j)
            ReDim Preserve passwords(j)
            If oSheet.Cells(j, 5).Text = "Administrator" Then
                usernames(j) = oSheet.Cells(j, 3).Value
                passwords(j) = oSheet.Cells(j, 4).Value
            End If
        Next

        For j = 2 To sheetCount
            If oSheet.Cells(j, 5).Text = "Administrator" Then
                If (adminusername = initialusername Or adminusername = usernames(j)) And 
             (adminpassword = initialpassword Or adminpassword = passwords(j)) Then
                    oSheet.Cells(j, 3).Value = usernameverify
                    oSheet.Cells(j, 4).Value = passwordverify
                    Dim filefinal As IO.FileInfo = 
                    My.Computer.FileSystem.GetFileInfo("Usernames.xlsx")
                    filefinal.IsReadOnly = False
                    oSheet.SaveAs(Path)
                    MsgBox("Administrator credentials have been updated",, "Success")
                    oExcel.Workbooks.Close()
                    oExcel.Quit()
                    Me.txtusername.Text = ""
                    Me.txtpassword.Text = ""
                    Exit Sub
                    ElseIf (adminusername <> usernames(j)) And (adminpassword <> passwords(j)) Then
                    MsgBox("You are Not authorized To reset the login credentials!",, "Alert")
                    Me.txtusername.Text = ""
                    Me.txtpassword.Text = ""
                    Exit Sub
                    ElseIf (adminusername = usernames(j)) And (adminpassword <> passwords(j)) Then
                    MsgBox("You are Not authorized To reset the login credentials!",, "Alert")
                    Me.txtusername.Text = ""
                    Me.txtpassword.Text = ""
                    Exit Sub
                    ElseIf (adminusername <> usernames(j)) And (adminpassword = passwords(j)) Then
                    MsgBox("You are Not authorized To reset the login credentials!",, "Alert")
                    Me.txtusername.Text = ""
                    Me.txtpassword.Text = ""
                    Exit Sub
                End If
            End If
        Next

        For j = 2 To sheetCount
            ReDim Preserve usernames(j)
            ReDim Preserve passwords(j)

            If oSheet.Cells(j, 5).Text = "Regular User" Then
                usernames(j) = oSheet.Cells(j, 3).Value
                passwords(j) = oSheet.Cells(j, 4).Value
            End If
        Next

        For j = 2 To sheetCount
            If oSheet.Cells(j, 5).Text = "Regular User" Then
                If (adminusername = initialusername Or adminusername = usernames(j)) And 
                   (adminpassword = initialpassword Or adminpassword Is passwords(j)) Then
                    MsgBox("You are Not authorized To reset the login credentials!",, "Alert")
                    Me.txtusername.Text = ""
                    Me.txtpassword.Text = ""
                    Exit Sub
                    ElseIf (adminusername <> usernames(j)) And (adminpassword <> passwords(j)) Then
                    MsgBox("You are Not authorized To reset the login credentials!",, "Alert")
                    Me.txtusername.Text = ""
                    Me.txtpassword.Text = ""
                    Exit Sub
                    ElseIf (adminusername = usernames(j)) And (adminpassword <> passwords(j)) Then
                    MsgBox("You are Not authorized To reset the login credentials!",, "Alert")
                    Me.txtusername.Text = ""
                    Me.txtpassword.Text = ""
                    Exit Sub
                    ElseIf (adminusername <> usernames(j)) And (adminpassword = passwords(j)) Then
                    MsgBox("You are Not authorized To reset the login credentials!",, "Alert")
                    Me.txtusername.Text = ""
                    Me.txtpassword.Text = ""
                    Exit Sub
                End If
            End If
        Next
    Loop
    Do While String.IsNullOrEmpty(adminusername) = True And String.IsNullOrEmpty(adminpassword) = 
    False
            MsgBox("You are Not authorized To reset the login credentials!",, "Alert")
            Me.txtusername.Text = ""
            Me.txtpassword.Text = ""
            Exit Sub
        Loop
        Do While String.IsNullOrEmpty(adminusername) = False And String.IsNullOrEmpty(adminpassword) 
        = True
        MsgBox("You are Not authorized To reset the login credentials!",, "Alert")
        Me.txtusername.Text = ""
        Me.txtpassword.Text = ""
        Exit Sub
        Loop
        Do While String.IsNullOrEmpty(adminusername) = True And String.IsNullOrEmpty(adminpassword) = 
        False
        MsgBox("You are Not authorized To reset the login credentials!",, "Alert")
        Me.txtusername.Text = ""
        Me.txtpassword.Text = ""
        Exit Sub
    Loop
    Do While String.IsNullOrEmpty(adminusername) = False And String.IsNullOrEmpty(adminpassword) = 
    True
        MsgBox("You are Not authorized To reset the login credentials!",, "Alert")
        Me.txtusername.Text = ""
        Me.txtpassword.Text = ""
        Exit Sub
    Loop

After tinkering with it for a while, I believe the culprit is this code block;

  For j = 2 To sheetCount
        If oSheet.Cells(j, 5).Text = "Administrator" Then
            If (adminusername = initialusername Or adminusername = usernames(j)) And 
         (adminpassword = initialpassword Or adminpassword = passwords(j)) Then
                oSheet.Cells(j, 3).Value = usernameverify
                oSheet.Cells(j, 4).Value = passwordverify.......
Next"
For some reason, as it loops, it doesn't discriminate on values of j based on my conditional 
If oSheet.Cells(j, 5).Text = "Administrator" Then.....It simply continues from j= 2 to sheetcount in +1 increments. How do I go around this? Anyone with the experience and expertise to develop a workaround? Thanks.

@Mary, your expertise would very much be appreciated here. ;) — DaVince294, Feb 08 '21 at 17:35
I don't want to question your choice to use an excel file as a database with some very critical information like usernames and passwords, but probably this code could be rewritten using OleDb without need to use Interop at all. — Steve, Feb 08 '21 at 17:38
@Steve, good point. This is really just a demo for my advisor. I'm building a civil engineering software and most people in my field don't know much about building software or security for that matter so functionality is my main consideration now. The actual final application will use MS-SQL as the database. I haven't used it in a while so I'm a bit rusty and that would require a bit of a learning curve my current circumstances won't allow me the time to re-learn before some critical deadlines. This is why I just wanted to figure this out with excel in the meantime. I hope that makes sense. — DaVince294, Feb 08 '21 at 18:06
Yes, it makes sense. But look at how to use OleDb to read an excel sheet like it is a table in a database. You can leverage the functionality of sql statements instead of being forced to use Interop. Start from here for example https://stackoverflow.com/questions/18511576/reading-excel-file-using-oledb-data-provider — Steve, Feb 08 '21 at 18:44
+1 For using OleDb, for the simple reason that when it comes to moving to SQL Server, it will make your life a whole lot easier. That said though, like Steve, even for a demo I would be questioning using Excel as the back end. IMO, there isn't too much of a learning curve with a simple SQL DB (especially since you've already had some experience) and you're likely to have to figure it out again at some stage anyway — Hursey, Feb 08 '21 at 19:34
Look into BCrpyt.Net for password security. It seems easy to use. No plain text passwords should be stored. — Mary, Feb 08 '21 at 19:38

How do I replace the username and password of software users in an excel database after they have been verified by an administrator in vb.net

0 Answers0