Solved

Adding rows to Northwind DataGridView - NoNullAllowedException

Posted on 2013-05-16
7
541 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

746 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

13 Experts available now in Live!

Get 1:1 Help Now