如何用VB.NET将注册表单连接Access数据库并显示到DataGridView及用户档案

存数无报错但未写入的核心排查点 #

• 未提交事务：若手动开启了事务但未调用Commit()，数据不会持久化
• 连接路径错误：相对路径失效导致程序读写的是错误的数据库文件，或目标目录无写入权限
• SQL参数问题：OleDb参数按顺序匹配而非参数名，参数数量/类型不匹配会导致静默失败
• 数据库被覆盖：Access文件设为"始终复制到输出目录"，每次运行都会覆盖输出目录的数据库，导致之前的数据丢失
• 未执行ExecuteNonQuery()：误用ExecuteReader()或未触发命令执行

修正后的注册数据插入代码 #

Imports System.Data.OleDb

Public Class RegisterForm
    ' 连接字符串：替换为你的数据库实际路径
    Private ReadOnly connString As String = 
        $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={Application.StartupPath}\UserDB.accdb;Persist Security Info=False;"

    Private Sub btnRegister_Click(sender As Object, e As EventArgs) Handles btnRegister.Click
        ' 先做基础输入验证
        If String.IsNullOrWhiteSpace(txtUsername.Text) Or String.IsNullOrWhiteSpace(txtPwd.Text) Then
            MessageBox.Show("用户名和密码不能为空")
            Return
        End If

        ' 使用Using自动释放连接/命令资源，避免内存泄漏
        Using conn As New OleDbConnection(connString)
            Try
                conn.Open()
                ' 参数化SQL：防注入+避免字段值匹配错误
                Dim insertSql As String = 
                    "INSERT INTO Users (Username, Password, Email, FullName) VALUES (?, ?, ?, ?)"
                Using cmd As New OleDbCommand(insertSql, conn)
                    ' OleDb按参数顺序绑定，注意顺序要和SQL中的占位符一致
                    cmd.Parameters.AddWithValue("@Username", txtUsername.Text)
                    cmd.Parameters.AddWithValue("@Password", txtPwd.Text) ' 生产环境务必加密密码
                    cmd.Parameters.AddWithValue("@Email", txtEmail.Text)
                    cmd.Parameters.AddWithValue("@FullName", txtFullName.Text)

                    ' 执行插入并检查影响行数
                    Dim affectedRows = cmd.ExecuteNonQuery()
                    If affectedRows > 0 Then
                        MessageBox.Show("注册成功")
                        ' 插入后立即刷新数据展示
                        LoadUsersToDataGridView()
                        ShowUserProfile(txtUsername.Text)
                    Else
                        MessageBox.Show("数据未写入，请检查字段名和参数")
                    End If
                End Using
            Catch ex As Exception
                MessageBox.Show($"错误：{ex.Message}")
            End Try
        End Using
    End Sub

加载数据到DataGridView #

Private Sub LoadUsersToDataGridView()
    Using conn As New OleDbConnection(connString)
        Try
            conn.Open()
            Dim querySql As String = "SELECT UserID, Username, Email, FullName FROM Users"
            Using adapter As New OleDbDataAdapter(querySql, conn)
                Dim userTable As New DataTable()
                adapter.Fill(userTable)
                dgvUserList.DataSource = userTable

                ' 可选：设置列名显示
                dgvUserList.Columns("UserID").HeaderText = "用户ID"
                dgvUserList.Columns("Username").HeaderText = "用户名"
                dgvUserList.Columns("Email").HeaderText = "邮箱"
            End Using
        Catch ex As Exception
            MessageBox.Show($"加载数据失败：{ex.Message}")
        End Try
    End Using
End Sub

展示用户档案（点击DataGridView触发） #

Private Sub dgvUserList_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgvUserList.CellClick
    ' 排除点击表头的情况
    If e.RowIndex < 0 Then Return

    Dim selectedRow = dgvUserList.Rows(e.RowIndex)
    ' 将选中行数据填充到档案控件
    lblProfileID.Text = selectedRow.Cells("UserID").Value.ToString()
    txtProfileUsername.Text = selectedRow.Cells("Username").Value.ToString()
    txtProfileEmail.Text = selectedRow.Cells("Email").Value.ToString()
    txtProfileFullName.Text = selectedRow.Cells("FullName").Value.ToString()
End Sub

' 也可根据用户名直接查询档案
Private Sub ShowUserProfile(username As String)
    Using conn As New OleDbConnection(connString)
        Try
            conn.Open()
            Dim profileSql As String = "SELECT * FROM Users WHERE Username = ?"
            Using cmd As New OleDbCommand(profileSql, conn)
                cmd.Parameters.AddWithValue("@Username", username)
                Using reader = cmd.ExecuteReader()
                    If reader.Read() Then
                        lblProfileID.Text = reader("UserID").ToString()
                        txtProfileUsername.Text = reader("Username").ToString()
                        txtProfileEmail.Text = reader("Email").ToString()
                        txtProfileFullName.Text = reader("FullName").ToString()
                    End If
                End Using
            End Using
        Catch ex As Exception
            MessageBox.Show($"加载档案失败：{ex.Message}")
        End Try
    End Using
End Sub

必做的验证步骤 #

1. 确认数据库路径：用MessageBox.Show(Application.StartupPath)查看程序运行目录，检查该目录下是否有数据库文件，是否能手动写入
2. 调整文件复制设置：右键项目中的Access文件→属性→复制到输出目录，改为如果较新则复制，避免每次运行覆盖数据库
3. 测试SQL语句：把生成的SQL语句和参数值打印出来，直接在Access查询中执行，确认是否能插入数据
4. 检查权限：如果数据库放在C:\Program Files等受保护目录，右键程序→以管理员身份运行测试

内容的提问来源于stack exchange，提问作者wnnlyn