How to make all columns allow null before adding a new row?

Question

How to make all columns allow null before adding a new row to the datatable .

 dt.Rows.Add(dt.NewRow());

This line throws an exception

Column XXX does not allow nulls

How to fix this problem.

Please stop starting your posts with "Q:". There's no need for it, and it makes your questions look funny in the preview lists that show only the first couple lines of your post. Just dive right into your question. — Andrew Barber, Feb 06 '12 at 10:11
hhhhh okay , just i wanna to format my questions . but any new question will be without `Q` :) Thanks for the hint. — Anyname Donotcare, Feb 07 '12 at 12:28

score 5 · Accepted Answer · edited Jun 23 '17 at 22:23

5

You don't add the row until it's filled and ready to be saved.

DataRow row = dt.NewRow();

...fill row with values...

dt.Rows.Add(row);

edited Jun 23 '17 at 22:23

CMedina

4,034
3
24
39

answered Jan 31 '12 at 14:13

AndyMcKenna

2,607
3
26
35

the datable has many columns and i wanna to initialize it with a row to allow me later .to do some thing like that : `dt.Rows[0]["regnum_e"] = dt_instead.Rows[0]["regnum_e"].ToString().TrimEnd();` Without throwing exception there's no row at position 0. – Anyname Donotcare Jan 31 '12 at 14:16
1

dt.NewRow() initializes a row, dt.Rows.Add() saves it. You need to add at least the required values to the DataRow you got from NewRow() before you try to add it to Rows. – AndyMcKenna Jan 31 '12 at 14:32
Eggxelent!; I only require include a new row to gathering the columns settings of the DataTable. With this option I can access to the settings without include the row in the original DataTable – MiBol Sep 01 '16 at 16:23

score 1 · Answer 2 · answered Jan 31 '12 at 14:15

Usually the database designer specifies that a column can't be null for a reason. For example, it might be a primary key or a foreign key, or is otherwise mandatory information.

If you are sure that it is OK to provide no data for this column for this particular record, try passing an empty string.

score 1 · Answer 3 · edited May 23 '17 at 10:33

1

It is usually better to initialize the whole row in memory before sending it to the database (instead of filling it field-by-field when it is already there).

However, if you absolutely must do that, and if your DBMS supports it, you can declare your NOT NULL constraints as deferred, so they are not checked until the transaction commits. Here is an Oracle example.

edited May 23 '17 at 10:33

Community

1
1

answered Jan 31 '12 at 14:23

Branko Dimitrijevic

50,809
10
93
167

score 1 · Answer 4 · answered Jan 31 '12 at 14:42

It's rarely good solution. Start from redesigning your database.

Consider removing NOT NULL constraints from all not necessary fields.

Also if any fields is obligatory, but you still do not want to fill it during row creation, set default value either in database or middle layer (ORM or whatever)

edit: however, in this case it looks like you're just trying to pass an empty row to db, before initializing it with data. That will never work ;-)

How to make all columns allow null before adding a new row?

4 Answers4