Use Last Inserted Id

Question

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 28-Sep-11 6:35am

aryan2010

Updated 28-Sep-11 14:11pm

walterhevedeich

v3

Add a Solution

3 solutions

Solution 3

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

Posted 28-Sep-11 7:16am

Ed Nutting

Solution 1

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

Posted 28-Sep-11 7:06am

devildx2050

Updated 28-Sep-11 14:12pm

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

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)

snorkie · Accepted Answer · 2011-09-28T07:09:00

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