Simple Student Records System with Database

Written in Visual BASIC with MS Access database

Required:

1. Add
2. Edit
3. Delete
4. Search
5. Get Quiz Average of a specific student

The Program

The Database

Main Form code

Public Class frmMain
    Sub read()
        If conn.State = ConnectionState.Open Then conn.Close()
        Dim dt As New DataTable
        adapter.Fill(dt)


        txtStudno.Text = dt.Rows(0).Item("Student Number")
        txtfname.Text = dt.Rows(0).Item("First Name")
        txtsurname.Text = dt.Rows(0).Item("Surname")
        txtgender.Text = dt.Rows(0).Item("Gender")
        txtq1.Text = dt.Rows(0).Item("Quiz 1")
        txtq2.Text = dt.Rows(0).Item("Quiz 2")
        txtq3.Text = dt.Rows(0).Item("Quiz 3")
        txtq4.Text = dt.Rows(0).Item("Quiz 4")
        dgv.ClearSelection()
    End Sub

    Sub dis()
        cmdUpdate.Enabled = True
        cmdCancel.Enabled = True
        cmdAdd.Enabled = False
        cmdDelete.Enabled = False
        cmdEdit.Enabled = False
        txtsearch.Enabled = False
        cmdsearch.Enabled = False
        txtStudno.Enabled = False
        filter.Enabled = False
        dgv.ClearSelection()

    End Sub

    Sub ena()
        cmdUpdate.Enabled = False
        cmdCancel.Enabled = False
        cmdAdd.Enabled = True
        cmdEdit.Enabled = True
        txtsearch.Enabled = True
        cmdsearch.Enabled = True
        txtStudno.Enabled = True
        filter.Enabled = True

        cmdAdd.Enabled = True
        dgv.ClearSelection()

    End Sub
    Sub clear()
        txtStudno.Text = ""
        txtfname.Text = ""
        txtsurname.Text = ""
        txtgender.Text = ""
        txtq1.Text = ""
        txtq2.Text = ""
        txtq3.Text = ""
        txtq4.Text = ""
        txtgender.SelectedIndex = -1
        dgv.ClearSelection()

    End Sub
    Sub populate()

        sSQL = "select StudNo as [Student Number],fname as [First Name], surname as [Surname], gender as [Gender], q1 as [Quiz 1], q2 as [Quiz 2], q3 as [Quiz 3], q4 as [Quiz 4] from tblstudents order by StudNo"
        execSQL(sSQL, False)
        filldg(sSQL, dgv)
        adapter.Fill(dt)

        dgv.ClearSelection()

    End Sub
    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs)

    End Sub

    Private Sub ToolStripLabel1_Click(sender As Object, e As EventArgs)

    End Sub

    Private Sub frmMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ena()
        populate()
        dgv.ClearSelection()
    End Sub

    Private Sub cmdAdd_Click(sender As Object, e As EventArgs) Handles cmdAdd.Click

        Add()

    End Sub

    Private Sub cmdEdit_Click(sender As Object, e As EventArgs) Handles cmdEdit.Click
        If dgv.Rows.Count > 0 Then
            If dgv.SelectedRows.Count > 0 Then
                dis()
                sSQL = "SELECT StudNo as [Student Number],fname as [First Name], surname as [Surname], gender as [Gender], q1 as [Quiz 1], q2 as [Quiz 2], q3 as [Quiz 3], q4 as [Quiz 4] from tblstudents where StudNo=" & dgv.CurrentRow.Cells("Student Number").Value & ""
                execSQL(sSQL, False)
                read()
            Else
                    MessageBox.Show("No record selected!")

            End If
        Else
            MessageBox.Show("No records!")
        End If
    End Sub

    Private Sub cmdCancel_Click(sender As Object, e As EventArgs) Handles cmdCancel.Click
        ena()
        clear()
        populate()
    End Sub

    Private Sub cmdUpdate_Click(sender As Object, e As EventArgs) Handles cmdUpdate.Click
        edit()
    End Sub

    Private Sub cmdDelete_Click(sender As Object, e As EventArgs) Handles cmdDelete.Click
        delete()
    End Sub


    Private Sub txtsearch_TextChanged(sender As Object, e As EventArgs) Handles txtsearch.TextChanged
        search()
    End Sub


    Private Sub cmdquiz_Click(sender As Object, e As EventArgs) Handles cmdquiz.Click
        ave()
    End Sub

    Private Sub filter_SelectedIndexChanged(sender As Object, e As EventArgs) Handles filter.SelectedIndexChanged
        txtsearch.Text = ""
    End Sub
End Class

Database connection module

Imports System.Data
Imports System.Configuration
Imports System.Data.OleDb
Imports System

Module ModuleConnection
    Public conn As New OleDbConnection
    Public cmd As New OleDbCommand
    Public RD As OleDbDataReader
    Public sSQL As String
    Public adapter As New OleDbDataAdapter
    Public mydataset As New DataSet
    Public dt As New DataTable


    Public Sub execSQL(ByVal query As String, ByVal stat As Boolean)
        Try
            If conn.State = ConnectionState.Open Then conn.Close()
            conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\students.mdb;User Id=admin;Password=;"
            '"server=RANEGILLIAN\DWHSEASDB;Database=login;Trusted_Connection=True;"
            conn.Open()
            cmd.Connection = conn
            cmd.CommandText() = query




            If stat = False Then
                RD = cmd.ExecuteReader()
            Else
                cmd.ExecuteNonQuery()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

    Public Sub filldg(ByVal query As String, ByRef dg As DataGridView)
        Try
            If conn.State = ConnectionState.Open Then conn.Close()
            cmd = New OleDbCommand(query, conn)
            adapter = New OleDbDataAdapter(cmd)

            mydataset = New DataSet()
            adapter.Fill(mydataset)
            dg.DataSource = ""
            dg.DataSource = mydataset.Tables(0)
            dg.AutoResizeColumns()

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
End Module

[1] Add module code

Module ModuleAdd

    Public Sub Add()

        If Len(Trim(frmMain.txtStudno.Text)) = 0 Then
            MessageBox.Show("Please don't leave any blanks")
        ElseIf frmMain.txtStudno.Text > 9999999999 Or frmMain.txtStudno.Text < 1000000000 Then
            MessageBox.Show("Enter 10 digits student number")
        ElseIf frmMain.txtfname.Text = "" Or frmMain.txtsurname.Text = "" Or frmMain.txtgender.Text = "" Then
            MessageBox.Show("Please don't leave any blanks")
        ElseIf frmMain.txtq1.Text > 100 Or frmMain.txtq1.Text < 0 Or frmMain.txtq2.Text > 100 Or frmMain.txtq2.Text < 0 Or frmMain.txtq3.Text > 100 Or frmMain.txtq3.Text < 0 Or frmMain.txtq4.Text > 100 Or frmMain.txtq4.Text < 0 Then
            MessageBox.Show("0-100 range on quizzes")
        Else
            sSQL = "select StudNo as [Student Number],fname as [First Name], surname as [Surname], gender as [Gender], q1 as [Quiz 1], q2 as [Quiz 2], q3 as [Quiz 3], q4 as [Quiz 4] from tblstudents where Studno=" & frmMain.txtStudno.Text & ""
            execSQL(sSQL, False)

            If RD.Read Then
                MsgBox("Student with student number of " + frmMain.txtStudno.Text + " already exists!", MsgBoxStyle.Critical, "Duplicated")
            Else



                sSQL = "INSERT INTO tblStudents(`StudNo`, `fname`, `surname`, `gender`, `q1`, `q2`, `q3`, `q4`) values ('" & frmMain.txtStudno.Text & "', '" & frmMain.txtfname.Text & "', '" & frmMain.txtsurname.Text & "', '" & frmMain.txtgender.Text & "', '" & frmMain.txtq1.Text & "', '" & frmMain.txtq2.Text & "', '" & frmMain.txtq3.Text & "', '" & frmMain.txtq4.Text & "') "
                execSQL(sSQL, True)

                MessageBox.Show("Record Added")

                frmMain.clear()
                frmMain.populate()



            End If
        End If
    End Sub

End Module

[2] Edit module code

Module ModuleUpdate

    Public Sub edit()
        If frmMain.txtfname.Text = "" Or frmMain.txtsurname.Text = "" Or frmMain.txtgender.Text = "" Then
            MessageBox.Show("Please don't leave any blanks")
        ElseIf frmMain.txtq1.Text > 100 Or frmMain.txtq1.Text < 0 Or frmMain.txtq2.Text > 100 Or frmMain.txtq2.Text < 0 Or frmMain.txtq3.Text > 100 Or frmMain.txtq3.Text < 0 Or frmMain.txtq4.Text > 100 Or frmMain.txtq4.Text < 0 Then
            MessageBox.Show("0-100 range on quizzes")
        Else
            sSQL = "UPDATE tblStudents SET fname='" & frmMain.txtfname.Text & "', surname='" & frmMain.txtsurname.Text & "', gender='" & frmMain.txtgender.Text & "', q1='" & frmMain.txtq1.Text & "', q2='" & frmMain.txtq2.Text & "', q3='" & frmMain.txtq3.Text & "', q4='" & frmMain.txtq4.Text & "' where Studno=" & frmMain.txtStudno.Text & ""
            execSQL(sSQL, True)

            MessageBox.Show("Record Updated")

            frmMain.clear()
            frmMain.ena()
            frmMain.populate()

        End If
    End Sub

End Module

[3] Delete module code

Module ModuleDelete

    Public Sub delete()
        If frmMain.dgv.Rows.Count > 0 Then
            If frmMain.dgv.SelectedRows.Count > 0 Then
                If MsgBox("Are you Sure?", MsgBoxStyle.YesNo + MsgBoxStyle.Question, "Confirmation") = MsgBoxResult.Yes Then
                    sSQL = "DELETE from tblStudents where Studno=" & frmMain.dgv.SelectedRows(0).Cells("Student Number").Value & ""
                    MessageBox.Show("Record Deleted!")
                    execSQL(sSQL, True)

                    frmMain.clear()
                    frmMain.populate()
                End If
            Else
                MessageBox.Show("No record selected!")

            End If
        Else
            MessageBox.Show("No records!")
        End If

    End Sub


End Module

[4] Search module code

Module ModuleSearch

    Public Sub search()
        If frmMain.filter.Text = "Student Number" Then
            sSQL = "select StudNo as [Student Number],fname as [First Name], surname as [Surname], gender as [Gender], q1 as [Quiz 1], q2 as [Quiz 2], q3 as [Quiz 3], q4 as [Quiz 4] from tblstudents where StudNo like '" & frmMain.txtsearch.Text & "%'"
            execSQL(sSQL, False)
            filldg(sSQL, frmMain.dgv)
            adapter.Fill(dt)


        ElseIf frmMain.filter.Text = "First Name" Then
            sSQL = "select StudNo as [Student Number],fname as [First Name], surname as [Surname], gender as [Gender], q1 as [Quiz 1], q2 as [Quiz 2], q3 as [Quiz 3], q4 as [Quiz 4] from tblstudents where fname like '" & frmMain.txtsearch.Text & "%'"
            execSQL(sSQL, False)
            filldg(sSQL, frmMain.dgv)
            adapter.Fill(dt)

        ElseIf frmMain.filter.Text = "Surname" Then
            sSQL = "select StudNo as [Student Number],fname as [First Name], surname as [Surname], gender as [Gender], q1 as [Quiz 1], q2 as [Quiz 2], q3 as [Quiz 3], q4 as [Quiz 4] from tblstudents where surname like '" & frmMain.txtsearch.Text & "%'"
            execSQL(sSQL, False)
            filldg(sSQL, frmMain.dgv)
            adapter.Fill(dt)
        Else
            sSQL = "select StudNo as [Student Number],fname as [First Name], surname as [Surname], gender as [Gender], q1 as [Quiz 1], q2 as [Quiz 2], q3 as [Quiz 3], q4 as [Quiz 4] from tblstudents where surname like '" & frmMain.txtsearch.Text & "%' or fname like '" & frmMain.txtsearch.Text & "%' or StudNo like '" & frmMain.txtsearch.Text & "%'"
            execSQL(sSQL, False)
            filldg(sSQL, frmMain.dgv)
            adapter.Fill(dt)
        End If


    End Sub

End Module

[5] Get Quiz Average of a specific student code

Module ModuleAve
    Public average As Single
    Public sum As Single

    Public Sub ave()

        If frmMain.dgv.Rows.Count > 0 Then
            If frmMain.dgv.SelectedRows.Count > 0 Then
                sum = frmMain.dgv.CurrentRow.Cells("Quiz 1").Value + frmMain.dgv.CurrentRow.Cells("Quiz 2").Value + frmMain.dgv.CurrentRow.Cells("Quiz 3").Value + frmMain.dgv.CurrentRow.Cells("Quiz 4").Value
                average = sum / 4

                MsgBox("The average of Student Number " + frmMain.dgv.CurrentRow.Cells("Student Number").Value.ToString + " is " + average.ToString + "%", MsgBoxStyle.OkOnly + MsgBoxStyle.Information, "Quiz Average")

                frmMain.clear()
                frmMain.populate()
                frmMain.dgv.ClearSelection()
            Else
                MessageBox.Show("No record selected!")

            End If
        Else
            MessageBox.Show("No records!")
        End If
    End Sub

End Module