Determine next question in list in an MVC survey application

This question relates to a similar question I asked yesterday that has background info.  The question is here

http://www.experts-exchange.com/Web_Development/Web_Frameworks/MVC/Q_26443780.html



I'm building an MVC survey app which renders out a set of questions and questions choices to a view.  ie

Q1  What is you age
   
    20-30
    30-40
     

Q2  What is your location
    Florida
    Texas

and so on

The view renders out all questions at once by looping through the survey in the view.  However I want to render only one question per page and have a "next" button which then loads the next question.

So the view would look like


Q1  What is you age
   
    20-30
    30-40

              NEXT (persist question to database)
     

Q2  What is your location
    Florida
    Texas

              NEXT (persist question to database)

and so on so each click on next button will save question response to databse.

My question is two things#

1.    If its a user completing questionnaire for first time then load first question

2.    If its a user returning then load next question that has not been answered.

I have a database table that stores amongst other things a set of questions and question responses.


Question answers are stored in this table.

tblQuestionAnswers
answerID
attemptID       ----- I can determine if user is new or returning to a survey
questionID
choiceID
choiceValue
dateanswered


I can already determine if its a new survey or if a user is returning to a survey by the "attemptID"


so my controller would be something like?


..if user has not completed survey then load first question into view

else

if user has started survey determine the next unanswered question and load it into view
ToString1Asked:
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.

 
nmarunCommented:
It's better you have another table with User information. This table might look something like:

Id
FirstName
LastName
UserName
AttemptedQuestionID -- this will store the last question that the user answered.

When you add a new user, set this to 0 and modify it as and when the user continues with the survey.

And continuing from your previous thread, I'd suggest that you modify your action method as follows:

public ViewResult Survey()
{
    // by now you should have the username of the user (login or windows identity or something)
    // lookup the user table and
    // int attemptedQuestionID = //get the AttemptedQuestionID value for the user in concern
    Question question = GetNextQuestion(attemptedQuestionID);
    return View(question);
}

private Question GetNextQuestion(int attemptedQuestionID)
{
     // look into the table and pull up question with the id attemptedQuestionID + 1
     // select [columns] from question where QuestionId = attemptedQuestionID + 1;
     return question;
}

Arun




0

Experts Exchange Solution brought to you by ConnectWise

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
 
nmarunCommented:
I'd also suggest to you that you modify your tblQuestionAnswers to something more normalized:

Table Question
QuestionId (int)
QuestionText (varchar)

Table Choice
ChoiceId (int)
ChoiceText (varchar)
QuestionId (int) - this one tells you what question this choice belongs to

Table Answer:
AnswerId (int)
QuestionId (int)
ChoiceId (int)
UserName (varchar) - if you need this.
DateAnswered (datetime)
0
 
ToString1Author Commented:
Hi nmarun

Yes I have normalised tables I just used the table in my question for mock purposes.

OK i have an attempt table

tblAttempt (int)
attemptID (int)
userID (int)
dateCreated   (date)
dateCompleted (date
surveyID  -   (link to survey that comprises of questions)

So with this table regardless of new or existing user I create an attempt record.

In code I do

    if (attemptRecord == null)  //user completing survey for first time so create a record
        {
            create attempt record

        }
   
    if (attemptRecord "dateCompleted"  == null)   //started survey but not finished

    if ((attemptRecord "dateCompleted" has a date)   // then survey has been completed
     
             
So from this I can determine new,existing or completed survey.

I then have a questionAnswer table that lists

AnswerId (int)
attemptID (int)
userid( int)
QuestionId (int)
ChoiceId (int)
UserName (varchar) - if you need this.
DateAnswered (datetime)

So if I write a question answer then I can set the datetime stamp.   I wanted to use the max date to determine the next unanswered question??

The reason is that if questions are deleted/added then the primary key may change.  For example

questionid          surveyid            text
1                   1                    what colour......
3                   1                    what age is.......
 etc
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
nmarunCommented:
I'm not saying your method would not work, but here's how I would do it:

Table Question
QuestionId (int)
QuestionText (varchar)
SortOrder (int) -- assign a number like 100, 200, 300... for every question that decides its order of sorting and in-turn its order of appearing to the user.

This way you can insert new questions in between two existing questions and modify your survey (kinda on-the-fly). Also your query to get the next question will be quite simple:

Select Top 1 QuestionId, QuestionText
From Question
Where QuestionId > attemptedQuestionId
Order by SortOrder ASC

The attemptedQuestionId could be passed to the stored procedure or set as a parameter to a SQL View.

Arun
0
 
ToString1Author Commented:
OK thanks nmarun

So say all question ids are in order say

1,2,3,4 etc

How could I load the first question?
0
 
nmarunCommented:
Your sql query could look something like:

SELECT TOP 1 QuestionId, QuestionText
FROM Question
WHERE SurveyId = @surveyId
ORDER BY QuestionId

Arun
0
 
nmarunCommented:
This query can be inside a stored procedure and @surveyId could be a parameter for the procedure.

Arun
0
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.

All Courses

From novice to tech pro — start learning today.