Click here to Skip to main content
15,665,942 members
Please Sign up or sign in to vote.
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.
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
Updated 29-Nov-14 10:50am

1 solution

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:
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.

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[^].
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:
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.
Share this answer
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.

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900