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
.
Suppose
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
tblQuestion
qid Question
-------------------
1 What is the capital of India?
tblAnswerChoice
ansid qid Choice
-------------------------
1 1 SriLanka
2 1 India
3 1 NewYork
4 1 London
tblAnswer
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)
values(@Question,@CreationDate)
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)
Thanks
Deepak