Unused Column throws not null error in PgSqlDataTable

[chris901]

Hello,

I am using this database:
http://www.postgresqltutorial.com/postg ... -database/

I installed the database like this:
http://www.postgresqltutorial.com/load- ... -database/

I want to use PgSqlDataTable with the actor table.

Here is the DDL for the table:

Code: Select all

CREATE TABLE public.actor
(
  actor_id integer NOT NULL DEFAULT nextval('actor_actor_id_seq'::regclass),   -- serial
  first_name character varying(45) NOT NULL,
  last_name character varying(45) NOT NULL,
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT actor_pkey PRIMARY KEY (actor_id)
);

My setup looks like this:

On pgSqlDataTable1 I set:
Active = true
CachedUpdates = false;

On dataLink1 I set:
DataSource = pgSqlDataTable1

SelectCommand for pgSqlDataTable1:

InsertCommand for pgSqlDataTable1:

Both buttons on the form interact with the dataLink1:

Code: Select all

    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btn_Save_Click(object sender, EventArgs e)
        {
            dataLink1.EndEdit();
        }

        private void btn_New_Click(object sender, EventArgs e)
        {
            dataLink1.AddNew();
        }
    }

So when I now click btn_Add and then btn_Save I get the following error:
(actor_id doesn't allow nulls)





Why does it even throw that error? I am not using the actor_id column in my insert statement (InsertCommand) at all. The correct value is generated automatically by the database because of the nextval of the sequence (see DDL).

When I use the same insert statement with a tool like pgAdmin 4 I works:

[Pinturiccio]

The actor_id column is a part of the primary key of your pgSqlDataTable1, and thus, AllowDBNull is equal to false for this column.

You want to use an insert command without the actor_id column so that the value for this serial column was generated on the server side. However, the query cannot be run on the client side, because if you don’t specify actor_id in the insert command, a DBNull value is inserted to it.

Since actor_id is a part of the primary key, it’s not enough just to assign true to AllowDBNull for it. First, you need to remove the actor_id column from the PrimaryKeys collection, and then set AllowDBNull for it to true.

[chris901]

Is there a way without removing the primary key?

More precisely: What is the correct setup if I want to save a column with the default value and use it in the SelectCommand?

[Pinturiccio]

Is there a way without removing the primary key?

Yes, there is a such way. You need to change the AutoIncrement and the ReadOnly properties to true for the serial column. After this, use an insert command without the serial column as shown on your screenshot, and the server-side generated value will be inserted to the database. In this case the serial value will be taken from the sequence on the server.