locked
ASP.NET VB - Questionaire Script - Access Table INNER JOIN Issues RRS feed

  • Question

  • User-1922837171 posted

    HI,

    I hope some can help what sounds complicated but actually isn't ...  think

    I am writng a quiz creator for a school, so the teacher is presented with a simple form where they can create questionaires with YES /NO answrs or "Never" , "Sometimes" , "Always" answers, ets  in the form they have a dropdown for the type of answer they want, so either YN or NSA. So I am trying to link up the Question with the true values so if the user creates a question

    1. Is the sun hot and choose answer YN I want to be able to pull out of the database "Is the Sun hot" "Yes" , "NO" to pass to an XML doc

    I have one database called "Questions" in that table I have 2 tables - "Answers" and "Question"

    The Answers Table contains Values for drop down

    ID / ANSWER_ID / ANSWER

    With Values

    1 / YN / Yes

    2/ YN / No

    3/ NSA / Never

    4 / NSA / Sometimes

    5 / NSA / Always

     

    Question Table -- Holds the questions and type of Answer

    ID / Question_ID / Question / Answer

    1 / Sport / Do you like Football / YN

    2 /Sport/ Do you Play Sport / NSA

     

    When I try and write a query I just end Up with multiple questions with the ansers:

     

    NODo You Like Football

    YESDoYou Like Football

    But I wanted

    Do You ike Football

    Yes

    No

    My Code is as follows :

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
            Dim ConnString As String = "provider=Microsoft.Jet.OleDb.4.0;Data Source=\App_Data\question.mdb"
            Dim SqlString As String = "SELECT ANSWERS.ANSWER,QUESTION.QUESTION FROM (ANSWERS INNER JOIN QUESTION ON ANSWERS.ANSWER_ID = QUESTION.ANSWERS)"
            Dim SqlString2 As String = "SELECT [ANSWER_ID], [ANSWER] FROM [ANSWERS])"
            Using conn As New OleDbConnection(ConnString)
                Using cmd As New OleDbCommand(SqlString, conn)
                    cmd.CommandType = CommandType.Text
                    conn.Open()
                    Using reader As OleDbDataReader = cmd.ExecuteReader()
                        While reader.Read()
                            Dim QA = ""
                            Dim AA = ""
                            QA = (reader("ANSWER").ToString())
                            AA = (reader("Question").ToString())
                            ' Response.Write(reader("ANSWER").ToString())
                            Response.Write(QA & AA & "<br>")
    
                        End While
    
                    End Using
    
                End Using
    
            End Using

     

     

    Thanks

    Andy

    Thursday, September 8, 2011 5:27 PM

Answers

  • User-1226263862 posted

    Sounds like you need more than 2 tables.  Check this out:

    QuestionTable

    • QuestionId
    • Question
    • QuestionTypeId
    • SortOrder
    • QuestionChoices (comma separated string of possible choices for MultipleChoice_RadioButton or other types. Example "Never,SomeTimes,Always" or "Yes,No" or "True,False")
    • CorrectAnswer (not needed if there isn't one)

    QuestionTypes

    • QuestionTypeId
    • QuestionType (YesNo,MultipleChoice_RadioButton,MultipleChoice_Checkbox,Text)
    • QuestionTypeFriendlyText(Yes/No, Multiple Choice Unique, Multiple Choice, Text Only)

    Answers

    • AnswerId
    • QuestionId
    • Answer
    • UserId

     

    So your UI would consist of a few textboxes and a dropdown list of question types that you would insert into the QuestionTable.  Then when you dynamically draw the Questions out of the QuestionTable you can dynamically create the control based on the QuestionType and set the text and values based on the QuestionChoices.  Below is some sample code for the dynamic creation of controls based on the QuestionType:

    switch (question.QuestionTypeId)
                    {
                        case (int)QuestionTypeEnum.Text:
                            // create a textbox control
                            TextBox txtbx = new TextBox();
                            txtbx.ID = "txtQuestion";
                            txtbx.TextMode = TextBoxMode.MultiLine;
                            txtbx.CssClass = "txtAnswer";
                            placeholder.Controls.Add(txtbx);
                            break;
                        case (int)QuestionTypeEnum.MultipleChoice_RadioButton:
                            // Create a radio button list
                            RadioButtonList rblQuestions = new RadioButtonList();
                            rblQuestions.ID = "rblQuestion";
                            rblQuestions.RepeatDirection = RepeatDirection.Vertical;
                            rblQuestions.RepeatLayout = RepeatLayout.Table;
                            String[] questions = question.QuestionChoices.Split(splitter);
                            foreach (String mcQuestion in questions)
                            {
                                ListItem li = new ListItem(mcQuestion, mcQuestion);
                                rblQuestions.Items.Add(li);
                            }
                            placeholder.Controls.Add(rblQuestions);
                            break;
                        case (int)QuestionTypeEnum.TrueOrFalse:
                            // Create a radio button list
                            RadioButtonList chklQuestions = new RadioButtonList();
                            chklQuestions.ID = "rblTorFQuestion";
                            chklQuestions.RepeatDirection = RepeatDirection.Horizontal;
                            chklQuestions.RepeatLayout = RepeatLayout.Flow;
                            String[] questionsCBL = question.QuestionChoices.Split(splitter);
                            foreach (String mcQuestion in questionsCBL)
                            {
                                ListItem li = new ListItem(mcQuestion, mcQuestion);
                                chklQuestions.Items.Add(li);
                            }
                            placeholder.Controls.Add(chklQuestions);
                            break;
                        case (int)QuestionTypeEnum.MultipleChoice_Checkbox:
                            // Create a checkbox list
                            CheckBoxList chklQuestionsMA = new CheckBoxList();
                            chklQuestionsMA.ID = "chkblQuestion";
                            chklQuestionsMA.RepeatDirection = RepeatDirection.Vertical;
                            chklQuestionsMA.RepeatLayout = RepeatLayout.Table;
                            String[] questionsMA = question.QuestionChoices.Split(splitter);
                            foreach (String mcQuestion in questionsMA)
                            {
                                ListItem li = new ListItem(mcQuestion, mcQuestion);
                                chklQuestionsMA.Items.Add(li);
                            }
    
                            placeholder.Controls.Add(chklQuestionsMA);
                            break;
                        default:
                            break;
                    }
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 8, 2011 11:24 PM

All replies

  • User-1226263862 posted

    Sounds like you need more than 2 tables.  Check this out:

    QuestionTable

    • QuestionId
    • Question
    • QuestionTypeId
    • SortOrder
    • QuestionChoices (comma separated string of possible choices for MultipleChoice_RadioButton or other types. Example "Never,SomeTimes,Always" or "Yes,No" or "True,False")
    • CorrectAnswer (not needed if there isn't one)

    QuestionTypes

    • QuestionTypeId
    • QuestionType (YesNo,MultipleChoice_RadioButton,MultipleChoice_Checkbox,Text)
    • QuestionTypeFriendlyText(Yes/No, Multiple Choice Unique, Multiple Choice, Text Only)

    Answers

    • AnswerId
    • QuestionId
    • Answer
    • UserId

     

    So your UI would consist of a few textboxes and a dropdown list of question types that you would insert into the QuestionTable.  Then when you dynamically draw the Questions out of the QuestionTable you can dynamically create the control based on the QuestionType and set the text and values based on the QuestionChoices.  Below is some sample code for the dynamic creation of controls based on the QuestionType:

    switch (question.QuestionTypeId)
                    {
                        case (int)QuestionTypeEnum.Text:
                            // create a textbox control
                            TextBox txtbx = new TextBox();
                            txtbx.ID = "txtQuestion";
                            txtbx.TextMode = TextBoxMode.MultiLine;
                            txtbx.CssClass = "txtAnswer";
                            placeholder.Controls.Add(txtbx);
                            break;
                        case (int)QuestionTypeEnum.MultipleChoice_RadioButton:
                            // Create a radio button list
                            RadioButtonList rblQuestions = new RadioButtonList();
                            rblQuestions.ID = "rblQuestion";
                            rblQuestions.RepeatDirection = RepeatDirection.Vertical;
                            rblQuestions.RepeatLayout = RepeatLayout.Table;
                            String[] questions = question.QuestionChoices.Split(splitter);
                            foreach (String mcQuestion in questions)
                            {
                                ListItem li = new ListItem(mcQuestion, mcQuestion);
                                rblQuestions.Items.Add(li);
                            }
                            placeholder.Controls.Add(rblQuestions);
                            break;
                        case (int)QuestionTypeEnum.TrueOrFalse:
                            // Create a radio button list
                            RadioButtonList chklQuestions = new RadioButtonList();
                            chklQuestions.ID = "rblTorFQuestion";
                            chklQuestions.RepeatDirection = RepeatDirection.Horizontal;
                            chklQuestions.RepeatLayout = RepeatLayout.Flow;
                            String[] questionsCBL = question.QuestionChoices.Split(splitter);
                            foreach (String mcQuestion in questionsCBL)
                            {
                                ListItem li = new ListItem(mcQuestion, mcQuestion);
                                chklQuestions.Items.Add(li);
                            }
                            placeholder.Controls.Add(chklQuestions);
                            break;
                        case (int)QuestionTypeEnum.MultipleChoice_Checkbox:
                            // Create a checkbox list
                            CheckBoxList chklQuestionsMA = new CheckBoxList();
                            chklQuestionsMA.ID = "chkblQuestion";
                            chklQuestionsMA.RepeatDirection = RepeatDirection.Vertical;
                            chklQuestionsMA.RepeatLayout = RepeatLayout.Table;
                            String[] questionsMA = question.QuestionChoices.Split(splitter);
                            foreach (String mcQuestion in questionsMA)
                            {
                                ListItem li = new ListItem(mcQuestion, mcQuestion);
                                chklQuestionsMA.Items.Add(li);
                            }
    
                            placeholder.Controls.Add(chklQuestionsMA);
                            break;
                        default:
                            break;
                    }
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 8, 2011 11:24 PM
  • User-1922837171 posted

    Hi,

    Thanks so much for your help, I will rework it as you suggested

    Much Better Plan !

     

    Andy

    Friday, September 9, 2011 2:12 AM