如何通过SQL将读取的内容读取并移动到VB.net中的变量中，并在表单中使用来自模块的连接字符串

Question

顺便说一句，这是我的第一个问题，我不知道该怎么问，也不知道该说哪里出了问题。有三件事我无法处理，所以如果有任何帮助，我们将不胜感激。模块：这个表单和第一个（登录）表单可以正常工作，但我无法在不重复使用"中包含的字符串的情况下让任何一个表单引用con.connection（如下所述）-我的尝试最终都出现了错误，包括说由于连接已打开，无法更改状态，但我希望从表单中引用同一个字符串。

Module ConnectionModule
Public con As OleDb.OleDbConnection = New OleDb.OleDbConnection
Public da As OleDb.OleDbDataAdapter
Public ds As DataSet = New DataSet
Public Path As String = Application.StartupPath
Public Sub OpenDb()
    con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=F:Computing A2CG4 CourseworkGreener CleaningdbCoursework.accdb"
    con.Open()
    If con.State = ConnectionState.Closed Then
        MsgBox("Connection to db not made.")
    End If
End Sub
Public CurrentUser As String = Nothing
End Module

第一种形式：

    Public Class LoginForm

Private Sub LoginForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    OpenDb()
    con.Close()
End Sub
Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
    Dim ID As String = txtID.Text
    Dim Pass As String = txtPassword.Text
    If IsNumeric(ID) = False Or ID.Length > 4 Or Pass = Nothing Then
        MsgBox("Staff ID is a 4-digit number and Password must not be blank.")
    Else
        Dim con As New System.Data.OleDb.OleDbConnection()
        OpenDb()
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=F:Computing A2CG4 CourseworkGreener CleaningdbCoursework.accdb"
        Try
            Dim sql As String = "SELECT * FROM tblStaff WHERE [StaffID]='" & ID & "' AND [Pword] = '" & Pass & "'"
            Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql)
            'Open Database Connection
            sqlCom.Connection = con
            con.Open()
            Dim sqlRead As System.Data.OleDb.OleDbDataReader = sqlCom.ExecuteReader()
            If sqlRead.Read() Then 'Correct:
                MenuForm.Show()
                Me.Hide()
                CurrentUser = ID
            Else 'Incorrect:
                MsgBox("Staff ID or Password incorrect.")
                txtPassword.Text = ""
                txtID.Text = ""
                txtID.Focus()
            End If
        Catch ex As Exception
            MsgBox("Database Connection Error.")
        End Try
        con.Close()
    End If
End Sub
End Class

更改密码的表单：这里的问题是lblUser（用户告诉他们哪个密码将被更改的说明）只将程序中已经存在的数据作为变量输出：CurrentUser（在成功登录时分配）。没有产生任何错误，但没有显示用户的全名（或可能从数据库中读取）。我也不确定UPDATE SQL命令应该如何包含在第二个过程btnAccept_click中。语法基本上是什么。我还没有找到一个明确的例子。

Imports System.Data.OleDb
Public Class PasswordForm
Private Sub PasswordForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    con = New System.Data.OleDb.OleDbConnection()
    con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=F:Computing A2CG4 CourseworkGreener CleaningdbCoursework.accdb"
    Dim Returned(2) As String
    CurrentUser = CurrentUser
    Dim cmd As OleDbCommand = New OleDbCommand("SELECT [Title], [Forename], [Surname] FROM tblStaff WHERE [StaffID]='" & CurrentUser & "'", con)
    Try
        con.Open()
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        If reader.HasRows Then
            reader.Read()
            'Makes db contents variables            
            Returned(0) = reader.Item("[Title]").ToString
            Returned(1) = reader.Item("[Forename]").ToString
            Returned(2) = reader.Item("[Surname]").ToString
        End If
        reader.Close()
    Catch ex As Exception
        Me.Hide()
        MsgBox("Database Connection Error.")
    Finally
        con.Close()
    End Try
    lblUser.Text = "Current User: " & CurrentUser & Returned(0) & Returned(1) & Returned(2)
    ''Only outputs CurrentUser
End Sub
Private Sub btnAccept_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAccept.Click
    Dim Current As String = txtCurrent.text
    Dim NewPass As String = txtNew.Text
    'Verification
    If txtNew.Text = txtConfirm.Text And NewPass.Length <= 20 Then
        Dim cmd As OleDbCommand = New OleDbCommand("UPDATE tblStaff SET [Pword]='" & NewPass & "' WHERE [StaffID]='" & CurrentUser & "'", con)

    End If
End Sub
End Class

再次感谢所有有想法的人（尤其是确切的代码）。哦，在这里，没有出现任何错误。只是缺少内容。

Answer 1

如果您在openDB（）中打开连接，并试图在form1中再次打开它，这将引发您得到的错误。因此，请对表单中所有与con相关的行进行注释。对passowrd表单也进行相同的注释。

'Dim con As New System.Data.OleDb.OleDbConnection()
OpenDb()
'con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=F:Computing A2CG4 CourseworkGreener CleaningdbCoursework.accdb"
 Try
        Dim sql As String = "SELECT * FROM tblStaff WHERE [StaffID]='" & ID & "' AND [Pword] = '" & Pass & "'"
        Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql)
        'Open Database Connection
        sqlCom.Connection = con
        'con.Open()
...
end try