VB.NET向Access插入数据时出现数据类型不匹配错误求助

排查与修复步骤 #

1. 检查字段名拼写 #

你的SQL语句中使用了Delevery_date，务必确认Access表中对应的字段名是否为正确拼写的Delivery_date——字段名拼写错误会导致数据库无法匹配对应字段，进而触发类型不匹配错误，这是高频踩坑点。

2. 显式指定参数的数据类型 #

OleDb参数化查询中，显式声明DbType可避免自动类型转换的歧义，尤其是日期类型：

' 修正后的参数定义，显式指定DbType
Dim parameters As OleDbParameter() = {
    New OleDbParameter("?", OleDbType.Integer) With {
        .Value = If(String.IsNullOrEmpty(TextBox1.Text), DBNull.Value, Integer.Parse(TextBox1.Text))
    },
    New OleDbParameter("?", OleDbType.Date) With {
        .Value = DateTimePicker2.Value.Date ' 仅传入日期部分，适配Access短日期字段
    },
    New OleDbParameter("?", OleDbType.VarChar) With {.Value = TextBox2.Text},
    New OleDbParameter("?", OleDbType.VarChar) With {.Value = TextBox3.Text},
    New OleDbParameter("?", OleDbType.Date) With {
        .Value = DateTimePicker1.Value.Date
    },
    New OleDbParameter("?", OleDbType.Integer) With {
        .Value = If(String.IsNullOrEmpty(TextBox4.Text), DBNull.Value, Integer.Parse(TextBox4.Text))
    }
}

3. 验证DateTimePicker的日期有效性 #

虽然DateTimePicker默认不会返回无效日期，但可添加防御性检查避免异常：

' 示例：检查日期是否有效
If Not DateTime.TryParse(DateTimePicker1.Value.ToString(), Nothing) Then
    MessageBox.Show("请选择有效的交付日期")
    Return
End If

4. 确认Access表字段类型 #

• SO_date和Delivery_date需设置为日期/时间类型，不要选择“短文本”或其他类型；
• So_no和order_value确认是**数字（整型）**类型：VB.NET的Integer对应Access的“长整型”，若字段是“短整型”，则需改用Short.Parse。

完整修正后的Button_Click代码 #

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    GroupBox1.Visible = True
    DataGridView1.Visible = True

    Try
        ' 修正字段名拼写为Delivery_date（根据实际表结构调整）
        Dim query As String = "INSERT INTO sales_order_header (So_no, SO_date, Customer, Project_name, Delivery_date, order_value) " & _
                              "VALUES (?, ?, ?, ?, ?, ?)"

        ' 显式指定参数类型的参数数组
        Dim parameters As OleDbParameter() = {
            New OleDbParameter("?", OleDbType.Integer) With {
                .Value = If(String.IsNullOrEmpty(TextBox1.Text), DBNull.Value, Integer.Parse(TextBox1.Text))
            },
            New OleDbParameter("?", OleDbType.Date) With {
                .Value = DateTimePicker2.Value.Date
            },
            New OleDbParameter("?", OleDbType.VarChar) With {.Value = TextBox2.Text},
            New OleDbParameter("?", OleDbType.VarChar) With {.Value = TextBox3.Text},
            New OleDbParameter("?", OleDbType.Date) With {
                .Value = DateTimePicker1.Value.Date
            },
            New OleDbParameter("?", OleDbType.Integer) With {
                .Value = If(String.IsNullOrEmpty(TextBox4.Text), DBNull.Value, Integer.Parse(TextBox4.Text))
            }
        }

        Database.ExecuteQueryWithParameters(query, parameters)

        MessageBox.Show("Order Created, add Items")
        TextBox1.ReadOnly = True
        TextBox2.ReadOnly = True
        TextBox3.ReadOnly = True
        TextBox4.ReadOnly = True
    Catch ex As Exception
        MessageBox.Show("An error occurred: " & ex.Message)
        ResetForm()
    End Try
End Sub

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