Cannot insert the value NULL into column
-
Hi all. I have run into a problem with inserting values into table. I have 2 tables: PUBLISHER with fields: PUBLISHER_CODE (Primary Key) and PUBLISHER_NAME. BOOK table fields: PUBLISHER_CODE (Primary Key and Foreign key to PUBLISHER table) and TITLE. The user enters bookCode and publisherName into 2 textboxes Here is the code for inserting: "INSERT INTO BOOK (TITLE) VALUES(@bookCode)"; "INSERT INTO PUBLISHER (PUBLISHER_NAME) VALUES(@publisherName)"; When running programme, the PUBLISHER table generates a new PUBLISHER_CODE (Primary Key) and publisherName inserted. Now problem is for the BOOK table the following error appears: "Cannot insert the value NULL into column 'PUBLISHER_CODE'", table BOOK :sigh: Why is this happening? Doesnt BOOK generate same/new PUBLISHER_CODE from PUBLISHER table since its a foreign key?
-
Hi all. I have run into a problem with inserting values into table. I have 2 tables: PUBLISHER with fields: PUBLISHER_CODE (Primary Key) and PUBLISHER_NAME. BOOK table fields: PUBLISHER_CODE (Primary Key and Foreign key to PUBLISHER table) and TITLE. The user enters bookCode and publisherName into 2 textboxes Here is the code for inserting: "INSERT INTO BOOK (TITLE) VALUES(@bookCode)"; "INSERT INTO PUBLISHER (PUBLISHER_NAME) VALUES(@publisherName)"; When running programme, the PUBLISHER table generates a new PUBLISHER_CODE (Primary Key) and publisherName inserted. Now problem is for the BOOK table the following error appears: "Cannot insert the value NULL into column 'PUBLISHER_CODE'", table BOOK :sigh: Why is this happening? Doesnt BOOK generate same/new PUBLISHER_CODE from PUBLISHER table since its a foreign key?
Member 9912091 wrote:
Doesnt BOOK generate same/new PUBLISHER_CODE from PUBLISHER table since its a foreign key?
No - you have to retrieve it from the inserted publisher record. I use a stored proc for this and return the new record after it is inserted, then you can use that ID to insert the book record. Or Wrap the whole thing in a SQL Transaction in a stored proc and do the insert to both table at once.
Never underestimate the power of human stupidity RAH
-
Hi all. I have run into a problem with inserting values into table. I have 2 tables: PUBLISHER with fields: PUBLISHER_CODE (Primary Key) and PUBLISHER_NAME. BOOK table fields: PUBLISHER_CODE (Primary Key and Foreign key to PUBLISHER table) and TITLE. The user enters bookCode and publisherName into 2 textboxes Here is the code for inserting: "INSERT INTO BOOK (TITLE) VALUES(@bookCode)"; "INSERT INTO PUBLISHER (PUBLISHER_NAME) VALUES(@publisherName)"; When running programme, the PUBLISHER table generates a new PUBLISHER_CODE (Primary Key) and publisherName inserted. Now problem is for the BOOK table the following error appears: "Cannot insert the value NULL into column 'PUBLISHER_CODE'", table BOOK :sigh: Why is this happening? Doesnt BOOK generate same/new PUBLISHER_CODE from PUBLISHER table since its a foreign key?
Primary key value cant be null. You have to provide any value for that.
-
Primary key value cant be null. You have to provide any value for that.
Bikash Prakash Dash wrote:
You have to provide any value for that
No you have to provide the correct key, any key will corrupt your data.
Never underestimate the power of human stupidity RAH