Solved

Adding rows to Northwind DataGridView - NoNullAllowedException

Posted on 2013-05-16
7
569 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

DevOps Toolchain Recommendations

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

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql server insert 13 31
Footer for each row on Gridview 2 21
Obtain cell value using column ID in DataGrid using VB.NET 2 19
Powershell File Sort 8 31
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

809 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