Visual studio: ERROR: Parameter ?_1 has no default value

Question

I'm having problems with the piece of code that i'm working on, i've searched around and can't seem to find any thing that can help me!

Public Class CustomerController

    Public Const CONNECTION_STRING As String = _
      "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=assignment.accdb"

    Public Sub insert(ByVal htData As Hashtable)

        Dim oConnection As OleDbConnection = New OleDbConnection(CONNECTION_STRING)

        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.VarChar, 255)
            oCommand.Parameters.Add("address", OleDbType.VarChar, 255)
            oCommand.Parameters.Add("email", OleDbType.VarChar, 255)
            oCommand.Parameters.Add("dob", OleDbType.VarChar, 255)

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

            oCommand.Prepare()

            Debug.Print("SQL: " & oCommand.CommandText)

            oCommand.ExecuteNonQuery()

            Debug.Print("The record was inserted.")

            'If an error has occurred, this will show an error message to inform user that the record was not inserted
        Catch ex As Exception
            Debug.Print("ERROR: " & ex.Message)
            MsgBox("An error occurred. The record wasn't inserted.")
        Finally
            oConnection.Close()
        End Try

    End Sub
End Class

When I run oCommand.ExecuteNonQuery() I get

"ERROR: Parameter ?_1 has no default value"

Answer 1

You can try this:

    oCommand.Parameters("title").Value = IIf(CStr(htData("title")) = "", DBNull.Value, CStr(htData("title")))
    oCommand.Parameters("gender").Value = IIf(CStr(htData("gender")) = "", DBNull.Value, CStr(htData("gender")))
    oCommand.Parameters("firstname").Value = IIf(CStr(htData("firstname")) = "", DBNull.Value, CStr(htData("firstname")))
    oCommand.Parameters("lastname").Value = IIf(CStr(htData("lastname")) = "", DBNull.Value, CStr(htData("lastname")))
    oCommand.Parameters("phone").Value = IIf(CStr(htData("phone")) = "", DBNull.Value, CStr(htData("phone")))
    oCommand.Parameters("address").Value = IIf(CStr(htData("address")) = "", DBNull.Value, CStr(htData("address")))
    oCommand.Parameters("email").Value = IIf(CStr(htData("email")) = "", DBNull.Value, CStr(htData("email")))
    oCommand.Parameters("dob").Value = IIf(CStr(htData("dob")) = "", DBNull.Value, CStr(htData("dob")))

Answer 2

Try this. If this doesn't work, there might be a column in your database that requires a value but none was supplied or it may be missing from the query. Also, it's good to employ Using blocks to automatically dispose of objects such as connections and commands.

Public Const CONNECTION_STRING As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=assignment.accdb"

Public Sub insert(htData As Hashtable)
    Try
        Using cn = New OleDbConnection(CONNECTION_STRING)
            cn.Open()
            Using cmd = New OleDbCommand("INSERT INTO Customer (title, gender, firstname, lastname, phone, address, email, dob) VALUES (?,?,?,?,?,?,?,?)", cn)
                With cmd.Parameters
                    .AddWithValue("title", htData("title"))
                    .AddWithValue("gender", htData("gender"))
                    .AddWithValue("firstname", htData("firstname"))
                    .AddWithValue("lastname", htData("lastname"))
                    .AddWithValue("phone", htData("phone"))
                    .AddWithValue("address", htData("address"))
                    .AddWithValue("email", htData("email"))
                    .AddWithValue("dob", htData("dob"))
                End With

                cmd.ExecuteNonQuery()
            End Using
        End Using

        Debug.Print("The record was inserted.")

        'If an error has occurred, this will show an error message to inform user that the record was not inserted
    Catch ex As Exception
        Debug.Print("ERROR: " & ex.Message)
        MsgBox("An error occurred. The record wasn't inserted.")
    End Try
End Sub