Unused Column throws not null error in PgSqlDataTable

Unused Column throws not null error in PgSqlDataTable

Postby chris901 » Mon 17 Oct 2016 05:25

Hello,

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

I installed the database like this:
http://www.postgresqltutorial.com/load-postgresql-sample-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: Image

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

On dataLink1 I set:
DataSource = pgSqlDataTable1

SelectCommand for pgSqlDataTable1: Image

InsertCommand for pgSqlDataTable1: Image

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)

Image



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:
Image
chris901
 
Posts: 64
Joined: Wed 20 Jul 2016 04:21

Re: Unused Column throws not null error in PgSqlDataTable

Postby Pinturiccio » Wed 19 Oct 2016 14:15

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.
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44

Re: Unused Column throws not null error in PgSqlDataTable

Postby chris901 » Wed 19 Oct 2016 19:24

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?
chris901
 
Posts: 64
Joined: Wed 20 Jul 2016 04:21

Re: Unused Column throws not null error in PgSqlDataTable

Postby Pinturiccio » Mon 24 Oct 2016 14:10

chris901 wrote: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.
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for PostgreSQL