EndEdit returns "Column 'No' does not allow nulls." error...

I have a table called "items" with a primary key called "no".

I cannot display the variable "no" in my form. I tried setting "visible = false" but that doesn't work.

I am using a form field called "nokey" instead.

Everything works fine until I try to insert a record. Here's a sample of the code:

Dim newItemsRow As SignsDataSet.itemsRow
newItemsRow = Me.SignsDataSet.items.NewitemsRow

If (Insert = True) Then
Dim MaxKey As Integer = 0

MaxKey = GetMax("Items")
MaxKey = MaxKey + 1
Me.NoKey = MaxKey.ToString
newItemsRow.No = Me.NoKey
newItemsRow.Nm_Latin = Me.Nm_LatinTextBox.Text
newItemsRow.Nm_Ang = Me.Nm_AngTextBox.Text
newItemsRow.Nm_Fr = Me.Nm_FrTextBox.Text

' Add the row to the Items table
Me.SignsDataSet.items.Rows.Add(newItemsRow)
Me.Validate()
Me.ItemsTableAdapter.Update(Me.SignsDataSet.items)
End If

The insert works but once I try to go to another record, I use EndEdit to check if the record was modified before allowing the move...

Private Sub BindingNavigatorMoveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorMoveFirstItem.Click, BindingNavigatorMovePreviousItem.Click, BindingNavigatorMoveNextItem.Click, BindingNavigatorMoveLastItem.Click

Dim response As DialogResult
Me.Position = Me.ItemsBindingSource.Position
Me.CancelBtn = False
Check_if_record_modified(response, "Next")
End Sub

Private Sub Check_if_record_modified(ByRef result As DialogResult, ByVal Action As String)
Me.ItemsBindingSource.EndEdit() ' <===== Generates the error

If (Action = "Next") Then
result = MessageBox.Show("Do you want to save before moving to another record ", "Warning: Data modified!", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button3)
End If

If (result = Windows.Forms.DialogResult.Yes) Then
SaveChanges("All")
InsertBtn = False
ElseIf (result = Windows.Forms.DialogResult.No) Then
Me.SignsDataSet.RejectChanges()
Me.ItemsBindingSource.CancelEdit()
ElseIf (result = Windows.Forms.DialogResult.Cancel) Then
Me.CancelBtn = True
InsertBtn = False
End If
End If
End Sub

Two things puzzle me beside the error message:
1) If I exit after the I have saved the inserted record, the new record appears and everything works fine

2) I have 1864 records in my table. When I push the insert button, I see in the Current Position of the BindingNavigator "1865". I ran the debugger step by step. Once all the steps are completed, the form appears and I get "1866" in the current position.

Once I exit and restart the application, I see I have 1865 records as it should.

Any help will be much appreciated!

Claude.

Answer this question

EndEdit returns "Column 'No' does not allow nulls." error...

blange
Ok, I have good and bad news:

Bad: I tried what you proposed but I get the same error when EndEdit executes.

It seems that the generated code cannot make the logical link between the variable I use
and the parameter it needs. I tried to bind it and I just can't figure it out.

Good: I got an idea and I feel stupid for not thinking about it before... I added to the form the primary key of my table and I am hiding it under the title of the form.

It's stupid but IT WORKS!!!

The reason I have to hide it is that if I set the field VISIBLE priority to False, it doesn't work.

Why has Microsoft come up with the idea that a Visible = False priority means there is no value for the field

(Try it, set Visible=False to a field and in Debug, you will see the field has no value.)

Anyway, adding and hiding my Primary Key fixes my problem but it sure is not the solution I was expecting.

Kind of feel like I am hiding the dust under the carpet...

Claude.
giftgirls

Sorry for the worthless reply earlier

I guessed at what the GetMax() function did and guessed wrong. Your method of getting the next ID should be fine. Sorry.

And you're right about having to call EndEdit() before the changes appear in the dataset - so that's ok too.

I'd have to guess that the problem lies in the fact that you're adding the new record to the DataTable directly - you should use the BindingSource instead. Once a datasource is bound to a BindingSource it is no longer valid to work with the data in the datasource directly. All changes should be done through the BindingSource.

So while I'm not sure exactly what is failing, I'm pretty sure that modifing your code to do all additions and edits through the BindingSource will fix it.
Dana Lutz
Thanks RKimble,

Sorry, I removed too much code in order to have a smaller post.

Here's how I check if the record has been changed:

Private Sub Check_if_record_modified(ByRef result As DialogResult, ByVal Action As String)
Me.ItemsBindingSource.EndEdit()

' Check if Item have changed
If (Me.SignsDataSet.HasChanges = True) then
' I ask the user if he want to save or not
End If
End Sub

I am using "EndEdit" before the IF since when I don't, the status of "Me.SignsDataSet.HasChanges" or "Me.SignsDataSet.items.Rows(Me.ItemsBindingSource.Position).RowState" shows it was not modified when the record was.

About your remark on a possible problem if a record was deleted, I am not sure I understand. I look for the biggest(max) value of the primary key then add 1. I tried it and it works fine.

I have no problem not using EndEdit if there is a way RowState will work without it.
I wonder why it doesn't work when Update works...

Again, thanks for the reply.
suranga_d

I'm not sure how you're trying to check for modifications to the current record... Calling EndEdit() just updates the underlying datasource with the values in the bound controls. If the control bound to "No" field contains "Nothing" then the current record is trying to have its primary key set to Null. You should check the RowState of the current Row to find out if it has any changes.

Also, your method of generating the primary key will fail if the user deletes an entry from the list... If there are 10 items in the list and the user deletes item 7, the code will generate a duplicate item 10 key.
EndEdit returns "Column 'No' does not allow nulls." error...

Answer this question