How to create a log history in VB.NET and saves it into mysql workbench database?-CodePudding

I only know few knowledge about vb.net and I am trying to understand it as much as I can. I've tried the code below for login button to save the login date & time, and it works. Yet, I'm not sure what code to input for logout button. Please help me about this, thankyou! :)

Private Sub Button1_LoggedIn(sender As Object, e As EventArgs) Handles Button1.Click
    Dim command As New MySqlCommand("SELECT `StudentId`, `Account Password` FROM `usersaccount` WHERE `StudentId` = @username AND `Account Password` = @password", connection)
    command.Parameters.Add("@username", MySqlDbType.VarChar).Value = Username.Text
    command.Parameters.Add("@password", MySqlDbType.VarChar).Value = Pass.Text
    Dim adapter As New MySqlDataAdapter(command)
    Dim table As New DataTable()

    adapter.Fill(table)

    If table.Rows.Count = 0 Then

        MessageBox.Show("Invalid Username Or Password")

    Else

        Using con As New MySqlConnection("server=localhost;username=root;password=;database=logsrecord"),
                cmd As New MySqlCommand("Insert into loghistory.logsrecord (Username, Date, LoggedIn) Values (@Username, @Date, @LoggedIn);", con)
            cmd.Parameters.Add("@Username", MySqlDbType.VarChar).Value = Username.Text
            cmd.Parameters.Add("@Date", MySqlDbType.VarChar).Value = DateTime.Now.ToString("MMM-dd-yyyy")
            cmd.Parameters.Add("@LoggedIn", MySqlDbType.VarChar).Value = TimeOfDay.ToString("hh:mm:ss")
            con.Open()
            cmd.ExecuteNonQuery()
        End Using
        Form3.Show()
        Me.Hide()


    End If

End Sub

CodePudding user response：

I changed your buttton name to one that is more descriptive.

You used a Using block for your second database connection but not the first. You want to get into the habit of always using Using blocks if the object lists a Dispose method in the docs.

You don't need to pull down the Account Password. It isn't necessary for your code and the less the password is on the wire, the better. Actually, you should never store passwords as plain text. They should always be salted and hashed.

MySql is a relational database. Tables can be associated with each other with Primary Key/Foreign Key relationships. You can use the primary key from the usersaccount table as a foreign key in the logsrecord table.

Private OPConStr As String = "Your connection string."

Private Sub btnLogIn_Click(sender As Object, e As EventArgs) Handles btnLogIn.Click
    Dim ID As Integer
    Using connection As New MySqlConnection(OPConStr),
            cmd As New MySqlCommand("SELECT `StudentId` FROM `usersaccount` WHERE `StudentId` = @username AND `Account Password` = @password", connection)
        cmd.Parameters.Add("@username", MySqlDbType.VarChar).Value = Username.Text
        cmd.Parameters.Add("@password", MySqlDbType.VarChar).Value = Pass.Text
        connection.Open()
        ID = CInt(cmd.ExecuteScalar())
    End Using
    If ID = 0 Then
        MessageBox.Show("Invalid Username Or Password")
        Exit Sub
    End If
    Using con As New MySqlConnection("server=localhost;username=root;password=07292021;database=logsrecord"),
                cmd As New MySqlCommand("Insert into loghistory.logsrecord (StudentID, DateIn, Action) Values (@ID, @Date, @Action);", con)
        cmd.Parameters.Add("@ID", MySqlDbType.VarChar).Value = ID
        cmd.Parameters.Add("@Date", MySqlDbType.DateTime).Value = Now()
        cmd.Parameters.Add("@Action", MySqlDbType.Int32).Value = 1
        con.Open()
        cmd.ExecuteNonQuery()
    End Using
    Form3.Show()
    Hide()
End Sub

EDIT

To update the login record with a logout DateTime you will need the primary key of the login record. Let's assume you have an Auto-Increment field for the logsrecord table for the primary key. You can use Select LAST_INSERT_ID(); following your insert query. https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id Then ExecuteScalar will return the Primary Key.

You will need to store this value in your application until the user logs out. I assumed a Module with a variable called LogInRecordID.

The amended code might look like this.

    Dim strSql = "Insert into loghistory.logsrecord (StudentID, DateIn, Action) Values (@ID, @Date, @Action);
                  Select LAST_INSERT_ID();"
    Dim InsertRecordID As Integer
    Using con As New MySqlConnection("server=localhost;username=root;password=07292021;database=logsrecord"),
            cmd As New MySqlCommand(strSql, con)
        cmd.Parameters.Add("@ID", MySqlDbType.VarChar).Value = ID
        cmd.Parameters.Add("@Date", MySqlDbType.DateTime).Value = Now()
        cmd.Parameters.Add("@Action", MySqlDbType.Int32).Value = 1
        con.Open()
        InsertRecordID = CInt(cmd.ExecuteScalar())
    End Using
    SomeModule.LogInRecordID = InsertRecordID

When the user logs out call code like the following. A user can just close an application without properly logging out. You may wish to call this from an Application Event. https://docs.microsoft.com/en-us/dotnet/visual-basic/developing-apps/programming/log-info/how-to-log-messages-when-the-application-starts-or-shuts-down

Private Sub LogOut()
    Dim strSql = "Update loghistory.logsrecord Set DateOut = @Date Where ID = @ID;"
    Using cn As New MySqlConnection(OPConStr),
            cmd As New MySqlCommand(strSql, cn)
        cmd.Parameters.Add("@Date", MySqlDbType.DateTime).Value = Now
        cmd.Parameters.Add("@ID", MySqlDbType.Int32).Value = SomeModule.LogInRecordId
        cn.Open()
        cmd.ExecuteNonQuery()
    End Using
End Sub