quiz query

Posted on 2009-07-08
Medium Priority
Last Modified: 2012-05-07
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

Question by:muneerz
  • 4
  • 2
LVL 42

Expert Comment

ID: 24806768
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:

  ContestID   (PK)

   QuestionID (PK)

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

   QuestionID (PK,FK)  
   AnswerID  (PK)



LVL 42

Expert Comment

ID: 24806787
Didn't quite finish.

I'd also have:

   QuestionID (PK,FK)
   AnswerID (FK)

Author Comment

ID: 24811245
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LVL 42

Expert Comment

ID: 24824547
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.


Author Comment

ID: 24829271
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
LVL 42

Accepted Solution

dqmq earned 2000 total points
ID: 24946248
OK, here's some sample code to get you started
CREATE PROCEDURE [dbo].[InsertUserAnswer]
      @UserId integer,
      @QuestionId integer,
      @AnswerId integer, 
      @ContestId integer 
      INSERT INTO  tblUserAnswer (UserId, QuestionId, AnswerId, ContestId) 
      VALUES (@UserId, @QuestionId, @AnswerId, @ContestId) 
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 
Catch ex As Exception 
      Throw ex 
End Try 

Open in new window


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question