VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "clsSubjects"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Dim mrs As New ADODB.Recordset
Dim msql As String
Public Sub AddSubject(ID As String, SubjectCode As String, SubjectName As String, Yearlevel As String, Semester As String)
If rs.State = adStateOpen Then rs.Close
msql = " Select * from Subject Where SubjectName='" & SubjectName & "'"
rs.Open msql, conn
If rs.RecordCount >= 1 Then
MsgBox "Duplicate ID.Please enter valid ID number.", vbCritical, ""
Exit Sub
End If
With rs
    .AddNew
    !ID = ID
    !SubjectCode = SubjectCode
    !SubjectName = SubjectName
    !Yearlevel = Yearlevel
    !Semester = Semester
    .Update
End With
MsgBox "New entry successfully saved to the record.", vbInformation
End Sub
Public Sub UpdateSubject(ID As String, SubjectCode As String, SubjectName As String, Yearlevel As String, Semester As String, OldID As String)
If rs.State = adStateOpen Then rs.Close
msql = " Update Subject set id='" & ID & "', SubjectCode='" & SubjectCode & "',SubjectName='" & SubjectName & "', YearLevel='" & Yearlevel & "',semester='" & Semester & "' Where id='" & OldID & "'"
rs.Open msql, conn
MsgBox "Record(s) Updated", vbInformation, ""
End Sub
Public Sub DeleteSubject(ID As String)
If rs.State = adStateOpen Then rs.Close
msql = " Delete * From Subject Where ID='" & ID & "'"
rs.Open msql, conn
MsgBox "Record(s) Deleted", vbInformation, ""
End Sub
Public Sub DisplaySubjects(lstCourse As ListView, SearchValue As String)
Dim lstItem As ListItem, a As Integer
If rs.State = adStateOpen Then rs.Close
If SearchValue <> "" Then
msql = " SELECT Subject.ID, Subject.SubjectCode, Subject.SubjectName, Subject.YearLevel, Subject.Semester" & _
       " From Subject" & _
       " Where Subject.SubjectName like '" & SearchValue & "%'" & _
       " GROUP BY Subject.ID, Subject.SubjectCode, Subject.SubjectName, Subject.YearLevel, Subject.Semester" & _
       " ORDER BY Subject.ID, Subject.SubjectName;"
Else
msql = " SELECT Subject.ID, Subject.SubjectCode, Subject.SubjectName, Subject.YearLevel, Subject.Semester" & _
       " From Subject" & _
       " GROUP BY Subject.ID, Subject.SubjectCode, Subject.SubjectName, Subject.YearLevel, Subject.Semester" & _
       " ORDER BY Subject.ID, Subject.SubjectName;"
End If
rs.Open msql, conn
   lstCourse.ListItems.Clear
   Do While Not rs.EOF
    a = a + 1
        Set lstItem = lstCourse.ListItems.Add(, , a)
            lstItem.SubItems(1) = rs(0).Value
            lstItem.SubItems(2) = rs(1).Value
            lstItem.SubItems(3) = rs(2).Value
            lstItem.SubItems(4) = rs(3).Value
            lstItem.SubItems(5) = rs(4).Value
            rs.MoveNext
            Loop
End Sub

Function GetID() As String
If rs.State = adStateOpen Then rs.Close
msql = " SELECT Max(Subject.ID) AS MaxOfID" & _
" FROM Subject;"
rs.Open msql, conn
If IsNull(rs(0).Value) Then
GetID = Format(1, "00000")
Else
GetID = Format(rs(0).Value + 1, "00000")
End If
End Function





