vb.net - 如何在登录表单 vb.net 中“刷新”MS Access 数据库？

Question

Imports System.Data.OleDb
Imports System.Data

Public Class frmLoginBP
    Dim connection As New OleDbConnection(My.Settings.Business_ProfileConnectionString)

    Private Sub lblInterfaceTypeTemp_Click(sender As Object, e As EventArgs)

    End Sub

    Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
        'Format the timer.
        lblDate.Text = Date.Now.ToString("dd-MM-yyyy hh:mm:ss")

    End Sub

    Private Sub lblDate_Click(sender As Object, e As EventArgs) Handles lblDate.Click
        'Format the timer.
        lblDate.Text = Date.Now.ToString("dd-MM-yyyy hh:mm:ss")
    End Sub

    Private Sub frmLoginBP_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'Show initial panels when loading the program.
        Timer1.Enabled = True

        Dim connection As New OleDbConnection(My.Settings.Business_ProfileConnectionString)
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        'button Register
        frmRegistrationBP.Show()
    End Sub

    Private Sub btnShowPassword(sender As Object, e As EventArgs) Handles PictureBox3.Click
        If txtBPPassword.UseSystemPasswordChar = True Then
            txtBPPassword.UseSystemPasswordChar = False
        Else
            txtBPPassword.UseSystemPasswordChar = True
        End If
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        If txtBPID.Text = "" Or txtBPPassword.Text = "" Then
            MsgBox("Enter Credentials", MsgBoxStyle.Exclamation, "Error!")
        Else
            If connection.State = ConnectionState.Closed Then
                connection.Open()
            End If
            Dim cmd As New OleDbCommand("select count(*) from [Business Details] where Email=? and Password=?", connection)
            cmd.Parameters.AddWithValue("@1", OleDbType.VarChar).Value = txtBPID.Text
            cmd.Parameters.AddWithValue("@2", OleDbType.VarChar).Value = txtBPPassword.Text
            Dim count = Convert.ToInt32(cmd.ExecuteScalar())

            If (count > 0) Then
                MsgBox("Login succeed!", MsgBoxStyle.Information)
                frmWelcomeNew.Show()
                Me.Hide()
            Else
                MsgBox("Account not found. Check your credentials!", MsgBoxStyle.Critical, "Error!")
            End If
        End If
    End Sub

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        txtBPID.Text = ""
        txtBPPassword.Text = ""
    End Sub
End Class

首先，我需要创建一个帐户才能登录我的系统，因此，我按下“注册”按钮。在我填写所需的文本框后，

Imports System.Data.OleDb

Public Class frmRegistrationBP
    Dim pro As String
    Dim connstring As String
    Dim command As String
    Dim myconnection As OleDbConnection = New OleDbConnection

    Private Sub lblInterfaceTypeTemp_Click(sender As Object, e As EventArgs)

    End Sub

    Private Sub btnUpload_Click(sender As Object, e As EventArgs) Handles btnUpload.Click
        'Declare variable.
        Dim opf As New OpenFileDialog

        'Filter the file types.
        opf.Filter = "Choose Image(*.jpg;*.png)|* .jpg;*.png"

        If opf.ShowDialog = DialogResult.OK Then

            'Assign the file to the picture box.
            pcbBusinessPhoto.Image = Image.FromFile(opf.FileName)
        End If

    End Sub

    Private Sub btnCancel_BizRegisPG1_Click(sender As Object, e As EventArgs) Handles btnCancel_BizRegisPG1.Click
        Me.Close()
    End Sub

    Private Sub btnReset_BizRegisPG1_Click(sender As Object, e As EventArgs) Handles btnReset_BizRegisPG1.Click
        txtBizAddress.Text = ""
        txtBizAddress.Text = ""
        txtBizName.Text = ""
        txtBizPhoneNo.Text = ""
        txtPassword.Text = ""
        txtPasswordReenter.Text = ""
        txtSSM.Text = ""
        cboBizType.Text = ""
        pcbBusinessPhoto.Image = Nothing
    End Sub

    Private Sub txtBizName_BizRegis_TextChanged(sender As Object, e As EventArgs) Handles txtBizName.TextChanged

    End Sub

    Private Sub btnNext_BizRegisPG1_Click(sender As Object, e As EventArgs) Handles btnNext_BizRegisPG1.Click

        If txtSQAnswer.Text = "" OrElse cboSQ.Text = "" OrElse txtSSM.Text = "" OrElse txtBizAddress.Text = "" OrElse txtBizEmail.Text = "" OrElse txtBizName.Text = "" OrElse txtBizPhoneNo.Text = "" OrElse txtPassword.Text = "" OrElse txtPasswordReenter.Text = "" OrElse cboBizType.Text = "" Then
            MsgBox("Please fill in the textboxes!", MsgBoxStyle.Critical, "Error!")
        ElseIf txtPasswordReenter.Text <> txtPassword.Text Then
            MsgBox("Your Reentered Password is not the same!", MsgBoxStyle.Critical, "Error!")
        Else
            If IsNumeric(txtBizPhoneNo.Text) = False Then
                MsgBox("Enter numeric value only in Company Phone Number", MsgBoxStyle.Critical, "Error!")
            ElseIf IsNothing(pcbBusinessPhoto.image) Then
                MsgBox("Please upload Business Photo", MsgBoxStyle.Critical, "Error!")
            Else
                pro = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\chiac\OneDrive\Desktop\OMC\Code\[OMC] Business Partner\[OMC] Business Partner\Business Profile.accdb"
                connstring = pro
                myconnection.ConnectionString = connstring
                myconnection.Open()
                command = " insert into [Business Details] values (@SSM,@BizName,@Address,@Email,@HP,@BizType,@Password,@BusinessPhoto,@OHFrom,@OHTo,@SQ1,SQ1Answer)"

                Dim cmd As OleDbCommand = New OleDbCommand(Command, myconnection)
                cmd.Parameters.Add(New OleDbParameter("@SSM", CType(UCase(txtSSM.Text), String)))
                cmd.Parameters.Add(New OleDbParameter("@BizName", CType(txtBizName.Text, String)))
                cmd.Parameters.Add(New OleDbParameter("@Address", CType(txtBizAddress.Text, String)))
                cmd.Parameters.Add(New OleDbParameter("@Email", CType(txtBizEmail.Text, String)))
                cmd.Parameters.Add(New OleDbParameter("@HP", CType(txtBizPhoneNo.Text, String)))
                cmd.Parameters.Add(New OleDbParameter("@BizType", CType(cboBizType.Text, String)))
                cmd.Parameters.Add(New OleDbParameter("@Password", CType(txtPassword.Text, String)))
                cmd.Parameters.Add(New OleDbParameter("@BusinessPhoto", pcbBusinessPhoto.Image))
                cmd.Parameters.Add(New OleDbParameter("@OHFrom", CType(cboOHFrom.Text, String)))
                cmd.Parameters.Add(New OleDbParameter("@OHTo", CType(cboOHTo.Text, String)))
                cmd.Parameters.Add(New OleDbParameter("@SQ1", CType(cboSQ.Text, String)))
                cmd.Parameters.Add(New OleDbParameter("@DQ1Answer", CType(txtSQAnswer.Text, String)))
                MsgBox("Record Saved")

                Try
                    cmd.ExecuteNonQuery()
                    cmd.Dispose()
                    myconnection.Close()
                    txtSSM.Clear()
                    txtBizAddress.Clear()
                    txtBizEmail.Clear()
                    txtBizName.Clear()
                    txtBizPhoneNo.Clear()
                    txtPassword.Clear()
                    txtPasswordReenter.Clear()
                    cboBizType.Text = ""
                    cboOHFrom.Text = ""
                    cboOHTo.Text = ""
                    pcbBusinessPhoto.Image = Nothing
                Catch ex As Exception

                    MsgBox(ex.Message)

                End Try


                frmRegistration2.Show()
                Me.Hide()
            End If
        End If
    End Sub

    Private Sub Panel1_Paint(sender As Object, e As PaintEventArgs) Handles Panel1.Paint

    End Sub

    Private Sub frmRegistrationBP_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    End Sub
End Class

Imports System.Data.OleDb

Public Class frmRegistration2

    Dim pro As String
    Dim connstring As String
    Dim command As String
    Dim myconnection As OleDbConnection = New OleDbConnection
    Dim finalGender As String
    Private Sub frmRegistration2_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    End Sub

    Private Sub btnUploadIC_Click(sender As Object, e As EventArgs) Handles btnUploadIC.Click
        'Declare variable.
        Dim opf As New OpenFileDialog

        'Filter the file types.
        opf.Filter = "Choose Image(*.jpg;*.png)|* .jpg;*.png"

        If opf.ShowDialog = DialogResult.OK Then

            'Assign the file to the picture box.
            pcbIC.Image = Image.FromFile(opf.FileName)
        End If

    End Sub

    Private Sub btnSubmit_BizRegisPG2_Click(sender As Object, e As EventArgs) Handles btnSubmit_BizRegisPG2.Click

        If txtIC.Text = "" OrElse txtFirstName.Text = "" OrElse txtLastName.Text = "" OrElse txtEmail.Text = "" OrElse txtHP.Text = "" OrElse dtpDOB.Text = "" Then
            MsgBox("Please fill in the textboxes!", MsgBoxStyle.Critical, "Error!")
        Else
            If IsNumeric(txtIC.Text) = False Then
                MsgBox("Please insert numerical value in Identity Card Number!", MsgBoxStyle.Critical, "Error!")
            ElseIf IsNothing(pcbIC.image) = True Then
                MsgBox("Please insert Owner Photo!", MsgBoxStyle.Critical, "Error!")
            ElseIf rbtFemale.Checked = False And rbtMale.Checked = False Then
                MsgBox("Please select your gender!", MsgBoxStyle.Critical, "Error!")
            ElseIf rbtAgree.Checked = False Then
                MsgBox("Please AGREE with Goodstopia Terms & Conditions!", MsgBoxStyle.Critical, "Error!")
            Else

                If rbtMale.Checked = True Then
                    finalGender = "Male"
                End If

                If rbtFemale.Checked = True Then
                    finalGender = "Female"
                End If

                pro = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\chiac\OneDrive\Desktop\OMC\Code\[OMC] Business Partner\[OMC] Business Partner\Business Profile.accdb"
                connstring = pro
                myconnection.ConnectionString = connstring
                myconnection.Open()
                command = " insert into [Owner Details] values (@IC,@FirstName,@LastName,@Email,@HP,@DOB,@Gender,@ICPhoto)"

                Dim cmd As OleDbCommand = New OleDbCommand(command, myconnection)

                cmd.Parameters.Add(New OleDbParameter("@IC", CType(UCase(txtIC.Text), String)))
                cmd.Parameters.Add(New OleDbParameter("@FirstName", CType(txtFirstName.Text, String)))
                cmd.Parameters.Add(New OleDbParameter("@LastName", CType(txtLastName.Text, String)))
                cmd.Parameters.Add(New OleDbParameter("@Email", CType(txtEmail.Text, String)))
                cmd.Parameters.Add(New OleDbParameter("@HP", CType(txtHP.Text, String)))
                cmd.Parameters.Add(New OleDbParameter("@DOB", CType(dtpDOB.Text, String)))
                cmd.Parameters.Add(New OleDbParameter("@Gender", finalGender))
                cmd.Parameters.Add(New OleDbParameter("@ICPhoto", pcbIC.Image))
                MsgBox("Record Saved")

                Try
                    cmd.ExecuteNonQuery()
                    cmd.Dispose()
                    myconnection.Close()
                    txtIC.Clear()
                    txtFirstName.Clear()
                    txtLastName.Clear()
                    txtHP.Clear()
                    txtEmail.Clear()
                    rbtFemale.Checked = False
                    rbtMale.Checked = False
                    pcbIC.Image = Nothing
                Catch ex As Exception

                    MsgBox(ex.Message)

                End Try
            End If

            Me.Hide()
            frmLoginBP.Show()


        End If

    End Sub

    Private Sub rbtAgreeTerms_BizRegis_CheckedChanged(sender As Object, e As EventArgs) Handles rbtAgree.CheckedChanged

    End Sub
End Class

我按下“提交”，我的 MS Access 登录数据库已经捕获了我输入的所有数据。

之后，登录表单将再次出现。但是，即使我已将详细信息保存在我的登录数据库中。我仍然无法登录到我的系统。我的猜测是这是因为登录表单中的数据库尚未“刷新”。

所以我的问题是如何在我的登录表单中“刷新”数据库？

标签： vb.net

Answer 1

连接使用它们在其Dispose方法中释放的非托管资源。你必须Dispose连接。Using...End Using块为我们处理这个。在使用 的方法中声明连接，而不是在Class级别。在类级别声明连接字符串，以便它可以在类中的任何方法中使用。

不要Sub在您的问题中包含空 s。和Timer日期标签与问题无关。不要包括这些方法。

Timer在设计时启用您的。为新连接创建一个局部变量Form.Load然后在它被使用之前让它超出范围是没有意义的。请记住，需要处理连接。

在btnShowPassword方法中（奇怪的是图片框，为什么？）您可以Boolean在一行中切换属性。

在Button1.Click使用OrElse中将If. 一旦找到 . 它就会停止计算表达式True。

现在到数据库代码。您永远不需要检查ConnectionState，因为它将在Using块中的方法中声明。连接和命令都包含在Using块中，因为命令也需要处理。在直接之前不要打开连接.Execute...

我很高兴看到Parameters. 我在CommandText. 问号可以正常工作，但更容易将参数与ParametersCollection您可以看到名称的时间联系起来。Access 只关注位置，但它更容易阅读。

vb 特定CInt已在 .net 中进行了优化。

第一个代码块应该如下所示。

Private ConStr As String = My.Settings.Business_ProfileConnectionString   '"Your connection string"

Private Sub btnShowPassword(sender As Object, e As EventArgs) Handles PictureBox3.Click
    txtBPPassword.UseSystemPasswordChar = Not txtBPPassword.UseSystemPasswordChar
End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    If txtBPID.Text = "" OrElse txtBPPassword.Text = "" Then
        MsgBox("Enter Credentials", MsgBoxStyle.Exclamation, "Error!")
    Else
        Dim count As Integer
        Using connection As New OleDbConnection(ConStr),
                cmd As New OleDbCommand("select count(*) from [Business Details] where Email=@1 and Password=@2", connection)
            cmd.Parameters.AddWithValue("@1", OleDbType.VarChar).Value = txtBPID.Text
            cmd.Parameters.AddWithValue("@2", OleDbType.VarChar).Value = txtBPPassword.Text
            connection.Open()
            count = CInt(cmd.ExecuteScalar())
        End Using
        If count = 0 Then
            MsgBox("Login succeed!", MsgBoxStyle.Information)
            frmWelcomeNew.Show()
            Me.Hide()
        Else
            MsgBox("Account not found. Check your credentials!", MsgBoxStyle.Critical, "Error!")
        End If
    End If
End Sub

在第二种形式中，我在btnNext.Click方法中划分代码。电话号码很少有破折号或分机号。但我不管它。我刚刚摆脱IsNumeric了vb6的剩余部分。

我很惊讶您没有使用您在第一种形式中使用的设置。这可能是你的问题。

您不需要将Text属性转换为String. 它已经是一个String.

Private ConStr As String = My.Settings.Business_ProfileConnectionString   '"Your connection string"

Private Sub btnUpload_Click(sender As Object, e As EventArgs) Handles btnUpload.Click
    Dim opf As New OpenFileDialog
    opf.Filter = "Choose Image(*.jpg;*.png)|* .jpg;*.png"
    If opf.ShowDialog = DialogResult.OK Then
        pcbBusinessPhoto.Image = Image.FromFile(opf.FileName)
    End If
End Sub

Private Sub btnNext_BizRegisPG1_Click(sender As Object, e As EventArgs) Handles btnNext_BizRegisPG1.Click
    If Not ValidateInput() Then
        Exit Sub
    End If
    Try
        InsertData()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
        Return
    End Try
    MsgBox("Record Saved")
    ClearForm()
    frmRegistration2.Show()
    Me.Hide()
End Sub

Private Function ValidateInput() As Boolean
    If txtSQAnswer.Text = "" OrElse cboSQ.Text = "" OrElse txtSSM.Text = "" OrElse txtBizAddress.Text = "" OrElse txtBizEmail.Text = "" OrElse txtBizName.Text = "" OrElse txtBizPhoneNo.Text = "" OrElse txtPassword.Text = "" OrElse txtPasswordReenter.Text = "" OrElse cboBizType.Text = "" Then
        MsgBox("Please fill in the textboxes!", MsgBoxStyle.Critical, "Error!")
        Return False
    End If
    If txtPasswordReenter.Text <> txtPassword.Text Then
        MsgBox("Your Reentered Password is not the same!", MsgBoxStyle.Critical, "Error!")
        Return False
    End If
    Dim Phone As Long
    If Not Long.TryParse(txtBizPhoneNo.Text, Phone) Then
        MsgBox("Enter numeric value only in Company Phone Number", MsgBoxStyle.Critical, "Error!")
        Return False
    End If
    If IsNothing(pcbBusinessPhoto.image) Then
        MsgBox("Please upload Business Photo", MsgBoxStyle.Critical, "Error!")
        Return False
    End If
    Return True
End Function

Private Sub InsertData()
    Using myconnection As New OleDbConnection(ConStr),
            cmd As New OleDbCommand("insert into [Business Details] values (@SSM,@BizName,@Address,@Email,@HP,@BizType,@Password,@BusinessPhoto,@OHFrom,@OHTo,@SQ1,SQ1Answer)", myconnection)
        cmd.Parameters.Add("@SSM", OleDbType.VarChar).Value = txtSSM.Text.ToUpper
        cmd.Parameters.Add("@BizName", OleDbType.VarChar).Value = txtBizName.Text
        cmd.Parameters.Add("@Address", OleDbType.VarChar).Value = txtBizAddress.Text
        cmd.Parameters.Add("@Email", OleDbType.VarChar).Value = txtBizEmail.Text
        cmd.Parameters.Add("@HP", OleDbType.VarChar).Value = txtBizPhoneNo.Text
        cmd.Parameters.Add("@BizType", OleDbType.VarChar).Value = cboBizType.Text
        cmd.Parameters.Add("@Password", OleDbType.VarChar).Value = txtPassword.Text
        cmd.Parameters.Add("@BusinessPhoto", OleDbType.LongVarBinary).Value = pcbBusinessPhoto.Image))
        cmd.Parameters.Add("@OHFrom", OleDbType.VarChar).Value = cboOHFrom.Text
        cmd.Parameters.Add("@OHTo", OleDbType.VarChar).Value = cboOHTo.Text)
        cmd.Parameters.Add("@SQ1", OleDbType.VarChar).Value = cboSQ.Text
        cmd.Parameters.Add("@DQ1Answer", OleDbType.VarChar).Value = txtSQAnswer.Text
        myconnection.Open()
        cmd.ExecuteNonQuery()
    End Using
End Sub

Private Sub ClearForm()
    txtSSM.Clear()
    txtBizAddress.Clear()
    txtBizEmail.Clear()
    txtBizName.Clear()
    txtBizPhoneNo.Clear()
    txtPassword.Clear()
    txtPasswordReenter.Clear()
    cboBizType.Text = ""
    cboOHFrom.Text = ""
    cboOHTo.Text = ""
    pcbBusinessPhoto.Image = Nothing
End Sub

对于第三种形式

Private ConStr As String = My.Settings.Business_ProfileConnectionString   '"Your connection string"

Private Sub btnSubmit_BizRegisPG2_Click(sender As Object, e As EventArgs) Handles btnSubmit_BizRegisPG2.Click
    If Not ValidateInput() Then
        Return
    End If
    Try
        InsertOwner()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
        Return
    End Try
    ClearForm()
    MsgBox("Record Saved")
    Me.Hide()
    frmLoginBP.Show()
End Sub

Private Function ValidateInput() As Boolean
    If txtIC.Text = "" OrElse txtFirstName.Text = "" OrElse txtLastName.Text = "" OrElse txtEmail.Text = "" OrElse txtHP.Text = "" OrElse dtpDOB.Text = "" Then
        MsgBox("Please fill in the textboxes!", MsgBoxStyle.Critical, "Error!")
        Return False
    End If
    Dim IC As Integer
    If Not Integer.Parse(txtIC.Text, IC) Then
        MsgBox("Please insert numerical value in Identity Card Number!", MsgBoxStyle.Critical, "Error!")
        Return False
    End If
    If pcbIC.image Is Nothing Then
        MsgBox("Please insert Owner Photo!", MsgBoxStyle.Critical, "Error!")
        Return False
    End If
    If rbtFemale.Checked = False And rbtMale.Checked = False Then
        MsgBox("Please select your gender!", MsgBoxStyle.Critical, "Error!")
        Return False
    End If
    If rbtAgree.Checked = False Then
        MsgBox("Please AGREE with Goodstopia Terms & Conditions!", MsgBoxStyle.Critical, "Error!")
        Return False
    End If
    Return True
End Function

Private Sub InsertOwner()
    Dim finalGender As String
    If rbtMale.Checked = True Then
        finalGender = "Male"
    Else
        finalGender = "Female"
    End If
    Dim Command = " insert into [Owner Details] values (@IC,@FirstName,@LastName,@Email,@HP,@DOB,@Gender,@ICPhoto)"
    Using myconnection As New OleDbConnection(ConStr),
            cmd As New OleDbCommand(Command, myconnection)
        cmd.Parameters.Add("@IC", OleDbType.VarChar).Value = UCase(txtIC.Text)
        cmd.Parameters.Add("@FirstName", OleDbType.VarChar).Value = txtFirstName.Text
        cmd.Parameters.Add("@LastName", OleDbType.VarChar).Value = txtLastName.Text
        cmd.Parameters.Add("@Email", OleDbType.VarChar).Value = txtEmail.Text
        cmd.Parameters.Add("@HP", OleDbType.VarChar).Value = txtHP.Text
        cmd.Parameters.Add("@DOB", OleDbType.VarChar).Value = dtpDOB.Text
        cmd.Parameters.Add("@Gender", OleDbType.VarChar).Value = finalGender
        cmd.Parameters.Add("@ICPhoto", OleDbType.LongVarBinary).Value = pcbIC.Image
        myconnection.Open()
        cmd.ExecuteNonQuery()
    End Using
End Sub

Private Sub ClearForm()
    txtIC.Clear()
    txtFirstName.Clear()
    txtLastName.Clear()
    txtHP.Clear()
    txtEmail.Clear()
    rbtFemale.Checked = False
    rbtMale.Checked = False
    pcbIC.Image = Nothing
End Sub

还有一件事。密码永远不应存储为纯文本。查找盐渍和散列。