如何在VB.NET中創建日志歷史并將其保存到mysqlworkbench資料庫中？

我對 vb.net 知之甚少，我正在盡可能多地了解它。我已經嘗試了下面的登錄按鈕代碼來保存登錄日期和時間，并且它有效。但是，我不確定要為注銷按鈕輸入什么代碼。請幫我解決這個問題，謝謝！:)

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

uj5u.com熱心網友回復：

我將您的按鈕名稱更改為更具描述性的名稱。

您Using為第二個資料庫連接使用了一個塊，但不是第一個。Using如果物件Dispose在檔案中列出了方法，您希望養成始終使用塊的習慣。

您無需下拉Account Password. 您的代碼沒有必要，密碼越少越好。實際上，您永遠不應該將密碼存盤為純文本。他們應該總是加鹽和散列。

MySql 是一個關系型資料庫。表可以通過主鍵/外鍵關系相互關聯。您可以使用usersaccount表中的主鍵作為表中的外鍵logsrecord。

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

編輯

要使用注銷更新登錄記錄，DateTime您將需要登錄記錄的主鍵。假設您有一個logsrecord主鍵表的自動增量欄位。您可以使用Select LAST_INSERT_ID();以下插入查詢。https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id 然后ExecuteScalar將回傳主鍵。

您需要將此值存盤在您的應用程式中，直到用戶注銷。我假設了一個帶有名為LogInRecordID.

修改后的代碼可能如下所示。

    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

標籤：网络 mysql-工作台

上一篇：填充：SelectCommand連接屬性尚未初始化

下一篇：我需要使用byval傳遞一個串列，但是我知道我不能這樣做，因為串列仍然會被更改，但是創建一個“新”串列會減慢我的代碼