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.

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

SQL
insert into tblQuestion(Question,CreationDate)
	values(@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)

Thanks
Deepak
Posted
Updated 28-Sep-11 14:11pm
v3

Deepak,

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.

Hogan

[Edit with SQL Stored Procedure]

Try the stored procedure below to solve your issue. Using the
SQL
@@Identity
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!

SQL
USE [QuestionDatabase]
GO

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

DECLARE 			varchar(8000)
DECLARE @QuestionID	int

BEGIN TRAN
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)


COMMIT TRAN

GO
   
v2
Comments
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!

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

C#
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#[^])

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
MyDatabaseContext.tblChoiceAnswers.InsertOnSubmit(newAnsChoice);
MyDatabaseContext.tblAnswers.InsertOnSubmit(newAnswer);
MyDatabaseContext.Submit();


Hope this helps,

Ed :)
   
for the recently inserted id

suppose your table is tbl_student contains 2 columns
1.id
2.name

create a stored procedure

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


then

at the IDE...


C#
Sqlconnection sc=new sqlconnection();
sc.conncetionstring="your connection string";
sc.open();
sqlcommand cmd=new sqlcommand();
cmd.connection=sc;
cmd.commandtype=commandtype.storedprocedure;
cmd.parameter.addwithvalues("@id",0);
cmd.Parameter.AddwithValue("@name",txt_name.Text);
int val=cmd.ExecuteScalar();

now you got the recently inserted record id...
   
v3
Comments
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