Adding rows to Northwind DataGridView - NoNullAllowedException

I create a simple Window Forms project, drag the Employees table from Data Source to the form to get a DataGridView and a BindingNavigator.

On running the project, I click the '+' on the Binding Navigator to create a new row, then click the "Save Data" button, and it crashes with "NoNullAllowedException".

So, how do I prevent this?

I thought it would be easy, but I just spent all day trying different things with no luck.NOrthwind
I'll add, let's say one of the columns is a checkbox, and another column is an integer.
deleydAsked:
Who is Participating?
 
deleydConnect With a Mentor Author Commented:
I finally found a working solution. Took a lot of research to find this.
 
1. For a DataGridView the BindingSource is a IBindingList.

2. The sender is our BindingSource.

3. The BindingSource is actually tied to a DataView, which has DataViewRows, so we need to create a DataViewRow of the proper type.

4. Sender.List.AddNew() returns a DataViewRow

5. Then drNew = drvNew.Row() extracts the actual DataRow (which in my case is of type ccdbaseDataSet1.CFacilityRow)

6. Then I can go ahead and set default values for the row, and return the row by setting e.NewObject

    Private Sub CFacilityBindingSource_AddingNew(sender As System.Object, e As System.ComponentModel.AddingNewEventArgs) Handles CFacilityBindingSource.AddingNew
        Dim drvNew As DataRowView
        Dim drNew As ccdbaseDataSet1.CFacilityRow

        drvNew = sender.List.AddNew()

        e.NewObject = drvNew          ' return this DataRowView as our new object

        drNew = drvNew.Row()          ' fetch the actual DataRow

        drNew.Baudrate = 2400         ' Set some default values
        drNew.LegacyCommands = False  ' Set some default values
        drNew.Active = True           ' Set some default values

        ' set a default key value (integer)
        ' loop through all current key values, find the largest value, make largest value + 1 our new SiteID
        ' Note that our new row will show up in sender.List and SiteID will be DBNull, so we first set
        ' a dummy value of 0 for the SiteID (otherwise we'd bomb when we called dr.SiteID to get the SiteID).
        drNew.SiteID = 0
        Dim sid As Integer
        Dim maxsid As Integer = 1  'in case sender.List has no items (except it will have our new item)
        For Each drv As DataRowView In sender.List
            Dim dr As CFacilityRow
            dr = drv.Row            ' get the row from the DataRowView
            sid = dr.SiteID         ' get the SiteID from the row
            If sid > maxsid Then    ' save the largest SiteID number
                maxsid = sid
            End If
        Next

        drNew.SiteID = maxsid + 1   ' Set a default SiteID for this new row

    End Sub

Open in new window

0
 
CodeCruiserCommented:
You need to fill values in columns of this new row before saving.
0
 
deleydAuthor Commented:
Yes, how do I fill in default values for cells that have null values in them, or numeric cells with no number in them, or checkboxes that haven't been checked or unchecked?

When the user clicks "Save" I'd like an opportunity to review the row and fill in default values for cells with null values in them.

I tried the 'CellValidating' event, but it looks like I can only cancel the adding of row, I'm not sure I can change the row and then say everything is OK. Also read I should use the 'CellParseing' event handler, but that only fires if the user enters and then exits a column.

"If you are doing conversions or formatting for display purposes, and want to validate the data before it gets pushed back into the underlying data source but after the parsing process has occurred, you will want to call that logic in the CellParsing event handler, not the CellValidating event handler."

Also read I could intercept the "AddingNew" event and give it a row with default values, but couldn't figure out how to create a row of the proper type. (It kept complaining I was mixing data types.)

Tried setting default values in the database design, but looks like I can only set default strings, which doesn't work for checkboxes.

So gave up and thought I'd ask the experts here.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
CodeCruiserCommented:
>Tried setting default values in the database design, but looks like I can only set default strings, which doesn't work for checkboxes.

Checkboxes are usually of type bit in db so you need to use 0 as default value for false.


>Also read I could intercept the "AddingNew" event and give it a row with default values, but couldn't figure out how to create a row of the proper type. (It kept complaining I was mixing data types.)

You can handle the TableNewRow event of datatable which will allow you to use e.Row object and set its values.
0
 
deleydAuthor Commented:
You can handle the TableNewRow event of datatable which will allow you to use e.Row object and set its values.

Close, but I don't have a DataTable. I have a DataSet, BindingSource, & DataGridView.

There's also a TableAdapter & TableAdapterManager. Not sure what they are used for.

DataSet has no Add event.

BindingSource is too generic, has only AddingNew event.

DataGridView has a lot of events. Has a RowsAdded event, but the event args for that  only gives me RowIndex and RowCount, no actual row or rows; plus it gets called quite often, not just when a new row is being added.

DataGridView also has a UserAddedRows event, but it didn't get called when I tested it.

Haven't yet found a useful event to intercept.
0
 
deleydAuthor Commented:
I should add here how to get the changes back into the database itself, now that I've figured out that part too.

      _form.Validate()
      _form.CFacilityBindingSource.EndEdit()
      _form.TableAdapterManager.UpdateAll(_form.CcdbaseDataSet1)
      _form.CcdbaseDataSet1.AcceptChanges()

Open in new window

Here _form is the form which has my DataGridView in it.

1. Need to manually call Validate to trigger the Validation events (OnValidating)
2. EndEdit
3.TableAdapterManager.UpdateAll (or TableAdapter.Update)
4. DataSet.AcceptChanges to actually write the changes

there's also a DataSet.RejectChanges . We'll see if that works when the user clicks the 'Cancel' button.
0
 
deleydAuthor Commented:
Solved it myself after a lot of research and experimentation.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.