Solved

Adding rows to Northwind DataGridView - NoNullAllowedException

Posted on 2013-05-16
7
596 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
API v SOA 8 63
Telerik RadEditor Control Save 8 39
Read oracle BLOB in vb.net 2 24
Select coumns form Data table? 3 26
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
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…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

739 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