Help in assigning database value to array value

[Serigraphie]

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:

CorrectAnswer(0) = select data where fieldValue = 1
CorrectAnswer(1) = select data where fieldValue = 2
CorrectAnswer(2) = select data where fieldValue = 3
CorrectAnswer(3) = select data where fieldValue = 4


and so on..

Any additional inputs will be greatly appreciated.

I tried by the way the use of data table however I am not sure how to do it because of the condition (different where clause for each array value).

Hoping you are able to get my meaning.

Thank you!

[digitalShaman]

if it is really like what you posted and you want to stick with the array you can "SELECT fieldValue,data" (no Where Clause) and then use somthing like that:
CorrectAnswer(cint(dr("fieldValue"))-1)=dr("data")
and put this in a loop for all rows that the database returned. this will assign the data to the array index determined by the fieldValue-1

[Serigraphie]

if it is really like what you posted and you want to stick with the array you can "SELECT fieldValue,data" (no Where Clause) and then use somthing like that:
CorrectAnswer(cint(dr("fieldValue"))-1)=dr("data")
and put this in a loop for all rows that the database returned. this will assign the data to the array index determined by the fieldValue-1

Hi Sir,
so for example I have the following queries:
Select AnswerValue from tbl_ECM_Questionnaire where QuestionID = 1 and CorrectAnswer = 'Y'"
Select AnswerValue from tbl_ECM_Questionnaire where QuestionID = 2 and CorrectAnswer = 'Y'"
Select AnswerValue from tbl_ECM_Questionnaire where QuestionID = 3 and CorrectAnswer = 'Y'"

Should I make it to be like this?

"SELECT AnswerValue,1"

And in my for loop

CorrectAnswer(cint(dr("AnswerValue"))-1)=dr("1")

My apologies, as I have very poor programming foundation.

[Shaggy Hiker]

It would be more like this:

SELECT AnswerValue, QuestionID FROM tbl_ECM_Questionnaire

However, that wouldn't address teh CorrectAnswer part of the query, and that makes it a bit more complicated. I assume that CorrectAnswer is either Y, N, and possibly Null, but what is QuestionID? It seems likely that it is a unique value, such that there is only one record with QuestionID = 1, in which case your query simply won't return a record if CorrectAnswer is not 'Y'. The alternative is that QuestionID is not unique, in which case there could be a record with QuestionID =1 and CorrectAnswer = 'Y', with another record with QuestionID = 1 and CorrectAnswer = 'N'.

In the latter case, the query you want would be like this:

SELECT AnswerValue, QuestionID FROM tbl_ECM_Questionnaire WHERE CorrectAnswer = 'Y'

and the array would be filled with something like this:

Do While YourDataReader.Read
 CorrectAnswer(CInt(dr("QuestionID"))-1) = dr("AnswerValue")
Loop

The idea is that the QuestionID serves as the array index, but since QuestionID starts at 1 and the array is 0 based, you have to subtract 1 from the QuestionID to get the proper array index.

If QuestionID is actually a unique value, then this won't work unless CorrectAnswer is ALWAYS 'Y', which would be pretty weird.

[Serigraphie]

Hi Shaggy,

Thank you for chiming in. CorrectAnswer is actually the CorrectAnswer value stored in the DB for a particular question so no it cannot be Null. I am storing the correctAnswer value in an array so I can perform a comparison between the user's answer versus the correct answer stored in the DB. Here is my code for comparing answers:

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


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

This way, I am able to get the correct computation of the user's correct answer, by assigning the correct answer value in the array. However that is not the way it should go, it should get the correct answer from the DB so the admin can manipulate and change the answers over time.


Also, QuestionID is not unique. The question ID varies per question. For example, for question 1, the QuestionID is 1, and there are multiple question answers (4 choices) associated for each question. for Question2, QuestionID is 2 and so on...

I have attached sample screenshot of the table for your reference.

THank you!!!