quiz query

please check and help me to get out
I have a task where i have to create a contest page 
 
i have already created a table called tblcontest 
 
tblcontest have contestid which is a primary key 
 
 
2) i have tblquestion where iam storing the questions and these questions will be related to contest id
 
hence in tblquestion i have created primary key as questionid and foreignkey as contestid
 
3) i have tblanswers which is storing 4 choice answers related to question id 
 
in tblanswers i have answerid as primary key and foreignkey as question id
 
4) the tblquestionanswers is having the questionid and answerid 
 
the question id is a question and answerid is a answer to the question
 
 
Now in asp.net page iam unable to call three questions with their multiple choice questions
 
i mean to say the quiz will have 3 question with multiple choice answers
 
each question will have 4 answers
 
below to this question i have also user info to store 
 
so i have created a tbluser with userid as a primary key
 
now, please help me how to fetch the data in asp.net page where i should be able to display  3 questions with 4 choice answers to each question
 
also the moment user submit the answers by clicking the submit button it shld store the user info as well as his answers history and later i should be able to see who is the winner or loser three correct quesion answers will make him winner

Open in new window

muneerzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dqmqCommented:
First, let's cleanup the table design.  Specifications 3 and 4 are conflicting.  

Specification 3 implies: an answer belongs to a single question
Specification 4 implies: an answer may belong to many questions

In other words,  you have two ways to find the answers to a question.  One is to join tblAnswer to tblQuestion.  The other is to join tblQuestionAnswer to tblQuestion.  

So...it's not clear to me why you have tblQuestionAnswers in your design. In fact, if you ALWAYS, ALWAYS, ALWAYS have exactly 4 Answers to EVERY question, it is not clear to me why you have tblAnswers in your design.  (Although, the two absolutes in that assertion do cast come reservations).

Really, I'd suggest this design:

tblContest
  ContestID   (PK)

tblQuestion
   QuestionID (PK)

tblContestQuestion   (allows same question to be used in multiple contests)
   ConstestID (PK, FK)
   QuestionID (PK, FK)

tblAnswer
   QuestionID (PK,FK)  
   AnswerID  (PK)




 







 

0
dqmqCommented:
Didn't quite finish.

I'd also have:

tblCorrectAnswer
   QuestionID (PK,FK)
   AnswerID (FK)
0
muneerzAuthor Commented:
I have created a database on my design and it is correct upto my knowledge, only the part along wuth userid how can i store the user details with correct answers,i want that code in asp.net code
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

dqmqCommented:
Like always, there are many ways.  But I would create a stored procedure to perform the insert and then invoke it from an ado command object, passing the questionID, UserI, and AnswerID as parameters.  You can find many examples on the web for how to call a stored procedure from VB or C# code behind an asp form.

0
muneerzAuthor Commented:
dear dg mg iam not able to find the solution in web, hence i have written to this forumn expecting a code help , please check you could help me in coding part
0
dqmqCommented:
OK, here's some sample code to get you started
CREATE PROCEDURE [dbo].[InsertUserAnswer]
      @UserId integer,
      @QuestionId integer,
      @AnswerId integer, 
      @ContestId integer 
AS 
BEGIN 
      SET NOCOUNT ON; 
      INSERT INTO  tblUserAnswer (UserId, QuestionId, AnswerId, ContestId) 
      VALUES (@UserId, @QuestionId, @AnswerId, @ContestId) 
END 
 
 
---------------------
 
Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString 
Dim con As New SqlConnection(strConnString) 
Dim cmd As New SqlCommand() 
cmd.CommandType = CommandType.StoredProcedure 
cmd.CommandText = "InsertUserAnswer"
cmd.Parameters.Add("@UserID,SqllDbType.Integer).Value = ????cmd.Parameters.Add("@QuestionID,SqllDbType.Integer).Value = ????
cmd.Parameters.Add("@AnswerID,SqllDbType.Integer).Value = ????
cmd.Parameters.Add("@ContestID,SqllDbType.Integer).Value = ????
 
cmdcmd.Connection = con 
Try 
      con.Open() 
      cmd.ExecuteNonQuery() 
 
Catch ex As Exception 
      Throw ex 
Finally 
      con.Close() 
      con.Dispose() 
End Try 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.