大家好 我最后插入的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 LondontblAnswer
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; cmdmandtype=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
发布评论