VS 2010 Help in assigning database value to array value-VBForums
Results 1 to 5 of 5

Thread: Help in assigning database value to array value

  1. #1

    Thread Starter
    Member
    Join Date
    Feb 2011
    Posts
    58

    Help in assigning database value to array value

    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!

  2. #2
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,204

    Re: Help in assigning database value to array value

    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

  3. #3

    Thread Starter
    Member
    Join Date
    Feb 2011
    Posts
    58

    Re: Help in assigning database value to array value

    Quote Originally Posted by digitalShaman View Post
    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.

  4. #4
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    31,107

    Re: Help in assigning database value to array value

    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:
    Code:
    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.
    My usual boring signature: Nothing

  5. #5

    Thread Starter
    Member
    Join Date
    Feb 2011
    Posts
    58

    Re: Help in assigning database value to array value

    Name:  DB.jpg
Views: 25
Size:  22.1 KB

    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!!!
    Last edited by Serigraphie; Mar 2nd, 2015 at 12:15 PM. Reason: additional notes

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.