phil652 phil652 - 7 months ago 42
Vb.net Question

Visual Basic quiz 1 questions is always wrong no matter what

To make it short, I have a database with questions, possible answers and good answers. I take 10 randoms questions from the database to create a multiple answer quiz in vb.net. My problem is that the Question with the ID_Question 7 is never right. Everything works for the other questions.
This is my database
Relationships
Sorry for censorship this information is not supposed to be shared
Possible Answer
Questions

This is my code use for this part(Code might not be needed, it might just be a problem in access)

This is my variables

'Variables for connection to database
Dim provider As String
Dim dataFile As String
Dim connString As String
Public myConnection As OleDbConnection = New OleDbConnection
Public dr As OleDbDataReader
Public dr2 As OleDbDataReader
Public dr3 As OleDbDataReader

'Array of RadioButtons
Dim RadioArray1() As RadioButton = {Q1a, Q1b, Q1c, Q1d, Q1e}
Dim RadioArray2() As RadioButton = {Q2a, Q2b, Q2c, Q2d, Q2e}
Dim RadioArray3() As RadioButton = {Q3a, Q3b, Q3c, Q3d, Q3e}
Dim RadioArray4() As RadioButton = {Q4a, Q4b, Q4c, Q4d, Q4e}
Dim RadioArray5() As RadioButton = {Q5a, Q5b, Q5c, Q5d, Q5e}
Dim RadioArray6() As RadioButton = {Q6a, Q6b, Q6c, Q6d, Q6e}
Dim RadioArray7() As RadioButton = {Q7a, Q7b, Q7c, Q7d, Q7e}
Dim RadioArray8() As RadioButton = {Q8a, Q8b, Q8c, Q8d, Q8e}
Dim RadioArray9() As RadioButton = {Q9a, Q9b, Q9c, Q9d, Q9e}
Dim RadioArray10() As RadioButton = {Q10a, Q10b, Q10c, Q10d, Q10e}


When the Form load

Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load
'Set up connection to databse, change path depending on location
provider = "Provider=Microsoft.Jet.OLEDB.4.0;"
dataFile = "Data Source=F:\Quiz\Programs\UNZipped\questions.mdb;Jet OLEDB:Database Password=magic;"

connString = provider & dataFile
myConnection.ConnectionString = connString
'Open connection
myConnection.Open()

Dim str As String
Dim str2 As String
Dim str3 As String
'Select 10 random questions
str = "SELECT TOP 10 ID_Question, Question From Questions ORDER BY Rnd(-(100000*ID_Question)*Time())"
Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
dr = cmd.ExecuteReader


Dim idArray(9) As Integer
Dim QuestionArray(9) As String
Dim LabelArray() As Label = {Label3, Label4, Label5, Label6, Label7, _
Label8, Label9, Label10, Label11, Label12}

'Dim RadioArray As List(Of RadioButton)



Dim PossibleAnswerList As List(Of String) = New List(Of String)


Dim total As Integer = 0
Dim cnt As Integer = 0
While dr.Read()
'Add id and question title into arrays
idArray(cnt) = dr.GetInt32(dr.GetOrdinal("ID_Question"))
QuestionArray(cnt) = dr("Question").ToString

Dim num As Integer 'numbers of possible answers

str2 = "SELECT Possible_Answer From PossibleAnswers Where ID_Question =" & idArray(cnt) & ""
Dim cmd2 As OleDbCommand = New OleDbCommand(str2, myConnection)
dr2 = cmd2.ExecuteReader

str3 = "SELECT Count(Possible_Answer) From PossibleAnswers Where ID_Question =" & idArray(cnt) & ""
Dim cmd3 As OleDbCommand = New OleDbCommand(str3, myConnection)
num = Convert.ToInt32(cmd3.ExecuteScalar)

While dr2.Read()
'Put all the possible answer of all the selected questions into a list
PossibleAnswerList.Add(dr2("Possible_Answer").ToString)


End While

Select Case cnt

Case 0 'Question 1
For i = 0 To num - 1
'Change text on Radio Button
RadioArray1(i).Text = PossibleAnswerList(total)
total = total + 1
Next
Case 1 'Question 2
For i = 0 To num - 1
RadioArray2(i).Text = PossibleAnswerList(total)
total = total + 1

Next i
Case 2 'Question 3
For i = 0 To num - 1
RadioArray3(i).Text = PossibleAnswerList(total)
total = total + 1

Next i
Case 3 'Question 4
For i = 0 To num - 1
RadioArray4(i).Text = PossibleAnswerList(total)
total = total + 1

Next i
Case 4 'Question 5
For i = 0 To num - 1
RadioArray5(i).Text = PossibleAnswerList(total)
total = total + 1

Next i
Case 5 'Question 6
For i = 0 To num - 1
RadioArray6(i).Text = PossibleAnswerList(total)
total = total + 1

Next i
Case 6 'Question 7
For i = 0 To num - 1
RadioArray7(i).Text = PossibleAnswerList(total)
total = total + 1

Next i
Case 7 'Question 8
For i = 0 To num - 1
RadioArray8(i).Text = PossibleAnswerList(total)
total = total + 1

Next i
Case 8 'Question 9
For i = 0 To num - 1
RadioArray9(i).Text = PossibleAnswerList(total)
total = total + 1

Next i
Case 9 'Question 10
For i = 0 To num - 1
RadioArray10(i).Text = PossibleAnswerList(total)
total = total + 1

Next i
End Select



cnt += 1
End While
'Put the Questions text on the Label(runs 10 times)
For i = 0 To QuestionArray.Length - 1
LabelArray(i).Text = QuestionArray(i)

Next i







myConnection.Close()

End Sub


When submit is clicked

Private Sub ButtonSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonSubmit.Click

Dim cnnOLEDB As New OleDbConnection

Dim cmdOLEDB As New OleDbCommand

Dim cnnOLEDB2 As New OleDbConnection

Dim cmdOLEDB2 As New OleDbCommand

Dim strConnectionString2 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = F:\Quiz\Programs\UNZipped\questions.mdb;Jet OLEDB:Database Password=magic;"
cnnOLEDB2.ConnectionString = strConnectionString2
'Declares a variable that counts the number of correct answers
Dim score As Short = 0
'Repeat this part 10 times for every different questions
For i = 0 To RadioArray1.Length - 1
'Find which button is selected
If RadioArray1(i).Checked = True Then

'Set up connection to databse, change path depending on location
provider = "Provider=Microsoft.Jet.OLEDB.4.0;"
dataFile = "Data Source=F:\Quiz\Programs\UNZipped\questions.mdb;Jet OLEDB:Database Password=magic;"

connString = provider & dataFile
myConnection.ConnectionString = connString
'Open connection
myConnection.Open()

Dim str4 As String
Dim good As Int16

'Check if answer is right
str4 = "SELECT Good_Answer From PossibleAnswers where Possible_Answer='" & RadioArray1(i).Text & "'"
Dim cmd4 As OleDbCommand = New OleDbCommand(str4, myConnection)
good = Convert.ToInt16(cmd4.ExecuteScalar)
End sub


Note: the last part is repeated 10 times to verify all 10 questions I just did not want to past to much code
Thank you

Answer

Your code searches a match in the Possible_Answers table using a string and it is not limiting the search to the current ID_Question.

If the table contains two records with the same answer text, it is highly probable that that query matches an answer intendend for a different question.

So the fix is relatively easy. When you query for the good_answer you need to add a WHERE condition also for the ID_Question....

str4 = "SELECT Good_Answer From PossibleAnswers " & _
       "where Possible_Answer='" & RadioArray1(i).Text & "' " & _
       "AND ID_Question = ?????"

Now the problem is just how to resolve the ID_Question when you are inside the button to submit the answer. This could be solved setting the Tag property of your Radiobutton array with the ID of the question.

Form_Load
.... 
   While dr.Read()
      'Add id and question title into arrays
      idArray(cnt) = dr.GetInt32(dr.GetOrdinal("ID_Question"))
      .....
      Select Case cnt
        Case 0 'Question 1 
            For i = 0 To num - 1
                'Change text on Radio Button
                RadioArray1(i).Text = PossibleAnswerList(total)
                RadioArray1(i).Tag = idArray(cnt)
                total = total + 1
            Next 
      ....

Now the final query could be rewritten using the Tag property

str4 = "SELECT Good_Answer From PossibleAnswers " & _
       "where Possible_Answer='" & RadioArray1(i).Text & "' " & _
       "AND ID_Question = " & RadioArray1(i).Tag

A final note. I have followed your style in writing this answer. But I should really warn you that concatenating strings to build command text is considered the GOTO of Database Programming. You should never use this method. Use always a parameterized query approach to avoid Sql Injection and parsing problems