Help in assigning database value to array value

[aplee]

Hello, Code Masters,

Here I am again today going to seek help and guidance from you.

I have here a manually assigned values to arrays 0 - 19
now my question is, how can I assign values to each array coming from the SQL database?

The catch is, it is a different SQL statement (specifically a different where clause) for each array value. For example:

Dim answers(19) As String

answers(0) = RbAnswers1.SelectedValue
answers(1) = RbAnswers2.SelectedValue
answers(2) = RbAnswers3.SelectedValue
answers(3) = RbAnswers4.SelectedValue
answers(4) = RbAnswers5.SelectedValue
answers(5) = RbAnswers6.SelectedValue
answers(6) = RbAnswers7.SelectedValue
answers(7) = RbAnswers8.SelectedValue
answers(8) = RbAnswers9.SelectedValue
answers(9) = RbAnswers10.SelectedValue
answers(10) = RbAnswers11.SelectedValue
answers(11) = RbAnswers12.SelectedValue
answers(12) = RbAnswers13.SelectedValue
answers(13) = RbAnswers14.SelectedValue
answers(14) = RbAnswers15.SelectedValue
answers(15) = RbAnswers16.SelectedValue
answers(16) = RbAnswers17.SelectedValue
answers(17) = RbAnswers18.SelectedValue
answers(18) = RbAnswers19.SelectedValue
answers(19) = RbAnswers20.SelectedValue

(This one below is what I need help with)

Dim CorrectAnswer(19) As String

CorrectAnswer(0) = "A"
CorrectAnswer(1) = "A"
CorrectAnswer(2) = "A"
CorrectAnswer(3) = "A"
CorrectAnswer(4) = "A"
CorrectAnswer(5) = "A"
CorrectAnswer(6) = "A"
CorrectAnswer(7) = "A"
CorrectAnswer(8) = "A"
CorrectAnswer(9) = "A"
CorrectAnswer(10) = "A"
CorrectAnswer(11) = "A"
CorrectAnswer(12) = "A"
CorrectAnswer(13) = "A"
CorrectAnswer(14) = "A"
CorrectAnswer(15) = "A"
CorrectAnswer(16) = "A"
CorrectAnswer(17) = "A"
CorrectAnswer(18) = "A"
CorrectAnswer(19) = "A"

Dim score As Integer = 0

For i As Integer = 0 To answers.Length - 1
    If answers(i) = CorrectAnswer(i) Then
        score += 1
    End If
Next

lbCorrectAnswerCount.Text = score / 20 * 100

What I want to happen is change the manual assignment of array to getting it from the DB:

Dim CorrectAnswer(19) As String

CorrectAnswer(0) = "A"
CorrectAnswer(1) = "A"
CorrectAnswer(2) = "A"
CorrectAnswer(3) = "A"

to :

CorrectAnswer(0) = Select AnswerValue from tbl_ECM_Questionnaire where QuestionID = 1 and CorrectAnswer = 'Y'"
CorrectAnswer(1) = Select AnswerValue from tbl_ECM_Questionnaire where QuestionID = 2 and CorrectAnswer = 'Y'"

CorrectAnswer(2) = Select AnswerValue from tbl_ECM_Questionnaire where QuestionID = 3 and CorrectAnswer = 'Y'"

and so on...

I did try the data table approach however, my poor programming skills hiner me from achieving the result that I want.
Thank you in advance!

[Teme64]

Do you design that database structure too or is it 'fixed'? I assume that you are having only multiple choise questions.

The database structure should be similar to this:

tbl_ECM_Questionnaire:
QuestionID  QuestionText    CorrectQuestionOptionID
1           "Why..."        2
2           "Where..."      1
3           "When...        2



tbl_ECM_QuestionnaireOptions:
QuestionID  QuestionOptionID    QuestionOptionText  AnswerValue
1           1                   "Because..."        "A"
1           2                   "Because..."        "B"
2           1                   "In..."             "A"
2           2                   "In..."             "B"
3           1                   "It was...          "A"
3           2                   "It was...          "B"

Table tbl_ECM_Questionnaire has the questions, each with an unique QuestionID, and a column for the correct answer option.

The second table tbl_ECM_QuestionnaireOptions has answer options. Table's unique key is the combination of the QuestionID and QuestionOptionID.

Tables are related in the following way:
1. QuestionID field makes a relation between a question and all the question's answer choises (QuestionOptionID).
2. Each question's QuestionOptionID (in the latter table) is unique. One of the QuestionOptionID is the correct answer and that correct QuestionOptionID can be found from the CorrectQuestionOptionID field in the questions table.

Here is how you get all the question's answer options for the question number one:

"SELECT QuestionOptionText, AnswerValue FROM tbl_ECM_Questionnaire, tbl_ECM_QuestionnaireOptions WHERE tbl_ECM_Questionnaire.QuestionID = tbl_ECM_QuestionnaireOptions.QuestionID AND tbl_ECM_Questionnaire.QuestionID = 1"

Here is how you get the correct answer option for the question number one:

"SELECT AnswerValue FROM tbl_ECM_Questionnaire, tbl_ECM_QuestionnaireOptions WHERE tbl_ECM_Questionnaire.QuestionID = tbl_ECM_QuestionnaireOptions.QuestionID AND tbl_ECM_Questionnaire.QuestionID = 1 AND tbl_ECM_Questionnaire.CorrectQuestionOptionID = tbl_ECM_QuestionnaireOptions.QuestionOptionID"

This doesn't answer your question about how to get something from the database to an array. I just wanted to point out that once the database structure is well designed, the coding itself should be an easy task :)

HTH