Hi ,
i am designing QuestionCreation form using asp.net(C#) which consists
4 fields(Question,Qtype(single/multiple),options,answer). these 4
fileds should be saved into 3 sql
tables(Quetion,Answer,Answer_Details). The fields for these tables
are:
Quetion(idquetion,QName,Qtype,idAnswer)
Answer(idAnswer,Answer)
Answer_Details(idAnswerDetail,idAnswer,Options)

for this i had written the following Querry:

use questionbank
create table Question
(idquestion int identity(1,1) primary key,
Qname varchar(MAX) not null,
Qtype varchar(20) not null,
idAnswer int
)

create table Answer
(idAnswer int references Question(idAnswer) on update cascade on
delete cascade,
Answer varchar(50) not null,
)

create table Answer_Detail
(idAnswerDetail int identity(1,1)primary key,
idAnswer int references Question(idAnswer) on update cascade on delete
cascade,
options varchar(50)
)

here i struck with problem.

” i want to set identity property for idAnswer in Answer and
Answer_Detail table. if i do so, i get an error, what can i do. here i
want, all the 3 idAnswers should be automatically incremented equally
during submitting QuetionCreation form.”.

and also i had one  add option on my form, which adds one text box on
each click. how can i store these dynamically generated options into
my Answer_Detail table.

It seems like question and answer have a 1-to-1 relationship. If so, you could do something like this and use the first stored proc (uspInsertQuestion) to insert the question and answer, and then using the returned identity value, use the second proc (uspInsertAnswerDetails) to insert the answer detail options.

CREATE TABLE Question
(
     idquestion INT IDENTITY(1, 1) PRIMARY KEY
   , Qname VARCHAR(MAX) NOT NULL
   , Qtype VARCHAR(20) NOT NULL
   , Answer VARCHAR(50) NOT NULL
) 
GO

CREATE TABLE Answer_Detail
(
     idAnswerDetail INT IDENTITY(1, 1) PRIMARY KEY
   , idquestion INT REFERENCES Question (idquestion) ON UPDATE CASCADE ON DELETE CASCADE
   , options VARCHAR(50)
) 
GO

CREATE PROC uspInsertQuestion (@Qname VARCHAR(MAX), @Qtype VARCHAR(20), @Answer VARCHAR(50))
AS
BEGIN
   INSERT INTO Question (Qname, Qtype, Answer) VALUES (@Qname, @Qtype, @Answer)
   RETURN SCOPE_IDENTITY()  
END
GO

CREATE PROC uspInsertAnswerDetails (@idquestion INT, @options VARCHAR(50))
AS
BEGIN
   INSERT INTO Answer_Detail (idquestion, options) VALUES (@idquestion, @options)
   RETURN SCOPE_IDENTITY()  
END
GO


 

Hi, thank you for giving your valuable suggestion.  but how can i set idquestion  in Answer_Detail table because we did not give value directly to it. it should be incremented as like in table Question.

and one more thing.

   i provided a link(add options, which adds one text box on each click) on my form. the no of options may change question to question. it may be 4 or 5... how can i insert these values into options field in Answer_Detail table because these are dynamically generated.

 

my Answer_Detail should be like this (if q1 has 4, q2 has 2 and q3 has 3 options)

   idanswerDetail      idquestion     options
         1                       1              opt1

         2                       1              opt2

         3                       1              opt4

         5                       2              opt1

         6                       2               opt2

         7                       3               opt1

         8                       3               opt2

         9                       3               opt3
 

 

When you execute the uspInsertQuestion procedure, it will return a value for you to use as the @idquestion parameter for the uspInsertAnswerDetails procedure.

This will give you the results you are looking for.

thanku sir, it's working.