students.cls

这是一个国内通用的学生管理系统!包括院系,学生基本信息,照片,成绩,课程等多方面的管理系统

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
'1 Student_Id  Int 学生编号
'2 Student_Name  Varchar 50  姓名
'3 Photo Image 照片
'4 Sex Char 2  性别
'5 Nationality Varchar 40  民族
'6 Birth Varchar 20  生日
'7 Political_Party Varchar 40  政治面貌
'8 Family_Place  Varchar 60  籍贯
'9 Id_Card Varchar 20  身份证号
'10  BadgeID Varchar 40  学生证号
'11  Home_phone  Varchar 30  家庭电话
'12  Residence Varchar 100 居住地址
'13  Postcode  Varchar 10  邮政编码
'14  InDate  Varchar 20  入校日期
'15  Class_Id  Smallint  所在院系编号
'16  Title Varchar 20  职务
'17  Memo  Varchar 200 备注
'18  Fillin_Time Varchar 20  填表时间（日期+时间）

Public Student_Id As Long
Public Student_Name As String
Public Sex As String
Public Nationality As String
Public Birth As String
Public Political_Party 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 = ""
  Political_Party = ""
  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)
  DB_Connect
  
  SqlStmt = "Delete FROM Students WHERE Student_Id=" + Trim(Str(TmpStudentId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
   
  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
  
  DB_Connect
  
  SqlStmt = "SELECT Student_Name FROM Students WHERE Student_Id=" + Trim(Str(TmpStudentId))
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
    GetName = ""
    Exit Function
  Else
    ColVal = String(100, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Lench(ColVal), pcblench)
    GetName = TrimStr(ColVal)
  End If
    
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
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
  
  DB_Connect
  
  SqlStmt = "SELECT * FROM Students WHERE Student_Id=" + Trim(Str(TmpStudentId))
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
    GetInfo = False
    Exit Function
  Else
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 2, 1, ColVal, Lench(ColVal), pcblench)
    Student_Name = TrimStr(ColVal)

    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 4, 1, ColVal, Lench(ColVal), pcblench)
    Sex = TrimStr(ColVal)
    
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 5, 1, ColVal, Lench(ColVal), pcblench)
    Nationality = TrimStr(ColVal)

    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 6, 1, ColVal, Lench(ColVal), pcblench)
    Birth = TrimStr(ColVal)
    
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 7, 1, ColVal, Lench(ColVal), pcblench)
    Political_Party = TrimStr(ColVal)
    
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 8, 1, ColVal, Lench(ColVal), pcblench)
    Family_Place = TrimStr(ColVal)
    
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 9, 1, ColVal, Lench(ColVal), pcblench)
    Id_Card = TrimStr(ColVal)
    
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 10, 1, ColVal, Lench(ColVal), pcblench)
    BadgeID = TrimStr(ColVal)
    
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 11, 1, ColVal, Lench(ColVal), pcblench)
    Home_phone = TrimStr(ColVal)
    
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 12, 1, ColVal, Lench(ColVal), pcblench)
    Residence = TrimStr(ColVal)

    ColVal = String(4000, 0)
    Rc = SQLGetData(Hstmt, 13, 1, ColVal, Lench(ColVal), pcblench)
    Postcode = TrimStr(ColVal)

    ColVal = String(4000, 0)
    Rc = SQLGetData(Hstmt, 14, 1, ColVal, Lench(ColVal), pcblench)
    InDate = TrimStr(ColVal)

    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 15, 1, ColVal, Lench(ColVal), pcblench)
    Class_Id = Val(ColVal)

    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 16, 1, ColVal, Lench(ColVal), pcblench)
    Title = TrimStr(ColVal)

    ColVal = String(500, 0)
    Rc = SQLGetData(Hstmt, 17, 1, ColVal, Lench(ColVal), pcblench)
    Memo = TrimStr(ColVal)

    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 18, 1, ColVal, Lench(ColVal), pcblench)
    Fillin_Time = TrimStr(ColVal)

  End If
    
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  GetInfo = True
  DB_Disconnect
End Function

Public Function GetNewId() As Long
  Dim TmpId As Long
  
  DB_Connect
    
  SqlStmt = "SELECT Student_Id FROM Students ORDER BY Student_Id"
  OdbcExt (SqlStmt)
  i = 1
  Do While SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Len(ColVal), pcblen)
    TmpId = Val(ColVal)
    If TmpId = i Then
      i = i + 1
    Else
      GetNewId = i
      Rc = SQLFreeStmt(Hstmt, SQL_DROP)
      DB_Disconnect
      Exit Function
    End If
  Loop
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
  
  GetNewId = i
End Function

Public Sub Insert()
  Student_Id = GetNewId
  
  DB_Connect
  
  SqlStmt = "INSERT INTO Students (Student_Id, Student_Name, Sex, Nationality," _
     + " Birth, Political_Party, Family_Place, Id_Card, BadgeId, Home_Phone," _
     + " Residence, Postcode, InDate, Class_Id, Title, Memo, Fillin_Time)" _
     + " Values(" + Trim(Str(Student_Id)) + ",'" + Trim(Student_Name) _
     + "','" + Trim(Sex) + "','" + Trim(Nationality) + "','" + Trim(Birth) _
     + "','" + Trim(Political_Party) + "','" + Trim(Family_Place) + "','" _
     + Trim(Id_Card) + "','" + Trim(BadgeID) + "','" + Trim(Home_phone) _
     + "','" + Trim(Residence) + "','" + Trim(Postcode) + "','" + Trim(InDate) _
     + "'," + Trim(Str(Class_Id)) + ",'" + Trim(Title) + "','" + Trim(Memo) _
     + "','" + Trim(Fillin_Time) + "')"
  OdbcExt (SqlStmt)
    
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub

Public Sub Load_By_Class(ByVal TmpClassId As Integer)
  Dim i As Integer
  '初始化院系班级数组
  Erase Arr_StuId
  ReDim Arr_StuId(0)
  Erase Arr_StuName
  ReDim Arr_StuName(0)
  '连接数据库
  DB_Connect
  '要执行的SQL语句
  SqlStmt = "SELECT Student_Id, Student_Name FROM Students WHERE Class_Id=" _
          + Trim(Str(TmpClassId)) + " ORDER BY Student_Id"
  '执行SQL语句
  OdbcExt (SqlStmt)
  i = 0
  '依次读取满足条件的学生编号读取到数组中
  Do Until SQLFetch(Hstmt) = SQL_NO_DATA_FOUND
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Len(ColVal), pcblen)
    ReDim Preserve Arr_StuId(i + 1)
    Arr_StuId(i) = Val(ColVal)
    
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 2, 1, ColVal, Len(ColVal), pcblen)
    ReDim Preserve Arr_StuName(i + 1)
    Arr_StuName(i) = TrimStr(ColVal)
    
    i = i + 1
  Loop
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  '断开与数据库的连接
  DB_Disconnect
End Sub

Public Sub Update(ByVal OriStudentId As Integer)
  DB_Connect
    
  SqlStmt = "Update Students Set Student_Name='" + Trim(Student_Name) _
          + "',Sex='" + Trim(Sex) + "',Nationality='" + Trim(Nationality) _
          + "',Birth='" + Trim(Birth) + "',Political_Party='" + Trim(Political_Party) _
          + "',Family_Place='" + Trim(Family_Place) + "',Id_Card='" + Trim(Id_Card) _
          + "',BadgeID='" + Trim(BadgeID) + "',Home_phone='" + Trim(Home_phone) _
          + "',Residence='" + Trim(Residence) + "',Postcode='" + Trim(Postcode) _
          + "',InDate='" + Trim(InDate) + "',Class_Id=" + Trim(Str(Class_Id)) _
          + ",Title='" + Trim(Title) + "',Memo='" + Trim(Memo) + "',Fillin_Time='" _
          + Trim(Fillin_Time) + "' WHERE Student_Id=" + Trim(Str(OriStudentId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub

'更新班级信息
Public Sub Update_Transfer(ByVal OriStudentId As Integer)
  DB_Connect
  
  SqlStmt = "Update Students Set Class_Id=" + Trim(Class_Id) _
          + " WHERE Student_id=" + Trim(OriStudentId)
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub