Question Spelling program with linking back to the database

Yhym

Member
Joined
Dec 8, 2013
Messages
7
Programming Experience
Beginner
(VB.NET 2010 & Access 2010)
First of all, I just want to say that I'm new to VB and if some of those things are easy to do and I'm asking for help with them, then now you know why. Anyway, my task is to create a program that will help students with their spelling. The program has to do these things:

  • Allow users to log in either as staff and/or pupils (DONE)
  • Allow staff to add words and definitions to the system (DONE)
  • Display a ten question spelling test (DONE)
  • Calculate and display a student's score at the end of the test
  • Store pupils' score (In the database that I've already created. It includes all the login details, words, definitions, etc.)
  • Provide analysis of pupils' progress
  • And also, the system should score each of the ten words using the following rules: ? If the pupil?s spelling is correct - 2 marks (Every single letter correct) ? If there is a minor error - 1 mark (1 letter wrong) ? If there is a major error - 0 mark (More than 1 letter wrong)
Basically, I need help with all the things that aren't marked as 'DONE' and what I would like you guys to help me with is to just show me how to do these things. It's hard to explain what I actually need but I hope that at least some of you know what I need. If you have any questions then I'll do my best to answer them! Thanks in advance for all the help!
Example of how you could help me. (For those of you who don't know what I mean.) For example you choose to help me with this: - Calculate and display a student's score at the end of the test. By telling me what to do or by showing me the code. But as I said earlier, if you have any questions about anything then just ask! ;)
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,717
Location
Sydney, Australia
Programming Experience
10+
Calculate and display a student's score at the end of the test
Calculating it is easy. You simply declare a variable and initialise it to zero and add the appropriate value to it after each question. As for display, you convert that numeric value to a String and display it how you would any other String. The details pened on exactly how you want it displayed, e.g. Label, MessageBox or what. It's a few seconds' work to find examples of any of those on the web though.
Store pupils' score (In the database that I've already created. It includes all the login details, words, definitions, etc.)
It's exactly like storing anything else in the database. You create a table with columns for the data you want to store, which will include the score, the student and whatever else is appropriate, and then you insert a record. If you have specific data access questions then you should ask those but storing data in a database is basically the same no matter the data or the database.
Provide analysis of pupils' progress
What progress? Within a single test or over time? What analysis? You're expecting us to guess a lot there.
the system should score each of the ten words using the following rules: ? If the pupil?s spelling is correct - 2 marks (Every single letter correct) ? If there is a minor error - 1 mark (1 letter wrong) ? If there is a major error - 0 mark (More than 1 letter wrong)
You need to put some thought into that before you do the design. It's the sort of thing that lots of people assume is easy without considering all the possibilities. For instance, if the word and the input are the same length then it's just a matter of comparing characters at corresponding indexes in the two Strings. What if the the input is not the right length though? If there's a missing letter or extra letter then what does that mean for the letters that follow? Human beings can do things like spot a missing letter in a word very easily but computers don't have that ability. They need a set of concrete rules to follow and they just apply those rules one by one. You should pick up a pen and paper and do some tests as though you were a computer to work out what the rules are. Only then can you implement those rules in code.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,717
Location
Sydney, Australia
Programming Experience
10+
It's hard to explain what I actually need but I hope that at least some of you know what I need.
I'm going to have to pull you up on this too. When people say that their issue is hard to explain, it generally isn't. It's your project and, while you may not be an experienced developer, you are a computer user and you know how Windows programs work. Are you really saying that you don't understand how your own application is supposed to work? If so then I suggest that you spend a bit more time thinking about that. If you do know how it's supposed to work then put a bit of time and effort into describing it to us, who have no prior knowledge and yet are just supposed to know things about it that you don't. I'm quite sure that you can do something like:
The user starts the program and a form appears. That form contains two TextBoxes, each with a descriptive Label beside them, a ListBox and a Button. The ListBox contains data from the X table in the database. The user selects an X value, enters Y and Z values into the TextBoxes and then clicks the Button. Etc.
You see how easy it is?

I'm not trying to have a go at you. You're certainly not the only person to post in this way but I want you to see that, if you want us to volunteer our time and effort to help you, it's in your best interests to spend your time and effort to make that as easy as possible. If we have to spend lots of time dragging information out of you then we will quite likely lose interest and you won't get the help you need. You have said that you will answer questions but it's often difficult for us to know what questions to ask if all we have is a vague description of the problem. It's also a waste of our time to ask you to provide information that you could/should have provided up front. I know that it can be difficult for you to know what's relevant but your comment above suggests that you know that your description is inadequate. Our time is valuable and, while we're happy to volunteer it to point, we're not here to be taken advantage of. While you might ask one question a week or even less, the people who answer questions often answer a hundred in that time. If you can make it easier for them then they're more likely to help you and others.

:)
 

Yhym

Member
Joined
Dec 8, 2013
Messages
7
Programming Experience
Beginner
Thanks for the help and I'll will try to make it clearer now.
I guess I'll just start with all of my forms, how they are linked to each other, what they do, (or what they are supposed to do) etc.

Main form- Splash screen, takes you to the login form. (No problems with that so no need to go into further details here)

Login form- Includes a text box where you can type in the username, a text box where you can type in the password, a button that will allow you to log in as a teacher and an appropriate form for that will show up(StaffMenu) and a button that will allow you to log in as a student and an appropriate form for that will show up(StudentMenu). I don't have any problems with this form just showing it so you can understand everything easier, later.
All the code that I have for this form:
Public Class frmLogin


    Private Sub cmdLoginT_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLoginT.Click
        ' Checks whether username or password field is empty
        If txtPass.Text = "" Or txtUsername.Text = "" Then
            MessageBox.Show("Please complete the required fields.", "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            ' If Both fields were filled in, then check whether the user exists in the database
            ' Connect to the database
            Dim Conn As New System.Data.OleDb.OleDbConnection()
            Conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database for VB.accdb"


            'Try
            ' Conn.Open()


            Dim SQL As String = "SELECT * FROM UserTable WHERE username='" & txtUsername.Text & "' AND password = '" & txtPass.Text & "'"
            Dim SQLComm As New System.Data.OleDb.OleDbCommand(SQL)


            ' Open Database Connection
            SQLComm.Connection = Conn
            Conn.Open()


            Dim SQLRdr As System.Data.OleDb.OleDbDataReader = SQLComm.ExecuteReader()
            ' Makes sure that you can't login as staff to the student area and that you can't login as student to the staff area 
            If SQLRdr.Read() Then
                If (SQLRdr.Item("Position")) = "Staff" Then
                    MsgBox("You have successfully logged in!")
                    ' After successfully logging in, the 'Staff Menu' form will show up
                    frmStaffMenu.Show()
                    Me.Close()
                ElseIf (SQLRdr.Item("Position")) = "Student" Then
                    MsgBox("You don't have access to this area!")
                    txtPass.Text = ""
                    txtUsername.Text = ""
                    End If


            Else
                ' If a user has entered wrong username and/or password combination then an error message will come up
                    MessageBox.Show("Username and Password do not match.", "Authentication Failure", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)


                ' Clear all fields so the user can quickly enter the details again
                    txtPass.Text = ""
                    txtUsername.Text = ""


                    ' Focus on the 'Username' field
                    txtUsername.Focus()
            End If


            'Catch ex As Exception
            'MessageBox.Show("Failed to connect to Database.", "Database Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            'End Try


        End If




    End Sub


    Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
        ' User clicking on clear button will clear all the fields and refocus to the 'Username' field to make everything easier and quicker
        txtUsername.Text = ""
        txtPass.Text = ""
        txtUsername.Focus()


    End Sub


    Private Sub cmdLoginS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLoginS.Click
      
        ' Checks whether username and/or password fields are empty
        If txtPass.Text = "" Or txtUsername.Text = "" Then
            MessageBox.Show("Please complete the required fields.", "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            ' If Both fields were filled in, then check whether the user exists in the database
            ' Connect to the database
            Dim Conn As New System.Data.OleDb.OleDbConnection()
            Conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database for VB.accdb"


            '    Try
            ' Conn.Open()




            Dim SQL As String = "SELECT * FROM UserTable WHERE username='" & txtUsername.Text & "' AND password = '" & txtPass.Text & "'"
            Dim SQLComm As New System.Data.OleDb.OleDbCommand(SQL)


            ' Open Database Connection
            SQLComm.Connection = Conn
            Conn.Open()


            Dim SQLRdr As System.Data.OleDb.OleDbDataReader = SQLComm.ExecuteReader()
            ' Makes sure that you can't login as staff to the student area and that you can't login as student to the staff area 
            If SQLRdr.Read() Then
                If (SQLRdr.Item("Position")) = "Student" Then
                    MsgBox("You have successfully logged in!")
                    ' After successfully logging in, the 'Student Menu' form will show up
                    frmStudentMenu.Show()
                    Me.Close()
                ElseIf (SQLRdr.Item("Position")) = "Staff" Then
                    MsgBox("You don't have access to this area!")
                    txtPass.Text = ""
                    txtUsername.Text = ""
            End If
            Else


                ' If a user has entered wrong username and/or password combination then an error message will come up
                MessageBox.Show("Username and Password do not match.", "Authentication Failure", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)


                ' Clear all fields so the user can quickly enter the details again
                txtPass.Text = ""
                txtUsername.Text = ""


                ' Focus on the 'Username' field
                txtUsername.Focus()
            End If


            'Catch ex As Exception
            '    MessageBox.Show("Failed to connect to Database.", "Database Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            'End Try


        End If
    End Sub


    Private Sub frmLogin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


    End Sub
    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        ' When a user will press the exit button, a message box will appear asking whether they want to close the program and if a user clicks 'Yes' then 
        ' the whole program will close but if they click 'No' then the message box will simply dissapear 
        Dim Result As DialogResult
        Result = MessageBox.Show("Are you sure you wish to close the program?", "Close program?", MessageBoxButtons.YesNo)
        If Result = Windows.Forms.DialogResult.Yes Then
            ' Makes sure that the program will actually fully close
            Application.Exit()
        Else
        End If
    End Sub
End Class

Staff menu form- A simple form with which you can easily be directed to any other form you want. In this case, 'Leaderboards'(Doesn't matter for now), 'Create test' and 'Check individual progress'(This form is supposed to allow the teacher to give some kind of comment after each test that the student has completed. I haven't created a form for that yet because there are other, more important things to do first, such as calculating and saving score as well as how to differentiate whether a student has done a major or minor mistake.)
Code:
Public Class frmStaffMenu


    Private Sub frmStaffMenu_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


    End Sub


    Private Sub btnLeader_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLeader.Click
        ' Once the button is clicked, 'Leaderboards' form will show up
        frmLeaderboards.Show()
    End Sub


    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        ' When a user will press the exit button, a message box will appear asking whether they want to close the program and if a user clicks 'Yes' then 
        ' the whole program will close but if they click 'No' then the message box will simply dissapear 
        Dim Result As DialogResult
        Result = MessageBox.Show("Are you sure you wish to close the program?", "Close program?", MessageBoxButtons.YesNo)
        If Result = Windows.Forms.DialogResult.Yes Then
            ' Makes sure that the program will actually fully close
            Application.Exit()
        Else
        End If
    End Sub


    Private Sub btnPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrev.Click
        ' The current form will close and go back to the previous form
        Me.Close()
    End Sub


    Private Sub btnCreateTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateTest.Click
        ' Once the button is clicked, 'Create test' form will show up
        frmCreateTest.Show()
    End Sub
End Class


Student menu form- The same as for the teacher but with different forms that they can be directed to. Again, a simple menu(?) form.
Code:
Public Class frmStudentMenu
    Private Sub frmStudentMenu_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


    End Sub


    Private Sub btnLeader_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLeader.Click
        ' Once the button is clicked, 'Leaderboards' form will show up
        frmLeaderboards1.Show()
    End Sub


    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        ' When a user will press the exit button, a message box will appear asking whether they want to close the program and if a user clicks 'Yes' then 
        ' the whole program will close but if they click 'No' then the message box will simply dissapear 
        Dim Result As DialogResult
        Result = MessageBox.Show("Are you sure you wish to close the program?", "Close program?", MessageBoxButtons.YesNo)
        If Result = Windows.Forms.DialogResult.Yes Then
            ' Makes sure that the program will actually fully close
            Application.Exit()
        Else
        End If
    End Sub


    Private Sub btnPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrev.Click
        ' The current form will close and go back to the previous form
        Me.Close()
    End Sub


    Private Sub btnDoTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDoTest.Click
        ' Once the button is clicked, 'Test' form will show up
        frmTest.ShowDialog()
    End Sub
End Class

Create test form- This is a form that I use to create a test in. It includes a label that just tells the user to complete all the fields and then click the save button, label that says 'Definition' with a text box under it where a teacher will be able to actually type in the definition, a label that says 'Answer' with a text box under it where a teacher will be able to type in the answer, a label that says 'Week number' with a text box next to it where a teacher will be able to type in the week number as well as a button save which saves all the data the teacher has typed in and saves it in the database in a table called 'TestData'. No problems with this form either.
Code:
Imports System.Data.OleDb
Public Class frmCreateTest
    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        ' When a user will press the exit button, a message box will appear asking whether they want to close the program and if a user clicks 'Yes' then 
        ' the whole program will close but if they click 'No' then the message box will simply dissapear 
        Dim Result As DialogResult
        Result = MessageBox.Show("Are you sure you wish to close the program?", "Close program?", MessageBoxButtons.YesNo)
        If Result = Windows.Forms.DialogResult.Yes Then
            ' Makes sure that the program will actually fully close
            Application.Exit()
        Else
        End If
    End Sub


    Private Sub btnPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrev.Click
        ' The current form will close and go back to the previous form
        Me.Close()
    End Sub


    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        ' Open Connection
        Dim MyDB, MyStr As String
        Dim Connection As New System.Data.OleDb.OleDbConnection()
        MyStr = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database for VB.accdb")


        Connection = New OleDb.OleDbConnection(MyStr)
        Connection.Open()


        ' Checks whether any of the fields are empty if yes, then an error message will show up
        If txtDef.Text = "" Or txtAns.Text = "" Or txtWeekNo.Text = "" Then
            MessageBox.Show("Please complete the required fields.", "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else


            ' Adding a new record to the database
            MyDB = "INSERT INTO TestData (Definition,Answer,WeekNumber) values ('" & txtDef.Text & "','" & txtAns.Text & "','" & txtWeekNo.Text & "')"


            Dim Run = New OleDb.OleDbCommand


            Try
                ' If record added successfully then a message box will show up
                Run = New OleDb.OleDbCommand(MyDB, Connection)
                Run.ExecuteNonQuery()
                MsgBox("Record has been successfully added!")
                txtDef.Text = ""
                txtAns.Text = ""
                txtWeekNo.Text = ""


            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Critical, "OleDb Error")
            End Try
        End If
    End Sub
End Class

Test form- This is the form which actually displays the test for the student. It includes these labels: 'Week number', 'lblWeekNo2', (This lable will actually change to a week number once the test starts, some bits of the test show up once the user actually clicks the start button and you can see that in the code below.) 'Definition', 'Answer', 'lblInfo' (This label changes as the the test progresses, it shows when the test starts and then simply displays the number of the questions, however, once the test is finished it changes to ''You have successfully completed the test! Well done!'') 'lblDef', (This label is linked with the database to actually show the definition to the user.) a text box which allows the student to actually type in the answer as well as two buttons. 'Start' (Starts the test and shows some bits and hides others[shown in the code below]) and 'Next' which basically just shows the next definition to the user.
P.S. I tried to display that score for the user but no matter what I do, it always shows 'You have scored 2' and this is where I need help. As well as how to then save that score with the week number and student details(Maybe their login?) into the database.

Code:
Public Class frmTest
    Dim Conn As New System.Data.OleDb.OleDbConnection()
    Dim Score As Integer = 0
    Dim Ansrs(1, 9) As String
    Dim r As Integer = 1
    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        ' When a user will press the exit button, a message box will appear asking whether they want to close the program and if a user clicks 'Yes' then 
        ' the whole program will close but if they click 'No' then the message box will simply dissapear 
        Dim Result As DialogResult
        Result = MessageBox.Show("Are you sure you wish to close the program?", "Close program?", MessageBoxButtons.YesNo)
        If Result = Windows.Forms.DialogResult.Yes Then
            ' Makes sure that the program will actually fully close
            Application.Exit()
        Else
        End If
    End Sub


    Private Sub btnPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrev.Click
        ' Instead of just closing the form, it closes the form so that when you open it again, everything resets
        Me.Dispose()
    End Sub


    Private Sub frmTest_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' Makes all the things invisible before the actual test starts
        lblDef.Visible = False
        lblWeekNo2.Visible = False
        lblInfo.Visible = False
        lblDefinition.Visible = False
        txtAns.Visible = False
        lblWeekNo.Visible = False
        btnNxt.Visible = False
        lblAnswer.Visible = False
        ' Opening the connection
        Conn = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=Database for VB.accdb")
        Conn.Open()
    End Sub


    Private Sub btnNxt_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNxt.Click
        If r = 10 Then
            Ansrs(0, r - 1) = txtAns.Text
            txtAns.Text = ""
            FinishTest()
        Else
            Ansrs(1, r - 1) = txtAns.Text
            r = r + 1
            txtAns.Text = ""
            StartTest()
        End If
    End Sub
    Private Sub btnStart_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStart.Click
        ' Makes all the buttons that you will need in the test, visible. It'll show the field for the user to complete
        txtAns.Visible = True
        lblWeekNo.Visible = True
        lblWeekNo2.Visible = True
        lblDef.Visible = True
        lblDefinition.Visible = True
        btnStart.Visible = False
        lblAnswer.Visible = True
        ' When the button 'Start the test!' is clicked, the 'StartTest' sub routine will start
        StartTest()
    End Sub
    Private Sub StartTest()
        ' Making the 'Next' button visible to allow the user to complete the whole test
        btnNxt.Visible = True
        ' Reading data from the database
        Dim StringStart As String = "SELECT * FROM TestData WHERE WordNumber = " & r
        Dim Comm As New OleDb.OleDbCommand(StringStart, Conn)
        Dim RDR As OleDb.OleDbDataReader = Comm.ExecuteReader
        RDR.Read()
        lblDef.Text = RDR.Item("Definition")
        Ansrs(0, r - 1) = RDR.Item("Answer")
        lblWeekNo2.Text = RDR.Item("WeekNumber")
        lblInfo.Visible = True
        lblInfo.Text = r & "/10"
    End Sub
    Private Sub FinishTest()
        lblInfo.Text = "You have successfully completed the test! Well done!"
        lblInfo.Left = (Me.Width / 1.5) - (lblInfo.Width / 1.5)
        ' When the test is done, it makes all of the things that the user won't need anymore, invisible
        lblDef.Visible = False
        lblWeekNo2.Visible = False
        btnNxt.Visible = False
        lblWeekNo.Visible = False
        txtAns.Visible = False
        lblAnswer.Visible = False
        lblDefinition.Visible = False
        TestScore()
    End Sub
    Private Sub TestScore()
        For r As Integer = 0 To 9
            If Ansrs(0, r) = Ansrs(1, r) Then
                Score = Score + 2
                MsgBox("You have scored " & Score)
            End If
        Next
    End Sub
End Class

I hope that this makes it at least a little bit clearer now... I'm going to leave the rest of the forms at least for now because this is what I think, would be best to do next.
Thanks.
 
Last edited by a moderator:
Top Bottom