Click here to Skip to main content
15,039,714 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
Hello Everybody

I am getting problem with the last inserted id.
I have three table namely
1. tblQuestion for storing questions
its column are qid, question
where qid is primark key and identity
2. tblAnswerChoice for storing choices for question. All question has four choices
its column are ansid, qid, answerchoice
where ansid is primark key and identity
ans qid is foreign key of tblquestion

3. tblAnswer for storing answer
its column are aid, qid and ansid
where aid is primary key and identity
qid is the reference of tblQuestion
ansid is the refrence of tblAnswerChoices

The structure of the form is
Question textbox
Choice 1 textbox radiobutton 1
Choice 2 textbox radiobutton 2
Choice 3 textbox radiobutton 3
choice 4 textbox radiobutton 4
and save button

when you press the save button
the question will store in the tblQuestion
answer choice will store in the tblAnswerChoice and pick the last stored id of the tblQuestion to store the value qid

I have done this task

But my problem is that I have to store the id of correct answer in the third table
i.e. tblAnswer.


Question - What is the cabital of India?
and its choices are
Choice 1- SriLanka
Choice 2- India
Choice 3- NewYork
Choice 4- London

the correct answer is india and its id is 2
it is generated when we save the question

then in the third table tblAnswer will have the id of India

After inserting the data in three table it will look


qid       Question
1          What is the capital of India?


ansid   qid        Choice
1        1       SriLanka
2        1       India
3        1       NewYork  
4        1       London


aid     qid     ansid
1        1        2

How to to store the id of India in the third table tblAnswer
as all the id is generated at one time and the insert query at same time then how to store the answer when the user select the option 2 i.e. India to save the answer

queries that i have used

insert into tblQuestion(Question,CreationDate)

	--for saving answer choices
	Declare @QuestionID INT
    	select @QuestionID = IDENT_CURRENT('tblQuestion')
	insert into tblAnswerChoice (QuestionID,AnswerChoice) values(@QuestionID ,@AnswerChoice1)
	select @QuestionID = IDENT_CURRENT('tblQuestion')
	insert into tblAnswerChoice (QuestionID,AnswerChoice) values(@QuestionID,@AnswerChoice2)
	select @QuestionID = IDENT_CURRENT('tblQuestion')
	insert into tblAnswerChoice (QuestionID,AnswerChoice) values(@QuestionID,@AnswerChoice3)
	select @QuestionID = IDENT_CURRENT('tblQuestion')
	insert into tblAnswerChoice (QuestionID,AnswerChoice) values(@QuestionID,@AnswerChoice4)
	select @QuestionID = IDENT_CURRENT('tblQuestion')
	insert into tblAnswerChoice (QuestionID,AnswerChoice) values(@QuestionID,@AnswerChoice5)

Updated 28-Sep-11 14:11pm


You may want to consider getting rid of tblAnswer. You could add an additional column to tblQuestion with CorrectAnswerID as a foreign key to tblAnswerChoice. This would simplify your design and require one less database call.

Or if you wanted to expand your questions types to allow for multiple answers on the same question, you could put an answer flag in tblAnswerChoice as [bool] CorrectAnswer column. Then you don't need to know any new ids, but simply pass in a bool to say if a supplied answer is correct.

If you still need the three table design that you are asking, provide the SQL code you're using to insert the data and I'll help.


[Edit with SQL Stored Procedure]

Try the stored procedure below to solve your issue. Using the
gets you the last ID number inserted into the database. Its safe because all of the code is contained within a transaction. I'm sure there is a better way, but this was just some quick help to get you going. Enjoy!

USE [QuestionDatabase]

CREATE PROCEDURE [dbo].[AddQuesion]
@Question			varchar(100), 
@Answer1			varchar(100), 
@Answer2			varchar(100), 
@Answer3			varchar(100), 
@Answer4			varchar(100), 
@Answer5			varchar(100),
@CorrectAnswer      int

DECLARE 			varchar(8000)
DECLARE @QuestionID	int

insert into tblQuestion(Question, CreationDate)
values		(@Question,@CreationDate)

--Get the QuestionID 1 time.
SELECT @Question = @@Identity
 --for saving answer choices
insert into tblAnswerChoice (QuestionID, AnswerChoice) values(@QuestionID ,@Answer1)
IF (@CorrectAnswer = 1)
	SET @CorrectAnswer = @@Identity

insert into tblAnswerChoice (QuestionID, AnswerChoice) values(@QuestionID ,@Answer2)
IF (@CorrectAnswer = 2)
	SET @CorrectAnswer = @@Identity

insert into tblAnswerChoice (QuestionID, AnswerChoice) values(@QuestionID ,@Answer3)
IF (@CorrectAnswer = 3)
	SET @CorrectAnswer = @@Identity

insert into tblAnswerChoice (QuestionID, AnswerChoice) values(@QuestionID ,@Answer4)
IF (@CorrectAnswer = 4)
	SET @CorrectAnswer = @@Identity

insert into tblAnswerChoice (QuestionID, AnswerChoice) values(@QuestionID ,@Answer5)
IF (@CorrectAnswer = 5)
	SET @CorrectAnswer = @@Identity

INSERT INTO tblAnswer (QuestionID, AnswerID) values(@QuestionID, @CorrectAnswer)


Ed Nutting 28-Sep-11 13:20pm
Good answer, my 5 :) I had considered suggesting removing the extra table, as you did, but the extra table can be useful and this is an ideal opportunity for the OP to learn new ways of handling SQL. Please see my solution (#3), Ed :)
aryan2010 28-Sep-11 13:46pm
Now I have added the SQL Query that I have used. I still need the use of third table tblAnswer to store the correct answer
aryan2010 28-Sep-11 16:30pm
I have not removed the extra table i.e. tblAnswer for storing answer.
And I have have written SQL Quesry to insert the data in the two table tblQuestion and tblAnswerChoice.

I have got no idea for the Query for third table so I have not added the the query for third table.
Can you give me the SQL Query for the third table?
snorkie 28-Sep-11 16:31pm
See my improved answer. Good luck!

Might I suggest that instead of getting SQL to create the Guid for you you specify it in the query and thus you will have your value. For example instead of writing an insert query that sets the ansid to a new guid, create your Guid in C# and store it in a variable by doing:

Guid NewAnsId = Guid.NewGuid();
string ChoiceINSERTQuery  = "INSERT INTO tblAnswerChoices VALUES(" + NewAnsId.toString() + ", " + qId.toString() + ", " + answerChoiceString + ")";
string AnswerINSERTQuery = "INSERT INTO tblAnswers VALUES(newGuid(), " + qId.toString() + ", " + NewAnsId.ToString()+ ")";
//Run query - I do NOT garuntee that above SQL will work as I have not tested it...

or if you're using Linq To SQl class (which I highly recommend switching to if you're not! : Simple LINQ to SQL in C#[^])

Guid NewAnsId = Guid.NewGuid();
tblChoiceAnswer newAnsChoice = new tblChoiceAnswer();
newAnsChoice.ansId = NewAnsId;
//... fill in other fields
tblAnswer newAnswer = new tblAnswer();
newAnswer.ansId = NewAnsId;
//...fill in other fields

Hope this helps,

Ed :)
for the recently inserted id

suppose your table is tbl_student contains 2 columns

create a stored procedure

create proc usp Add_student
@id int,
@name varchar(50)
insert into tbl_student values
select @@identity


at the IDE...

Sqlconnection sc=new sqlconnection();
sc.conncetionstring="your connection string";;
sqlcommand cmd=new sqlcommand();
int val=cmd.ExecuteScalar();

now you got the recently inserted record id...
Ed Nutting 28-Sep-11 13:18pm
Reason for my vote of 2: You need to explain better what you're talking about. OP is not going to understand what you mean by 'Execute Scalar' methods (im not sure I do to be honest) nor how to implement your suggested SQL properly. Please improve this and I will up vote it :)

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