frmstudentselect.frm

用VB实现的一个学生管理系统

         Charset         =   134
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      _Version        =   393216
   End
End
Attribute VB_Name = "frmStudentSelect"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub FormInitialize()
txtname.Text = ""
txtStatusID.Text = ""
txtnumber.Text = ""
With cobsex
  .Clear
  
  .AddItem "男"
  .AddItem "女"
  .ListIndex = 0
  .Enabled = False
  End With
  With cobinyear
 .Clear
 .AddItem "1985年"
  .AddItem "1986年"
  .AddItem "1987年"
  .AddItem "1988年"
   .AddItem "1989年"
  .AddItem "1990年"
   .AddItem "1991年"
  .AddItem "1992年"
   .AddItem "1993年"
  .AddItem "1994年"
   .AddItem "1995年"
  .AddItem "1996年"
   .AddItem "1997年"
  .AddItem "1998年"
   .AddItem "1999年"
  .AddItem "2001年"
  .AddItem "2002年"
  .AddItem "2003年"
  .AddItem "2004年"
  .AddItem "2005年"
  .AddItem "2006年"
  .AddItem "2007年"
  .AddItem "2008年"
  .AddItem "2009年"
  .AddItem "2010年"
  .AddItem "2011年"
  .AddItem "2012年"
  .AddItem "2013年"
  .AddItem "2014年"
  .AddItem "2015年"
  .AddItem "2016年"
  .AddItem "2017年"
  .AddItem "2018年"
  .AddItem "2018年"
  .AddItem "2020年"
    .ListIndex = 0
    .Enabled = False
End With



'设置民族
 Set adoRS = adoCon.Execute("select name from Nation")
With cobNation
  .Clear
  While Not adoRS.EOF
    .AddItem Trim(adoRS("name"))
    adoRS.MoveNext
    
   Wend
  .ListIndex = 0
  .Enabled = False
  
End With
'设置专业
Set adoRS = adoCon.Execute("select name from Speciality")
With cobSpeciality
  .Clear
  While Not adoRS.EOF
    .AddItem Trim(adoRS("name"))
    adoRS.MoveNext
    
   Wend
  .ListIndex = 0
  .Enabled = False
  End With
  '设置学院
Set adoRS = adoCon.Execute("select name from College")
With cobcollege
  .Clear
  While Not adoRS.EOF
    .AddItem Trim(adoRS("name"))
    adoRS.MoveNext
    
   Wend
  .ListIndex = 0
  .Enabled = False
  End With

 '设置班级
Set adoRS = adoCon.Execute("select name from Class")
With cobclass
  .Clear
  While Not adoRS.EOF
    .AddItem Trim(adoRS("name"))
    adoRS.MoveNext
    
   Wend
  .ListIndex = 0
  .Enabled = False
  End With

 '设置教师
Set adoRS = adoCon.Execute("select name from Teacher")
With cobteacher
  .Clear
  While Not adoRS.EOF
    .AddItem Trim(adoRS("name"))
    adoRS.MoveNext
    
   Wend
  .ListIndex = 0
  .Enabled = False
  End With


End Sub

Private Sub Check1_Click()
If Check1.Value = Checked Then
    cobclass.BackColor = &HC0FFFF   '淡黄色
    cobclass.Enabled = True
    cobclass.SetFocus
 ElseIf Check1.Value = Unchecked Then
    cobclass.BackColor = &H8000000F '灰色
    cobclass.Enabled = False
 End If

End Sub

Private Sub Check2_Click()
If Check2.Value = Checked Then
    cobcollege.BackColor = &HC0FFFF   '淡黄色
     cobcollege.Enabled = True
    cobcollege.SetFocus
 ElseIf Check2.Value = Unchecked Then
     cobcollege.BackColor = &H8000000F '灰色
     cobcollege.Enabled = False
 End If

End Sub



Private Sub ChkBirthday_Click()
If ChkBirthday.Value = 1 Then
   dtStart.Enabled = True
   dtEnd.Enabled = True
Else
   dtEnd.Enabled = False
   dtStart.Enabled = False
End If
End Sub

Private Sub chkinyear_Click()
  If chkinyear.Value = Checked Then
       cobinyear.BackColor = &HC0FFFF   '淡黄色
       cobinyear.Enabled = True
       cobinyear.SetFocus
    ElseIf chkinyear.Value = Unchecked Then
       cobinyear.BackColor = &H8000000F  '灰色
       cobinyear.Enabled = False
    End If

End Sub

Private Sub ChkName_Click()
    If ChkName.Value = Checked Then
       txtname.BackColor = &HC0FFFF   '淡黄色
       txtname.Enabled = True
       txtname.SetFocus
    ElseIf ChkName.Value = Unchecked Then
       txtname.BackColor = &H8000000F  '灰色
       txtname.Enabled = False
    End If


End Sub

Private Sub chkNation_Click()
If chkNation.Value = Checked Then
    cobNation.BackColor = &HC0FFFF   '淡黄色
    cobNation.Enabled = True
    cobNation.SetFocus
 ElseIf chkNation.Value = Unchecked Then
    cobNation.BackColor = &H8000000F '灰色
    cobNation.Enabled = False
 End If
End Sub

Private Sub chknumber_Click()
  If chknumber.Value = Checked Then
       txtnumber.BackColor = &HC0FFFF   '淡黄色
       txtnumber.Enabled = True
       txtnumber.SetFocus
    ElseIf chknumber.Value = Unchecked Then
       txtnumber.BackColor = &H8000000F  '灰色
       txtnumber.Enabled = False
    End If
End Sub

Private Sub chksex_Click()
 If chksex.Value = Checked Then
    cobsex.BackColor = &HC0FFFF   '淡黄色
    cobsex.Enabled = True
    cobsex.SetFocus
 ElseIf chksex.Value = Unchecked Then
    cobsex.BackColor = &H8000000F '灰色
    cobsex.Enabled = False
 End If
End Sub

Private Sub ChkSpeciality_Click()
If ChkSpeciality.Value = Checked Then
    cobSpeciality.BackColor = &HC0FFFF   '淡黄色
    cobSpeciality.Enabled = True
    cobSpeciality.SetFocus
 ElseIf ChkSpeciality.Value = Unchecked Then
    cobSpeciality.BackColor = &H8000000F '灰色
    cobSpeciality.Enabled = False
 End If
End Sub

Private Sub chkStatusID_Click()
If chkStatusID.Value = Checked Then
    txtStatusID.BackColor = &HC0FFFF   '淡黄色
    txtStatusID.Enabled = True
    txtStatusID.SetFocus
 ElseIf chkStatusID.Value = Unchecked Then
    txtStatusID.BackColor = &H8000000F '灰色
    txtStatusID.Enabled = False
 End If
End Sub

Private Sub chkteacher_Click()
If chkteacher.Value = Checked Then
    cobteacher.BackColor = &HC0FFFF   '淡黄色
    cobteacher.Enabled = True
    cobteacher.SetFocus
 ElseIf chkteacher.Value = Unchecked Then
    cobteacher.BackColor = &H8000000F '灰色
    cobteacher.Enabled = False
 End If

End Sub

Private Sub CmdExit_Click()
Unload Me
End Sub

Private Sub CmdFind_Click()
Dim sname, ssex, sNation, sSpeciality, sStatusID, sclass, steacher, sinyear, snumber, scollege As String
Dim SQL As String
If Option1.Value = True Then    '模糊查询
If ChkName.Value = 1 Then  '姓名
   sname = "Name like '%" & Trim(txtname.Text) & "%'"
Else
   sname = "Name like '%'"
End If
'性别
If chksex.Value = 1 Then
   ssex = "Sex='" & cobsex.Text & "'"
Else
   ssex = "Sex in('男','女')"
End If
'出生日期
If ChkBirthday.Value = 1 Then
  Sbirthday = "Birthday Between '" & Format(dtStart.Value, "yyyy-mm-dd") & "' and '" & Format(dtEnd.Value, "yyyy-mm-dd") & "'"
Else
  Sbirthday = "Birthday like '%'"
End If
'学号
If chknumber.Value = 1 Then
   snumber = "ID like '%" & Trim(txtnumber.Text) & "%'"
Else
   snumber = "ID like '%'"
End If
'学院
If Check2.Value = 1 Then
  scollege = "college like '%" & Trim(cobcollege.Text) & "%'"
  Else
  scollege = "college like '%'"
  End If
'专业
If ChkSpeciality.Value = 1 Then
  sSpeciality = "Speciality like '%" & Trim(cobSpeciality.Text) & "%'"
  Else
  sSpeciality = "Speciality like '%'"
  End If
'班级
If Check1.Value = 1 Then
  sclass = "Class like '%" & Trim(cobclass.Text) & "%'"
  Else
  sclass = "Class like '%'"
  End If
'教师
If chkteacher.Value = 1 Then
  steacher = "Teacher like '%" & Trim(cobteacher.Text) & "%'"
  Else
  steacher = "Teacher like '%'"
  End If
  '入学日期
If chkinyear.Value = 1 Then
  sinyear = "Inyear like '%" & Trim(cobinyear.Text) & "%'"
  Else
  sinyear = "Inyear like '%'"
  End If


'民族
If chkNation.Value = 1 Then
  sNation = "Nation like '%" & Trim(cobNation.Text) & "%'"
  
Else
  sNation = "Nation like '%'"
  
End If
'身份证号
If chkStatusID.Value = 1 Then
  sStatusID = "StatusID like '%" & Trim(txtStatusID.Text) & "%'"
  
Else
  sStatusID = "StatusID like '%'"
  
End If
ElseIf Option2.Value = True Then  '准确查询
If ChkName.Value = 1 Then  '姓名
   sname = "Name like '" & Trim(txtname.Text) & "'"
Else
   sname = "Name like '%'"
End If
'性别
If chksex.Value = 1 Then
   ssex = "Sex='" & cobsex.Text & "'"
Else
   ssex = "Sex in('男','女')"
End If
'出生日期
If ChkBirthday.Value = 1 Then
  Sbirthday = "Birthday Between '" & Format(dtStart.Value, "yyyy-mm-dd") & "' and '" & Format(dtEnd.Value, "yyyy-mm-dd") & "'"
Else
  Sbirthday = "Birthday like '%'"
End If
'学号
If chknumber.Value = 1 Then
   snumber = "ID like '" & Trim(txtnumber.Text) & "'"
Else
   snumber = "ID like '%'"
End If
'学院
If Check2.Value = 1 Then
  scollege = "college like '" & Trim(cobcollege.Text) & "'"
  Else
  scollege = "college like '%'"
  End If
'专业
If ChkSpeciality.Value = 1 Then
  sSpeciality = "Speciality like '" & Trim(cobSpeciality.Text) & "'"
  Else
  sSpeciality = "Speciality like '%'"
  End If
'班级
If Check1.Value = 1 Then
  sclass = "Class like '" & Trim(cobclass.Text) & "'"
  Else
  sclass = "Class like '%'"
  End If
'教师
If chkteacher.Value = 1 Then
  steacher = "Teacher like '" & Trim(cobteacher.Text) & "'"
  Else
  steacher = "Teacher like '%'"
  End If
  '入学日期
If chkinyear.Value = 1 Then
  sinyear = "Inyear like '" & Trim(cobinyear.Text) & "'"
  Else
  sinyear = "Inyear like '%'"
  End If


'民族
If chkNation.Value = 1 Then
  sNation = "Nation like '" & Trim(cobNation.Text) & "'"
  
Else
  sNation = "Nation like '%'"
  
End If
'身份证号
If chkStatusID.Value = 1 Then
  sStatusID = "StatusID like '" & Trim(txtStatusID.Text) & "'"
  
Else
  sStatusID = "StatusID like '%'"
  
End If


End If
If chknumber.Value = 0 And chkteacher.Value = 0 And ChkBirthday.Value = 0 And chkinyear.Value = 0 And Check1.Value = 0 And ChkName.Value = 0 And chksex.Value = 0 And ChkSpeciality.Value = 0 And chkNation.Value = 0 And chkStatusID.Value = 0 And Check2.Value = 0 Then
MsgBox "请选择查询内容！", vbOKOnly + vbExclamation, "系统提示"
Exit Sub
End If
  SQL = "Select '学号'=ID,'姓名'=Name,'性别'=Sex,'出生日期'=Birthday,"
  SQL = SQL & "'民族'=Nation,'学院'=College,'专业'=Speciality,'班级'=Class,'班主任'=Teacher,'入学年份'=Inyear,'联系电话'=Phone,"
  SQL = SQL & "'身份证号'=StatusID,'电子信箱'=Email,'家长姓名'=HouseName,"
  SQL = SQL & "'家长电话'=HousePhone,'家庭地址'=HouseAddress,'邮政编码'=PostCode,"
  SQL = SQL & "'备注'=Memo from Student where "
  SQL = SQL & sname
  SQL = SQL & " And " & snumber
  SQL = SQL & " And " & ssex
  SQL = SQL & " And " & Sbirthday
  SQL = SQL & " And " & sNation
  SQL = SQL & " And " & scollege
  SQL = SQL & " and " & sSpeciality
  SQL = SQL & " And " & sclass
  SQL = SQL & " And " & steacher
  SQL = SQL & " And " & sinyear
  SQL = SQL & " And " & sStatusID
 
AdoFind.ConnectionString = "provider=sqloledb.1;server=(local);user id=sa;pwd=;database=MyDB"
AdoFind.RecordSource = SQL
AdoFind.Refresh
If AdoFind.Recordset.BOF And AdoFind.Recordset.EOF Then
  MsgBox "没有找到你需要的数据！", vbOKOnly + vbExclamation, "系统提示"
End If
MousePointer = Default
CmdFind.Enabled = True
CmdShowAll.Enabled = True
CmdExit.Enabled = True
End Sub


Private Sub CmdShowAll_Click()
Dim SQL As String
Dim sCon As String
  SQL = "Select 学号=ID,姓名=Name,性别=Sex,出生日期=Birthday,"
  SQL = SQL & "民族=Nation,学院=College,专业=Speciality,班级=Class,班主任=Teacher,入学年份=Inyear,联系电话=Phone,"
  SQL = SQL & "身份证号=StatusID,电子信箱=Email,家长姓名=HouseName,"
  SQL = SQL & "家长电话=HousePhone,家庭地址=HouseAddress,邮政编码=PostCode,"
  SQL = SQL & "备注=Memo from Student"
  sCon = "provider=sqloledb.1;Server=(local);Uid=sa;Pwd=;Database=MyDB"
  AdoFind.ConnectionString = sCon
  AdoFind.RecordSource = SQL
  AdoFind.Refresh
  
End Sub


Private Sub cobSpeciality_Click()
'班级
SQL = ""
SQL = "select 班级 from Spclass where  专业='" & cobSpeciality.Text & "'"
Set adoRS = adoCon.Execute(SQL)

cobclass.Clear
Do While Not adoRS.EOF
  cobclass.AddItem adoRS("班级")
  adoRS.MoveNext
Loop

End Sub

Private Sub Form_Load()
ShockwaveFlash1.Movie = App.Path & "\flash\top.swf"

On Error GoTo errMsg
Me.Move (Screen.Width - Me.Width) / 2, (Screen.Height - Me.Height) / 2 - 600
Call FormInitialize
txtnumber.Enabled = False
cobteacher.Enabled = False
errMsg:
  If Err.Number <> 0 Then
      MsgBox Err.Number & Err.Description, vbOKOnly + vbCritical, "出错提示"
     Exit Sub
  End If

End Sub