How to insert data in a table which contain foreign keys

Question

0.00/5 (No votes)

See more:

Hello Guys.
I'm developing a project of Library Management System and facing a problem. I have a tables like author, publisher, category, bookauthor(BookAuthorId,AuthorId,BookId), Bookcategory(BookCategoryId,BookId,CategoryId), BookPublisher(BookPublisherId, BookId,PublisherId) and most of all book table which contain attributes(BookId,BookTitle, BookEdition, BookAuthorId,BookPublisherId,BookCategoryId). Hope you'll understand my DB.
The question is, whenever i'm going to insert data for a new book, i firstly insert values of BookTitle,BookEdition and BookQuantity, for the idea to fetch the id of freshly inserted book and then put it into foreign keys tables like BookAuthor(BookId,Combobox1.selectedvalue).
But it doesn't working. Tell me how can i add new book and handle these foreign keys at one button click, i'm sharing the code, hope you'll pick up my point.

C#

DatabaseConnect db = new DatabaseConnect();
DataSet ds = new DataSet();
db.executequery("Insert into Book(BookTitle, BookEdition,BookQuantity,BookStatus)Values('" + textBox1.Text + "','" + textBox2.Text + "''" + textBox3.Text + "','1')");
ds = db.executedataset("Select Max(BookId) from Book");
int i = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());

db.executequery("Insert into BookCategory(BookId,CategoryId)Values('" + i + "','" + comboBox1.SelectedValue + "')");

db.executequery("Insert into BookAuthor(BookId,AuthorId)Values('" + i + "','" + comboBox2.SelectedValue + "')");
db.executequery("Insert into BookPublisher(BookId,PublisherId)Values('" + i + "','" + comboBox3.SelectedValue + "')");
MessageBox.Show(comboBox1.SelectedText, " Book Successfully inserted");

Help me out please.

Edit - table structure from comments
Table Name= Book
BookId int NotNull
BookTitile varchar NotNull
BookEdition varchar NotNull
BookQuantity varchar NotNull
BookStatus int NotNull
BookAuthorId int NotNull
BookCategoryId int NotNull
BookPublisherId int NotNull

BookAuthor =Table
BookAuthorId int NotNull
BookId int NotNull
AuthorId int NotNull

BookPublisher =table
BookPublisherId int NotNull
BookId int NotNull
PublisherId int Not Null

Author =Table
AuthorId int notNull
AuthorName varchar notNull

Publisher = table
PublisherId int notnull
PublisherName Varchar notNull

Posted 29-Nov-14 6:55am

M Adeel Khalid

Updated 29-Nov-14 10:50am

Tomas Takac

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tomas Takac · Accepted Answer · 2014-11-29T07:07:00

Solution 1

You must use SCOPE_IDENTITY[^] in your SQL to get the latest identity value inserted and return that to the application. I don't know the DatabaseConnect class but it should look something like this:

C#

var bookId = db.executescalar("Insert into Book(BookTitle, BookEdition,BookQuantity,BookStatus)Values('" + textBox1.Text + "','" + textBox2.Text + "''" + textBox3.Text + "','1'); select SCOPE_IDENTITY()");

Please note that your code is vulnerable to SQL injection. You should use parameters to pass the values from UI to the database.

Edit
I reused your code to extract first value in the dataset to get around the non-existing executescalar(). If SCOPE_IDENTITY is not working for you can try @@IDENTITY[^].

C#

DataSet ds = db.executedataset("Insert into Book(BookTitle, BookEdition,BookQuantity,BookStatus)Values('" + textBox1.Text + "','" + textBox2.Text + "''" + textBox3.Text + "','1'); select @@IDENTITY");
int bookId = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());

Edit 2 - to solve the real problem
You need to provide values for all not null columns like this:

C#

DataSet ds = db.executedataset("Insert into Book (BookTitle, BookEdition, BookQuantity, BookStatus, BookCategoryId, BookAuthorId, BookPublisherId) Values('" + textBox1.Text + "','" + textBox2.Text + "''" + textBox3.Text + "','1','" + comboBox1.SelectedValue + "','" + comboBox2.SelectedValue + "','" + comboBox3.SelectedValue + "'); select SCOPE_IDENTITY()");
int bookId = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());

I don't know if your DatabaseConnect class supports parameter, but it's something you should really look into.

Edit 3 - change tables
I wasn't paying much attention to the other tables previously. The issue cannot be resolved - there is a chicken-egg problem. To insert a Book you need BookPublisherId. But to insert into the BookPublisher table you need BookId. You need to change your table structure. Let Book reference Publisher, Author, Category directly:

Book table
  BookId       indentity
  AuthorId     FK referencing Author table
  PublisherId  FK referencing Publisher table
  CategoryId   FK referencing Category table
  ... other columns ...

Drop tables BookCategory, BookAuthor, BookPublisher they are useless in their current form. This way the last query will work as expected.

Posted 29-Nov-14 7:07am

Tomas Takac

Updated 29-Nov-14 11:37am

v5

Comments

M Adeel Khalid 29-Nov-14 13:23pm

My DB class has 2 methods, 1 for executequery(insert statements), 2nd for executedataset(select statements), i'm using SQLServer which is built in Visual Studio 2010, scope identity() could be a built in function of sql server but not supported in Visual studio's own SQl. Any suggestions?

Tomas Takac 29-Nov-14 13:44pm

Answer updated. As for your SQL Server, you are probably referring to Express edition. I don't see why SCOPE_IDENTITY should not be supported there. I couldn't find anything online anyway. Could you post the error message?

M Adeel Khalid 29-Nov-14 13:47pm

I've checked scope identity(), it doesn't show any error while i checked the query. Problem is mentioned below.
"Cannot insert the value NULL into column 'BookPublisherId', table 'lms.dbo.Book'; column does not allow nulls. INSERT fails.
The statement has been terminated."
it means the recently added bookId is not fetched correctly. :(

Tomas Takac 29-Nov-14 13:57pm

This has nothing to do with SCOPE_IDENTITY. Please note that BookPublisherId is not among the values you are inserting into Book. But the column is NOT NULL hence the error. Update your query to insert the value. Now I see this was the problem from the very beginning. Please be careful to provide the error message next time. Anyway, you should use SCOPE_IDENTITY, not selecting max(id) from the table, this part of the answer holds.

M Adeel Khalid 29-Nov-14 14:03pm

Sorry, it was my first time to post here, that's why i asked wrongly.
I've tried scope identity as per your suggestion but it still giving me the same error. I'm inserting 3 values in book and try to get the book id for the bookauthor table where i used to enter book id and author id(selected value from combobox). It took whole day to solve this problem but still stands. :( i'm very much disappointed.

Tomas Takac 29-Nov-14 14:16pm

You are actually inserting four values to book: BookTitle, BookEdition,BookQuantity,BookStatus. What I'm saying is that the Book table has a columns named BookPublisherId which does not allow nulls. But in your insert there is no value for that column so the insert fails. You insert should look like this:insert into Book(BookTitle,BookEdition,BookQuantity,BookStatus,BookPublisherId) values(...)

M Adeel Khalid 29-Nov-14 14:21pm

you are right but BookPublisher table contains 2 foreign keys(BookId and PublisherId) which are not null, i also have to insert in that table. Tell me how can i insert only BookPublisherId without having need to insert in it's foreign keys. It also gives an error.

Tomas Takac 29-Nov-14 14:25pm

Don't mind my last comment below. This answers the question. You have two options: either provide value for the FKs, or alter the table to allow nulls in those columns.

M Adeel Khalid 29-Nov-14 14:18pm

How to update query which works perfectly?
i did the following...

db.executequery("insert into Book(BookTitle, BookEdition,BookQuantity,BookStatus)Values('"+tb1.text+"','"+tb2.text+"','"+tb3.text+"','1') ")
dataset ds.executedataset("Select Scope_Identity()As expr1 from Book");
int i=convert.int32(ds.tables[0].rows[0][0].tostring());
db.executequery("Insert into BookPublisher(BookId,PublisherId)Values('"+i+"',cb1.selectedvalue)");

BookPublisher Id is set to autoincrement, still giving same error.

Tomas Takac 29-Nov-14 14:23pm

First, you didn't follow my code example. Insert and Select SCOPE_IDENTITY must in one query. Secondly, I'm not talking about BookPublisher table. The error message clearly says the insert into Book failed. Can you confirm there is a column named BookPublisherId in Book table? Or better post the structure of the Book table in your question?

M Adeel Khalid 29-Nov-14 14:27pm

Kindly give me your e-mail id, i'll post my whole database to you, then you'll clearly understand my guide and i'm 100% sure, you'll guide me in a better way.

Tomas Takac 29-Nov-14 14:35pm

Sorry, I won't post my e-mail address here. Please update your question with the create script for Book table. This is where the problem is.

M Adeel Khalid 29-Nov-14 14:42pm

Sorry, i don't know how to create Script. I post my table here.

Table Name= Book
BookId int NotNull
BookTitile varchar NotNull
BookEdition varchar NotNull
BookQuantity varchar NotNull
BookStatus int NotNull
BookAuthorId int NotNull
BookCategoryId int NotNull
BookPublisherId int NotNull

Tomas Takac 29-Nov-14 15:38pm

This is good. Please note that all the columns are not null. We know BookId is an identity so has a default value. You can omit column from insert only if it's nullable or has a default value. Now look at your insert into Book, you are only providing values form title, edition, quantity, status. You need to provide author, category and publisher. Now this shouldn't be a problem because you have those ids, you use them in the subsequent inserts. I updated the solution.

M Adeel Khalid 29-Nov-14 15:59pm

i followed what you mentioned, still error.. :(

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Book_BookPublisher". The conflict occurred in database "lms", table "dbo.BookPublisher", column 'BookPublisherId'.
The statement has been terminated.

Tomas Takac 29-Nov-14 16:48pm

I looked carefully at you other tables and you've got a chicken-egg problem. My previous suggestion is not correct, not for you table structure at least. See you need BookPublisherId to insert into Book. And you need BookId to insert into BookPublisher. There is no way to resolve this. I suggest you go back to the drawing board and re-think it. I will update the answer accordingly.

M Adeel Khalid 29-Nov-14 14:45pm

BookAuthor =Table
BookAuthorId int NotNull
BookId int NotNull
AuthorId int NotNull

BookPublisher =table
BookPublisherId int NotNull
BookId int NotNull
PublisherId int Not Null

Author =Table
AuthorId int notNull
AuthorName varchar notNull

Publisher = table
PublisherId int notnull
PublisherName Varchar notNull