DataGridView AllowUserToAddRow property doesn't work

Question

I have a simple project with Entity Framework, I have a DataGridView in my Form and I set its AllowUserToAddRow property to true but still I can not add new rows into it.

And here is my code:

DBEntities context = new DBEntities();
private void Form1_Load(object sender, EventArgs e)
{
    var q = (from i in context.myTable
             select i).ToList();
    DataGridView.DataSource = q;
}

private void btnSave_Click(object sender, EventArgs e)
{
    context.SaveChanges();
    MessageBox.Show("saved successfully");
}

If I use a BindingSource control, it allow me to insert rows in DataGridView but with this approach after I call context.SaveChanges() nothing insert in my database file. So I thought maybe its relative to this problem that DataGridView with true AllowUserToAddRow property doesn't let me to insert row in DataGridView.

Dactyl

Answer 1

Your problem is that you call .ToList() and materialize your query - this appears to break the full databinding.

You should be able to simply have:

DBEntities context = new DBEntities();
private void Form1_Load(object sender, EventArgs e)
{
    var q = (from i in context.myTable
             select i);
    DataGridView.DataSource = q;
}

I tried this and it works fine for allowing new rows (you do need to have a primary key in your table but you should have that anyway).

---

Do Note: this behaviour has been intentionally broken in Entity Framework 4.1 - Webforms data binding with EF Code-First Linq query error

---

I say should in my answer because I'm actually a little surprised it is this easy. I recall it not working so nicely in earlier versions of Entity Framework and I haven't used 4.0 very much.

If the solution above doesn't work you may have to do this the hard way and add new objects yourself before saving:

First introduce a binding source and when you save do something like (with an imaginary entity of Customer in the example):

foreach (Customer customer in bs.List)
{         
    // In my db customerId was an identity column set as primary key
    if (customer.CustomerId == 0)
        context.Customers.AddObject(customer);
}
context.SaveChanges();

Biceps

Thank you very much. Problem was with ToList() method. I removed ToList() method and now it works fine with both insertion or delete. But still it throw an exception in insertion if you have auto-increment (pk) column in your DataGridView. Do you have any suggestion how to handle that exception? – Dactyl

@masoudkeshavarz You mean that your database table has an auto-increment identity column as the primary key? This works fine for me. What is the exception? Probably best to ask this as a new question. – Biceps

An error occurred while updating the entries. See the inner exception for details. Well, fine I ask it in new question. Thank you very much :) – Dactyl

@masoudkeshavarz great - myself or someone else should be able to help once you post the question (include the inner exception) one thing that you should double check is that the model is up to date. Refresh it and see if this still happens. Also - what happens if you add items to the context in code, can you insert then? – Biceps

yes I can insert item in code. In code I leave (pk) empty and SQL SERVER understand its auto-increment. But in DataGridView it seems that it try to insert 0 value as (pk) and it doesn't understand that it is auto-increment – Dactyl

@masoudkeshavarz Try and refresh the model - this works for me with no changes needed. (if refresh doesn't work, post the new question so more people know about it) – Biceps

@masoudkeshavarz one thing to check is that if you go to the model (the edmx) your id column should have StoreGeneratePattern set to Identity - this should be autogenerated for you. – Biceps

@David Hall At least in .Net 4.5, the first aproach throws an error. "Data binding to a store query is not supported. Instead populate a DbSet with data, for example by calling Load on the DbSet, and then bind to local data." But the second aproach with the foreach worked to me. Thanks a lot. – Perlaperle

@AlejandrodelRío thanks for the comment - this is indeed a change in EF4.1 I've added a link to my answer to another SO answer where this is discussed. – Biceps

@David Hall Yes. Indeed this is a huge change that modify the ways to work. It's very important to keep in mind, mostly for people that are recently using the newer .net . Thanks – Perlaperle

Answer 2

I have just painfully upgraded to EF 6 from 4 and I have a similar issue, the solution in EF6 is below and I have shown a where statement for further help.

DBEntities context = new DBEntities();
private void Form1_Load(object sender, EventArgs e)
{
  context.MyTable.Where(e => e.myField == 1).Load();

  BindingSource bs = new BindingSource();
  bs.DataSource = context.MyTable.Local.ToBindingList();
  myDatagridView.DataSource = bs;
}

You can now use context.SaveChanges(); to save the changes or inserts

Thorp

Answer 3

I have had a similar issue with a custom database implementation of an Interbase dialect. The solution for me was similar to that of above:

var tableAList = _dbImplementation.SelectAll<TableA>().ToList();
var bindingSource = new BindingSource();
bindingSource.DataSource = typeof (TableA);
foreach (var tableA in tableAList)
{
    bindingSource.Add(tableA);
}
dataGridView.DataSource = bindingSource;

Helpful reference: A Detailed Data Binding Tutorial

Plotinus

Answer 4

If you are going to bind the dataGridView to a source, then the only appropriate way to insert a row is to add a row to the data structure your DataGridView is binded to.

Victorie

@masoudkeshavarz I will write an example in my answer – Blouse

This is not true - the DataGridView is designed to allow users to add rows to the bound data source using the UI, in fact this is possibly the most standard usage of the control. – Biceps

true, i completely agree with you. However, even when a user add a row, you still wants to update the underlined data structure, because the dataGridView will automatically reflect that change. – Victorie

Answer 5

For me the problem was that I had added two new DataGrids to my form that are bound through a BindingList - but forgot to make a class for them inheriting from INotifyPropertyChanged. I had something like this:

public BindingList<String> MyProperty { get; set; } = "";

But it should have been this:

public BindingList<TextAndOrder> MyProperty { get;
set; } = new BindingList<TextAndOrder>();

In order for BindlingList to work it must be bound to a class that inherits from INotifyPropertyChanged

Where TextAndOrder Class was:

public class TextAndOrder : INotifyPropertyChanged
{
    private String _text = "";
    private int _order = 0;

    public String Text 
    {   
        get
        {
            return _text;
        }
        
        set
        {
            if (_text != value)
            {
                _text = value;
                this.NotifyPropertyChanged("Text");
            }
        }
    }

    public int Order 
    {
        get
        {
            return _order;
        }

        set
        {
            if (_order != value)
            {
                _order = value;
                this.NotifyPropertyChanged("Order");
            }
        }
    }


    private void NotifyPropertyChanged(string propertyName)

    {
        if (PropertyChanged != null)
        {
            this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
        }

    }

    public event PropertyChangedEventHandler PropertyChanged;


}

My Binding in the MainForm.cs was like:

grid.DataSource = MayDataClass.MyProperty; 
grid.AllowUserToAddRows = true; 
grid.AllowUserToDeleteRows = true; 
grid.AllowUserToResizeRows = true; 
grid.AllowUserToResizeColumns = true;

Grapher