classes.cls

vb+sql学籍管理系统

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "Classes"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Public Class_Id As Long
Public Class_Name As String
Public UpperId As Long

Public Sub Init()
  Class_Id = -1
  Class_Name = ""
  UpperId = -1
End Sub

'删除
Public Sub Delete(ByVal TmpId As Long)
  SqlStmt = "DELETE FROM Classes WHERE Class_Id=" + Trim(Str(TmpId))
  SQLExt (SqlStmt)
End Sub

Public Sub GetInfo(TmpId As Long)
  Dim rs As New ADODB.Recordset
  Class_Id = TmpId
  
  SqlStmt = "SELECT * FROM Classes WHERE Class_Id=" + Trim(Str(TmpId))
  Set rs = QueryExt(SqlStmt)
  If Not rs.EOF Then
    '读取院系班级名称
    Class_Name = rs.Fields(1)
    
    '读取上一级院系班级的编号
    UpperId = rs.Fields(2)
  Else
    Init
  End If
    
  rs.Close
End Sub

Public Function GetName(ByVal TmpId As Long) As String
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT Class_Name FROM Classes WHERE Class_Id=" + Trim(Str(TmpId))
  Set rs = QueryExt(SqlStmt)
  If Not rs.EOF Then
    GetName = rs.Fields(0)
  Else
    GetName = ""
  End If
  rs.Close
End Function

Public Function GetNewId() As Long
  Dim TmpId As Long
  Dim rs As New ADODB.Recordset
  '设置要执行的SQL语句,取当前最大的记录编号
  SqlStmt = "SELECT MAX(Class_Id) FROM Classes"
  '执行SQL语句
  Set rs = QueryExt(SqlStmt)
  '读取SELECT语句返回的查询结果
  If IsNull(rs.Fields(0)) Then
    GetNewId = 1
  Else
    GetNewId = rs.Fields(0) + 1
  End If
  
  rs.Close
End Function

Public Function HaveStudent(ByVal TmpClassId As Long) As Boolean
  Dim rs As New ADODB.Recordset
  '设置要执行的SQL语句
  SqlStmt = "SELECT * FROM Students WHERE Class_id=" + Trim(Str(TmpClassId))
  '执行SQL语句
  Set rs = QueryExt(SqlStmt)
  If rs.EOF Then
    HaveStudent = False
  Else
    HaveStudent = True
  End If
  rs.Close
End Function

Public Function HaveSon(ByVal TmpUpperId As Long) As Boolean
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT Class_Id FROM Classes WHERE UpperId=" _
          + Trim(Str(TmpUpperId))
  Set rs = QueryExt(SqlStmt)
  If rs.EOF Then
    HaveSon = False
  Else
    HaveSon = True
  End If
  rs.Close
End Function

Public Function In_DB(ByVal ClassName As String) As Boolean
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT Class_Id FROM Classes WHERE Class_Name='" + Trim(ClassName) + "'"
  Set rs = QueryExt(SqlStmt)
  If rs.EOF Then
    In_DB = False
  Else
    In_DB = True
  End If
  rs.Close
End Function

Public Function Insert() As Long
  Dim Class_Id As Long
  '生成新的编号
  Class_Id = GetNewId
  ' 插入记录
  SqlStmt = "INSERT INTO Classes VALUES(" + Trim(Str(Class_Id)) + ",'" _
            + Trim(Class_Name) + "'," + Trim(Str(UpperId)) + ")"
  SQLExt (SqlStmt)
  Insert = Class_Id
End Function

Public Sub Load_Class_ByUpper(UpperId As Long)
  Dim i As Integer
  Dim rs As New ADODB.Recordset
  
  '初始化院系班级数组
  Erase Arr_ClassName
  Erase Arr_ClassId
  ReDim Arr_ClassName(0)
  ReDim Arr_ClassId(0)
  
  DB_Connect
  SqlStmt = "SELECT Class_Id,Class_Name FROM Classes WHERE UpperId=" _
          + Trim(Str(UpperId)) + " ORDER BY Class_Id"
  Set rs = QueryExt(SqlStmt)
  i = 0
  Do Until rs.EOF
    '读取院系班级编号
    ReDim Preserve Arr_ClassId(i + 1)
    Arr_ClassId(i) = rs.Fields(0)
    '读取院系班级名称
    ReDim Preserve Arr_ClassName(i + 1)
    Arr_ClassName(i) = rs.Fields(1)
    
    rs.MoveNext
    i = i + 1
  Loop
  rs.Close
End Sub

'更新数据
Public Sub Update(ByVal TmpId As Long)
  SqlStmt = "UPDATE Classes SET Class_Name='" + Trim(Class_Name) _
          + "' WHERE Class_Id=" + Trim(Str(TmpId))
  SQLExt (SqlStmt)
End Sub