students.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 = "Students"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Public Student_Id As Long
Public Student_Name As String
Public Sex As String
Public Nationality As String
Public Birth As String
Public Family_Place As String
Public Id_Card As String
Public BadgeID As String
Public Home_phone As String
Public Residence As String
Public Postcode As String
Public InDate As String
Public Class_Id As Integer
Public Title As String
Public Memo As String
Public Fillin_Time As String

Public Sub Init()
  Student_Id = 0
  Student_Name = ""
  Sex = ""
  Nationality = ""
  Birth = ""
  Family_Place = ""
  Id_Card = ""
  BadgeID = ""
  Home_phone = ""
  Residence = ""
  Postcode = ""
 ' InDate = ""
  Class_Id = -1
  Title = ""
  Memo = ""
  Fillin_Time = ""
End Sub

'删除数据
Public Sub Delete(ByVal TmpStudentId As Long)
  SqlStmt = "Delete FROM Students WHERE Student_Id=" + Trim(TmpStudentId)
  SQLExt (SqlStmt)
  ' 删除其他表中的相关记录
  MyChange.DeleteByStudent (TmpStudentId)
  MyExp.DeleteByStudent (TmpStudentId)
End Sub

Public Function GetName(ByVal TmpStudentId As Long) As String
  If TmpStudentId <= 0 Then
    GetName = ""
    Exit Function
  End If
  
  Dim rs As New ADODB.Recordset
  SqlStmt = "SELECT Student_Name FROM Students WHERE Student_Id=" + Trim(TmpStudentId)
  Set rs = QueryExt(SqlStmt)
  If rs.EOF Then
    GetName = ""
    Exit Function
  Else
    GetName = Trim(rs.Fields(0))
  End If
  rs.Close
End Function

Public Function GetInfo(ByVal TmpStudentId As Long) As Boolean
  If TmpStudentId <= 0 Then
    Init
    GetInfo = False
    Exit Function
  End If
  
  Student_Id = TmpStudentId
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT * FROM Students WHERE Student_Id=" + Trim(TmpStudentId)
  Set rs = QueryExt(SqlStmt)
  If rs.EOF Then
    GetInfo = False
    Exit Function
  Else
    ' 学生姓名
    Student_Name = Trim(rs.Fields(1))
    ' 性别
    If IsNull(rs.Fields(3)) Then
      Sex = ""
    Else
      Sex = Trim(rs.Fields(3))
    End If
    ' 民族
    If IsNull(rs.Fields(4)) Then
      Nationality = ""
    Else
      Nationality = Trim(rs.Fields(4))
    End If
    ' 生日
    If IsNull(rs.Fields(5)) Then
      Birth = ""
    Else
      Birth = Trim(rs.Fields(5))
    End If
    ' 籍贯
    If IsNull(rs.Fields(6)) Then
      Family_Place = ""
    Else
      Family_Place = Trim(rs.Fields(6))
    End If
    ' 身份证
    If IsNull(rs.Fields(7)) Then
      Id_Card = ""
    Else
      Id_Card = Trim(rs.Fields(7))
    End If
    ' 工作证号
    If IsNull(rs.Fields(8)) Then
      BadgeID = ""
    Else
      BadgeID = Trim(rs.Fields(8))
    End If
    ' 住宅电话
    If IsNull(rs.Fields(9)) Then
      Home_phone = ""
    Else
      Home_phone = Trim(rs.Fields(9))
    End If
    ' 居住地址
    If IsNull(rs.Fields(10)) Then
      Residence = ""
    Else
      Residence = Trim(rs.Fields(10))
    End If
    ' 邮政编码
    If IsNull(rs.Fields(11)) Then
      Postcode = ""
    Else
      Postcode = Trim(rs.Fields(11))
    End If
    ' 入校日期
    'If IsNull(rs.Fields(15)) Then
      'InDate = ""
    'Else
      'InDate = Trim(rs.Fields(15))
    'End If
    ' 班级编号
    Class_Id = Trim(rs.Fields(12))
    ' 职务
    If IsNull(rs.Fields(13)) Then
      Title = ""
    Else
      Title = Trim(rs.Fields(13))
    End If
    ' 备注
    If IsNull(rs.Fields(14)) Then
      Memo = ""
    Else
      Memo = Trim(rs.Fields(14))
    End If
    ' 填表时间
    If IsNull(rs.Fields(2)) Then
      Fillin_Time = ""
    Else
      Fillin_Time = Trim(rs.Fields(2))
    End If
  End If
  GetInfo = True
  rs.Close
End Function

Public Sub Insert()
  SqlStmt = "INSERT INTO Students (Student_Id,Student_Name, Sex, Nationality," _
     + " Birth, Family_Place, Id_Card, BadgeId, Home_Phone," _
     + " Residence, Postcode, Class_Id, Title, Memo, Fillin_Time)" _
     + " Values('" + Trim(BadgeID) + "','" + Trim(Student_Name) _
     + "','" + Trim(Sex) + "','" + Trim(Nationality) + "','" + Trim(Birth) _
     + "','" + Trim(Family_Place) + "','" _
     + Trim(Id_Card) + "','" + Trim(BadgeID) + "','" + Trim(Home_phone) _
     + "','" + Trim(Residence) + "','" + Trim(Postcode) _
     + "'," + Trim(Str(Class_Id)) + ",'" + Trim(Title) + "','" + Trim(Memo) _
     + "','" + Trim(Fillin_Time) + "')"
  SQLExt (SqlStmt)
End Sub

Public Sub Load_By_Class(ByVal TmpClassId As Integer)
  Dim i As Integer
  Dim rs As New ADODB.Recordset
  '初始化院系班级数组
  Erase Arr_StuId
  ReDim Arr_StuId(0)
  Erase Arr_StuName
  ReDim Arr_StuName(0)
  '要执行的SQL语句
  SqlStmt = "SELECT Student_Id, Student_Name FROM Students WHERE Class_Id=" _
          + Trim(TmpClassId) + " ORDER BY Student_Id"
  '执行SQL语句
  Set rs = QueryExt(SqlStmt)
  i = 0
  '依次读取满足条件的学生编号读取到数组中
  Do Until rs.EOF
    ReDim Preserve Arr_StuId(i + 1)
    Arr_StuId(i) = rs.Fields(0)
    
    ReDim Preserve Arr_StuName(i + 1)
    Arr_StuName(i) = Trim(rs.Fields(1))
    
    i = i + 1
    rs.MoveNext
  Loop
  rs.Close
End Sub

Public Sub Update(ByVal OriStudentId As Integer)
  SqlStmt = "Update Students Set Student_Name='" + Trim(Student_Name) _
          + "',Sex='" + Trim(Sex) + "',Nationality='" + Trim(Nationality) _
          + "',Birth='" + Trim(Birth) + "', Family_Place='" + Trim(Family_Place) _
          + "',Id_Card='" + Trim(Id_Card) _
          + "',BadgeID='" + Trim(BadgeID) + "',Home_phone='" + Trim(Home_phone) _
          + "',Residence='" + Trim(Residence) + "',Postcode='" + Trim(Postcode) _
          + "',Class_Id=" + Trim(Str(Class_Id)) _
          + ",Title='" + Trim(Title) + "',Memo='" + Trim(Memo) + "',Fillin_Time='" _
          + Trim(Fillin_Time) + "' WHERE Student_Id=" + Trim(OriStudentId)
  SQLExt (SqlStmt)
End Sub

'更新班级信息
Public Sub Update_Transfer(ByVal OriStudentId As Integer)
  SqlStmt = "Update Students Set Class_Id=" + Trim(Class_Id) _
          + " WHERE Student_id=" + Trim(OriStudentId)
  SQLExt (SqlStmt)
End Sub