使用最后插入的ID [英] Use Last Inserted Id
问题描述
大家好
我最后插入的ID出现问题.
我有三张桌子
1. tblQuestion用于存储问题
它的列是qid，问题
其中qid是primark键和身份
2. tblAnswerChoice用于存储问题的选择.所有问题都有四个选择
它的列是ansid，qid，answerchoice
其中ansid是primark键和身份
ans qid是tblquestion的外键
3. tblAnswer用于存储答案
它的列是aid，qid和ansid
援助是主键和身份的地方
qid是tblQuestion
的引用
ansid是tblAnswerChoices
的提要
表格的结构是
问题文字框
选择1文本框单选按钮1
选择2文本框单选按钮2
选择3文本框单选按钮3
选择4文本框单选按钮4
并保存按钮
按下保存按钮时
该问题将存储在tblQuestion
答案选择将存储在tblAnswerChoice 中，并选择tblQuestion的最后存储的ID以存储值qid
我已经完成了这项任务
但是我的问题是我必须将正确答案的ID存储在第三张表中
即tblAnswer.
假设
问题-印度的首都是多少?
它的选择是
选择1-斯里兰卡
选择2-印度
选择3-纽约
选择4-伦敦
正确答案是印度，其ID为2
它是在我们保存问题时生成的
那么在第三张表中，tblAnswer将具有印度的ID
在三个表中插入数据后，它将看起来
tblQuestion
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
tblAnswerChoice
ansid qid Choice
-------------------------
1 1 SriLanka
2 1 India
3 1 NewYork
4 1 London
tblAnswer
tblAnswer
aid qid ansid
----------------------
1 1 2
如何在第三张表tblAnswer中存储印度的ID
因为所有ID一次生成，插入查询同时生成，所以当用户选择选项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)
--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)
谢谢
Deepak
Thanks
Deepak
推荐答案
Deepak，
您可能要考虑摆脱tblAnswer.您可以在tblQuestion中添加一个额外的列，并将CorrectAnswerID作为tblAnswerChoice的外键.这将简化您的设计，并减少一个数据库调用.
或者，如果您想扩展问题类型以允许在同一问题上有多个答案，则可以将答案标志放在[bool] CorrectAnswer列中.然后，您无需知道任何新的ID，只需简单地输入布尔值即可说明提供的答案是否正确.
如果仍然需要您要的三表设计，请提供用于插入数据的SQL代码，我们将为您提供帮助.
霍根
[使用SQL存储过程进行编辑]
请尝试以下存储过程来解决您的问题.使用
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
Try the stored procedure below to solve your issue. Using the
@@Identity可以获取插入到数据库中的最后一个ID号.这是安全的，因为所有代码都包含在事务中.我敢肯定有更好的方法，但这只是一些快速的帮助，可以助您一臂之力.享受吧！
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]
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
可能我建议您在查询中指定SQL而不是让它为您创建Guid因此，您将拥有自己的价值.例如，不要编写将ansid设置为新guid的插入查询，而是使用C#创建您的Guid并将其存储在变量中，方法是:
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...
或者如果您使用的是Linq To SQl类(强烈建议您切换到该类！):简单的LINQ to SQL in C# [ ^ ])
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
MyDatabaseContext.tblChoiceAnswers.InsertOnSubmit(newAnsChoice);
MyDatabaseContext.tblAnswers.InsertOnSubmit(newAnswer);
MyDatabaseContext.Submit();
希望这会有所帮助，
Ed:)
Hope this helps,
Ed :)
最近插入的ID
假设您的表是tbl_student包含2列
1.id
2.名称
创建一个存储过程
for the recently inserted id
suppose your table is tbl_student contains 2 columns
1.id
2.name
create a stored procedure
create proc usp Add_student
@id int,
@name varchar(50)
as
insert into tbl_student values
(@name)
select @@identity
然后
在IDE上...
then
at the IDE...
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();
现在您获得了最近插入的记录ID ...
now you got the recently inserted record id...
这篇关于使用最后插入的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
