系统数据中发生'System.Data.OleDb.OleDbException'类型的未处理异常.dll

Question

我在此代码中收到错误，旨在在 Access 数据库中创建记录，但似乎无法弄清楚原因。

Option Explicit On
Option Strict On
Imports System.Data.OleDb
'Name:          CustomerController.vb
'Description:   Class acting as intermediary between the Customer Form and Customer table
'               Contains Most of the CRUD business Logic
'Author:        Alastair McIntyre
'Date:          12/04/2015
Public Class CustomerController
    Public Const CONNECTION_STRING As String = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=assignment 1.accdb"
    Public Function insertCustomer(ByVal htCustomer As Hashtable) As Integer
        Dim oConnection As OleDbConnection = New OleDbConnection(CONNECTION_STRING)
        Dim iNumRows As Integer
        Try
            Debug.Print("Connection string: " & oConnection.ConnectionString)
            oConnection.Open()
            Dim oCommand As OleDbCommand = New OleDbCommand
            oCommand.Connection = oConnection
            oCommand.CommandText = _
                "INSERT INTO customer (title, gender, firstname, lastname, phone, address, email, dob) VALUES (?, ?, ?, ?, ?, ?, ?, ?);"
            oCommand.Parameters.Add("title", OleDbType.VarChar, 255)
            oCommand.Parameters.Add("gender", OleDbType.VarChar, 255)
            oCommand.Parameters.Add("firstname", OleDbType.VarChar, 255)
            oCommand.Parameters.Add("lastname", OleDbType.VarChar, 255)
            oCommand.Parameters.Add("phone", OleDbType.Integer, 11)
            oCommand.Parameters.Add("address", OleDbType.VarChar, 255)
            oCommand.Parameters.Add("email", OleDbType.VarChar, 255)
            oCommand.Parameters.Add("dob", OleDbType.Integer, 8)
            oCommand.Parameters("title").Value = CStr(htCustomer("title"))
            oCommand.Parameters("gender").Value = CStr(htCustomer("gender"))
            oCommand.Parameters("firstname").Value = CStr(htCustomer("firstname"))
            oCommand.Parameters("lastname").Value = CStr(htCustomer("lastname"))
            oCommand.Parameters("phone").Value = CInt(htCustomer("phone"))
            oCommand.Parameters("address").Value = CStr(htCustomer("address"))
            oCommand.Parameters("email").Value = CStr(htCustomer("email"))
            oCommand.Prepare()
            iNumRows = oCommand.ExecuteNonQuery()
            Debug.Print(CStr(iNumRows))
            Debug.Print("The record was insterted")
            'Catch ex As Exception
            'Debug.Print("Error: " & ex.Message)
            'MsgBox("An error occured. The record wasn't inserted")
        Finally
            oConnection.Close()
        End Try
        Return iNumRows
    End Function
End Class

注释掉错误消息以尝试调试错误后，我发现错误发生在这里"https://i.stack.imgur.com/fQgBJ.png" 在 Debub 模式下发生这种情况时，应用程序崩溃，并且不会在链接数据库中创建记录

Answer 1

阅读错误消息：

参数 ？_8 没有默认值。

查询有 8 个参数占位符：

oCommand.CommandText = _
            "INSERT INTO customer (title, gender, firstname, lastname, phone, address, email, dob) VALUES (?, ?, ?, ?, ?, ?, ?, ?);"

然后添加 8 个参数：

oCommand.Parameters.Add("title", OleDbType.VarChar, 255)
oCommand.Parameters.Add("gender", OleDbType.VarChar, 255) 
oCommand.Parameters.Add("firstname", OleDbType.VarChar, 255)
oCommand.Parameters.Add("lastname", OleDbType.VarChar, 255)
oCommand.Parameters.Add("phone", OleDbType.Integer, 11)
oCommand.Parameters.Add("address", OleDbType.VarChar, 255)
oCommand.Parameters.Add("email", OleDbType.VarChar, 255)
oCommand.Parameters.Add("dob", OleDbType.Integer, 8)

然后。。。您设置 7 个值：

oCommand.Parameters("title").Value = CStr(htCustomer("title"))
oCommand.Parameters("gender").Value = CStr(htCustomer("gender"))
oCommand.Parameters("firstname").Value = CStr(htCustomer("firstname"))
oCommand.Parameters("lastname").Value = CStr(htCustomer("lastname"))
oCommand.Parameters("phone").Value = CInt(htCustomer("phone"))
oCommand.Parameters("address").Value = CStr(htCustomer("address"))
oCommand.Parameters("email").Value = CStr(htCustomer("email"))

您需要为第 8 个参数（"dob"）设置一个值。

（旁注：电话和 DOB 可能不应该是整数，因为它们不是整数值。 电话是文本值，DOB 是日期值。