Solved

Adding rows to Northwind DataGridView - NoNullAllowedException

Posted on 2013-05-16
7
556 Views
Last Modified: 2013-05-27
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.
0
Comment
Question by:deleyd
  • 5
  • 2
7 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39174379
You need to fill values in columns of this new row before saving.
0
 

Author Comment

by:deleyd
ID: 39175287
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39175326
>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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:deleyd
ID: 39178654
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
 

Accepted Solution

by:
deleyd earned 0 total points
ID: 39188181
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
 

Author Comment

by:deleyd
ID: 39188646
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
 

Author Closing Comment

by:deleyd
ID: 39198762
Solved it myself after a lot of research and experimentation.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now