'Column 'CustID' does not allow nulls.' error when trying to write to access dat-CodePudding

I'm trying to write to my database from textboxes in VB form. But when I click the 'add'button, this error is given under the `CustomersBindingSource.EndEdit() line even though all my textboxes are filled in.

Using connection As New OleDbConnection(builder.ConnectionString)

        connection.Open()

        Dim str As String
        str = "Insert into Items([InvoiceID]. [Item],[Cost],[Quantity]) Values (?,?,?,?,?,?)"
        Dim cmd As OleDbCommand = New OleDbCommand(str, connection)
        cmd.Parameters.Add(New OleDbParameter("CustID", CType(CustIDTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("FirstName", CType(First_NameTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("LastName", CType(Last_NameTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Address", CType(AddressTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("PhoneNo", CType(Phone_noTextBox.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Points", CType(PointsTextBox.Text, String)))

        CustomersBindingSource.AddNew()
        CustomersBindingSource.EndEdit()
        CustomersTableAdapter.Update(POS_database_latestDataSet.Customers)

 End Using

I don't understand what I'm doing wrong.

CodePudding user response：

Just as the error states, that column does not allow NULL values. And you're not inserting a value into it. You're inserting into these columns:

([InvoiceID]. [Item],[Cost],[Quantity])

Which (1) looks like a typo between the first two columns and (2) doesn't even resemble the values you're trying to insert.

Specify the columns that you want to insert the values into:

([CustID], [FirstName], [LastName], [Address], [PhoneNo], [Points])

CodePudding user response：

Since the sql string and the parameters didn't match I picked the parameters and changed the sql string. I had to guess the table name and field names in the database. Please check for the correct names. If a field is an auto-number field (usually found as the primary key, and ID field) you do not insert a value for that field. The database will insert the value for you.

Both connections and commands need to be disposed, so include the command in the Using block by putting a comma after the first line and removing the Dim.

Don't open the connection until just before the command is executed.

The Text property of a TextBox is a String. You do not need the convert the type.

I used a With...End With to save a little clutter.

In OleDb the order that the parameters appear in the sql string must match the order they are added to the parameters collection. The Add method will create a new Parameter object and add it to the parameters collection. I had to guess at the datatypes. Check your database.

You wrote all that lovely command code and then you completely ignored it. Executing the command will update the database.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim str = "Insert into Customers(FirsName, LastName, Address, PhoneNo, Points) Values (@FirstName, @LastName, @Address, @PhoneNo, @Points);"
    Using connection As New OleDbConnection(builder.ConnectionString),
            cmd As OleDbCommand = New OleDbCommand(str, connection)
        With cmd.Parameters
            .Add("@FirstName", OleDbType.VarWChar).Value = First_NameTextBox.Text
            .Add("@LastName", OleDbType.VarWChar).Value = Last_NameTextBox.Text
            .Add("@Address", OleDbType.VarWChar).Value = AddressTextBox.Text
            .Add("@PhoneNo", OleDbType.VarWChar).Value = Phone_noTextBox.Text
            .Add("@Points", OleDbType.Integer).Value = CInt(PointsTextBox.Text)
        End With
        connection.Open()
        cmd.ExecuteNonQuery()
    End Using
End Sub