-1

I set up a normal binding source with a datagridview to a table. I also set the ID to identity so that I wouldn't have to worry about it. But whenever I call .UpdateAll() on the BindingSource I get the SQL Exception

"Cannot insert explicit value for identity column in table 'Teachers' when IDENTITY_INSERT is set to OFF."

Now how do I tell the binding Source NOT to push the empty ID column to the table? I know this seems like an issue that was already discussed a hundred times before, but I'm new to this and it's driving me crazy.

chrismatic
  • 3
  • 3

1 Answers1

0

Depending on what you want to achieve there is a couple of approach. If you want your identity column to contain non-unique values (which isn't very good idea, but the decision is yours to make) then you can simply disable the identity column in the database:

SET IDENTITY_INSERT Teachers ON;

To revert it run the following:

SET IDENTITY_INSERT Teachers OFF;

But probably, what makes more sense is to make your identity column autoincrement. So when designing your Teachers table the ID column should have this form:

[ID] [int] IDENTITY(1,1) NOT NULL

Or, if you are using SQL Server Management Studio:

  1. Go to your table. Right click it and select Design.
  2. Select the ID column.
  3. In column properties tab find "Identity Specification".
  4. Set "Is Identity" to "Yes", "Identity Increment" to "1", "Identity Seed" to "1".

That should make your ID column to increase automatically hence avoiding empty value when updating the data from the DataGridView.

PiotrWolkowski
  • 8,408
  • 6
  • 48
  • 68
  • First of all thank you for your reply. I had already gotten this far. The problem was that the datasource didn't regard the ID value as autocincrement just like in this post http://stackoverflow.com/questions/6139163/identity-insert-and-linq-to-sql?rq=1 I am sorry that I didn't reasearch this thoroughly enough before posting. – chrismatic Apr 20 '15 at 17:19