Master-Detail form - PK Columns

[afh]

I'm trying to build a master-detail form using two DataGridView's based on Oracle 11g Database in VB.net (Enterprise 2015). It is a setup form to define categories (category_id, title, status) and sub categories (sub_cat_id, category_id, title, status). I've followed this example to build the form. However, to retrieve the primary column value I've used following query in the category table adapter to fetch next sequence value.

SELECT ePos.PROD_CATG_SEQ.NEXTVAL FROM DUAL

I'm facing following problems:


a) Form generates error if I leave the primary key column "category_id" blank
b) If I provide any dummy value say "-1" in the "category_id" column while filling the record and press save button, -1 is stored in the database and value of sequence is just shown on the form.



The tables structure is as follows:

CREATE TABLE ePos.prod_category
(
    category_id NUMBER(5) PRIMARY KEY,
    title   VARCHAR2(100) NOT NULL,
    status  VARCHAR2(1) default 'Y' NOT NULL
);


CREATE SEQUENCE ePos.PROD_CATG_SEQ
  INCREMENT BY 1
  START WITH 1
  MAXVALUE 99999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER;


CREATE TABLE ePos.prod_sub_category
(
    sub_cat_id  NUMBER(5) PRIMARY KEY,
    category_id NUMBER(5) NOT NULL REFERENCES ePos.prod_category(category_id),
    title   VARCHAR2(100) NOT NULL,
    status  VARCHAR2(1) default 'Y' NOT NULL
);


CREATE SEQUENCE ePos.PROD_SUBCATG_SEQ
  INCREMENT BY 1
  START WITH 1
  MAXVALUE 99999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER;

 

[jmcilhinney]

Are you including 'category_id' in your INSERT statement?

 

[afh]

How may I check Insert statement as I'm not using any customized Insert statement.

 

[afh]

I've changed the InsertCommand of the product category table adapter as follows and created a trigger at database level to assign next value of sequence to PK column. But still getting error if I leave category_id blank. I also want to retrieve the value of the category_id back from database after insertion to display on the Datagridview.

[U][B]InsertCommand Property:[/B][/U] 

INSERT INTO PROD_CATEGORY (TITLE, STATUS)
VALUES (:TITLE, :STATUS)

[U][B]Database Trigger:
[/B][/U]
CREATE OR REPLACE TRIGGER PROD_CATG_TRIG
BEFORE INSERT ON prod_category
FOR EACH ROW
BEGIN
    :NEW.category_id := PROD_CATG_SEQ.NEXTVAL;
END;