CODEWITHSUNDEEP
vb.netoledboledbexception
Alastair McIntyre
Alastair McIntyre

Reputation: 11

Unhandled exception of type 'System.Data.OleDb.OleDbException' occured in System.Data.dll

I'm getting an error in this code, designed to create records in an Access Database, but can't seem to work out why.

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

After commenting out the error message to try and debug the error, I've found the error occurs here "https://i.sstatic.net/fQgBJ.png" When this occurs in Debub mode, the application crashes, and does not create a record in the linked database

Upvotes: 1

Views: 822

Answers (1)

David
David

Reputation: 219037

Read the error message:

Parameter ?_8 has no default value.

Your query has 8 parameter placeholders:

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

Then you add 8 parameters:

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)

Then... you set 7 values:

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"))

You need to set a value for the 8th parameter ("dob").

(Side note: Phone and DOB probably shouldn't be integers since they're not integer values. Phone is a text value, DOB is a date value.)

Upvotes: 1

Related Questions