如何解决VB.NET中INSERT语句的语法错误？

问题根源 #

你的INSERT语句报错主要由以下几处问题导致：

• INSERT语句的Values子句末尾缺少闭合括号
• 含空格的字段名（如Date of birth）未用方括号包裹，Access会识别为语法错误
• 使用了Access保留字Password，未正确添加方括号转义
• 执行INSERT操作错误使用ExecuteReader()（该方法用于查询返回结果集），应改用ExecuteNonQuery()
• 性别参数类型不匹配（Boolean变量却用OleDbType.VarChar）
• 输入验证逻辑存在错误判断

修正后的完整代码 #

Imports System.Data.OleDb
Imports System.IO

Public Class Form2
    ' 建议后续将连接字符串移到配置文件，避免硬编码绝对路径
    Dim connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Dimph\OneDrive\Desktop\Richfield BSC IT --- 1st year\Second Semester\Programming 512\Assignment\WinFormsApp1\WinFormsApp1\bin\Debug\net6.0-windows\Database2.accdb")

    Private Sub btnUplaodImage(sender As Object, e As EventArgs) Handles Button6.Click
        If Not String.IsNullOrEmpty(OpenFileDialog1.FileName) Then
            Dim fileName As String = Path.GetFileName(OpenFileDialog1.FileName)
            ' 此处可添加实际文件复制逻辑，示例：
            ' File.Copy(OpenFileDialog1.FileName, Path.Combine(Application.StartupPath, "Upload", fileName))
            MessageBox.Show("File Uploaded")
        End If
    End Sub

    Private Sub btnBrowserImage(sender As Object, e As EventArgs) Handles Button5.Click
        Dim result As DialogResult = OpenFileDialog1.ShowDialog()
        If result = DialogResult.OK Then
            TextBox5.Text = OpenFileDialog1.FileName
        End If
    End Sub

    Private Sub btnSubmit(sender As Object, e As EventArgs) Handles Button3.Click
        ' 优化输入验证逻辑
        If String.IsNullOrEmpty(txtName.Text) OrElse String.IsNullOrEmpty(txtLastName.Text) _
            OrElse String.IsNullOrEmpty(txtPassword.Text) OrElse String.IsNullOrEmpty(txtUsename.Text) _
            OrElse Not (rdFemale.Checked Or rdMale.Checked) Then
            MsgBox("请填写所有必填字段！", MsgBoxStyle.Critical)
            Return
        End If

        Dim Gender As Boolean = rdFemale.Checked ' 简化性别判断逻辑

        Try
            If connection.State = ConnectionState.Closed Then
                connection.Open()
            End If

            ' 修正INSERT语句：补全括号、转义特殊字段名
            Dim cmd As New OleDbCommand("INSERT INTO [Sign_Up] ([Name], [Lastname], [Date of birth], [Gender], [Password], [UserName]) " & _
                                       "VALUES (@Name, @LastName, @DateOfBirth, @Gender, @Password, @Username)", connection)

            ' 修正参数类型，确保与数据库字段匹配
            cmd.Parameters.Add("@Name", OleDbType.VarChar).Value = txtName.Text
            cmd.Parameters.Add("@LastName", OleDbType.VarChar).Value = txtLastName.Text
            cmd.Parameters.Add("@DateOfBirth", OleDbType.DBDate).Value = DateTimePicker1.Value.Date ' 仅传递日期部分
            cmd.Parameters.Add("@Gender", OleDbType.Boolean).Value = Gender
            cmd.Parameters.Add("@Password", OleDbType.VarChar).Value = txtPassword.Text
            cmd.Parameters.Add("@Username", OleDbType.VarChar).Value = txtUsename.Text

            ' 用ExecuteNonQuery执行INSERT操作，返回受影响行数
            Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
            If rowsAffected > 0 Then
                MsgBox("账户创建成功", MsgBoxStyle.Information)
                ' 可选：清空输入框
                txtName.Clear()
                txtLastName.Clear()
                txtPassword.Clear()
                txtUsename.Clear()
                rdFemale.Checked = False
                rdMale.Checked = False
                DateTimePicker1.Value = DateTime.Now
            Else
                MsgBox("账户创建失败", MsgBoxStyle.Exclamation)
            End If
        Catch ex As Exception
            MsgBox("错误信息：" & ex.Message, MsgBoxStyle.Critical)
        Finally
            ' 确保连接无论是否出错都会关闭
            If connection.State = ConnectionState.Open Then
                connection.Close()
            End If
        End Try
    End Sub
End Class

核心修正说明 #

1. SQL语句语法修复：补全Values子句的闭合括号，给含空格或保留字的字段（Date of birth、Password）添加方括号，避免Access语法解析错误。
2. 执行方法修正：使用ExecuteNonQuery()执行INSERT操作，该方法专为无结果集的SQL操作设计，返回受影响的行数，便于判断操作是否成功。
3. 参数类型匹配：性别参数改为OleDbType.Boolean，与变量类型一致，避免不必要的类型转换错误。
4. 输入验证优化：用String.IsNullOrEmpty()判断文本框空值，用Not (rdFemale.Checked Or rdMale.Checked)判断单选按钮未选择，逻辑更严谨。
5. 异常处理：添加Try-Catch-Finally块，确保数据库连接在出错时也能正常关闭，同时捕获错误信息便于调试。

内容的提问来源于stack exchange，提问作者Dimpho Pdols Ramokhoase