智能排课系统vb代码.doc

Vb核心代码设计一：输入界面与代码（以班级表为例）Dim Conn As New ADODB.Connection 连接变量Dim SqlString As String 查询字符串Dim Connstring As String 连接字符串Dim RS1 As New ADODB.Recordset 记录集变量Dim bm 定义一个变量用来存放添加记录时原来记录的位置Dim tj As BooleanPrivate Sub Command10_Click()Form4.HideForm3.ShowEnd SubPrivate Sub Command1_Click() 移到首记录 RS1.MoveFirst Command1.Enabled = False Command2.Enabled = False Command3.Enabled = True Command4.Enabled = True Call DispValue 显示当前记录的值End SubPrivate Sub Command2_Click() 前移 RS1.MovePrevious If RS1.BOF Then RS1.MoveFirst Command1.Enabled = False Command2.Enabled = False End If Command3.Enabled = True Command4.Enabled = True Call DispValue 显示当前记录的值End SubPrivate Sub Command3_Click() 后移 RS1.MoveNext If RS1.EOF Then RS1.MoveLast Command3.Enabled = False Command4.Enabled = False End If Command1.Enabled = True Command2.Enabled = True Call DispValue 显示当前记录的值End SubPrivate Sub Command4_Click() 移到末记录 RS1.MoveLast Command3.Enabled = False Command4.Enabled = False Command1.Enabled = True Command2.Enabled = True Call DispValue 显示当前记录的值End SubPrivate Sub Command5_Click() 删除记录 If (MsgBox(真的要删除该记录吗？, vbQuestion + vbYesNo, 删除提示) = vbYes) Then RS1.Delete 删除 RS1.MoveNext If RS1.EOF Then 如果删除的是末记录 RS1.MoveLast End If End If Call DispValue 显示当前记录的值End SubPrivate Sub Command6_Click() 添加记录 bm = RS1.Bookmark 用书签记下当前记录位置 RS1.AddNew 调用AddNew方法增加新记录 Call EditStatus 置按钮和文本框状态 Call EmptyText 清空文本框 tj = True 是添加新记录End SubPrivate Sub Command7_Click() 修改记录 Call EditStatus 置按钮和文本框状态 tj = False 不是添加记录，是修改记录End SubPrivate Sub Command8_Click() 确定操作 Call SetValue 给各字段赋值 RS1.Update 调用Update方法保存 Call OkStatus 置按钮和文本框状态 MsgBox (保存成功) 给出保存成功提示End SubPrivate Sub Command9_Click() 取消操作 If (MsgBox(真的要取消修改吗？, vbQuestion + vbYesNo) = vbYes Then RS1.CancelUpdate If tj Then 如果是增加新记录 RS1.Bookmark = bm 把记录指针移动到原来的位置 End If Call DispValue 显示当前记录的值 Call OkStatus 置按钮和文本框状态 End IfEnd SubPrivate Sub Form_Load() Connstring = Driver=SQL Server;Server=PC2012110412ewr.;Database=GXPK; _ & uid =sa;pwd=t123 SQL Server普通安全模式建立到Pubs数据库的连接的连接字符串 设置连接字符串 With Conn .ConnectionString = Connstring 给ConnectionString属性赋值 .ConnectionTimeout = 10 .Open 给连接的ConnectionString属性赋值，然后使用求带参数的Open方法打开连接 End With MsgBox (连接成功) RS1.CursorType = adOpenDynamic RS1.CursorLocation = adUseClient 游标服务的位置、默认值adUseServer。游标用于：控制记录定位、控制其他用户对数据库所作的更改的可视性、控制数据可更新性 RS1.LockType = adLockOptimistic RS1.Open 班级信息表, Conn, 3, 3 If RS1.EOF And RS1.BOF Then 如果表为空 MsgBox 表为空，请添加记录 Else Call DispValue 显示当前记录的值 End If Call TextDisable 使文本框不可用 Command8.Enabled = False Command9.Enabled = FalseEnd SubPrivate Sub DispValue() Text1.Text = RS1.Fields(班级编号) If IsNull(Trim(RS1.Fields(班级名称) Then 解决课程名字段值为空移指针出错（无效使用NULL）的问题，其他字段类推。 Text2.Text = Else Text2.Text = RS1.Fields(班级名称) End If Text5.Text = RS1.Fields(所属系) Text7.Text = RS1.Fields(学生人数)End SubPrivate Sub SetValue() RS1.Fields(班级编号) = Text1.Text RS1.Fields(班级名称) = Text2.Text RS1.Fields(所属系称) = Text5.Text RS1.Fields(学生人数) = Text7.TextEnd SubPrivate Sub TextEnable() 使所有的文本框可用 Text1.Enabled = True Text2.Enabled = True Text5.Enabled = True Text7.Enabled = TrueEnd SubPrivate Sub TextDisable() 使所有的文本框不可用 Text1.Enabled = False Text2.Enabled = False Text5.Enabled = False Text7.Enabled = FalseEnd SubPrivate Sub EditStatus() 设置在编辑或添加记录时的各控件状态 Command1.Enabled = False Command2.Enabled = False Command3.Enabled = False Command4.Enabled = False Command5.Enabled = False Command6.Enabled = False Command7.Enabled = False Command8.Enabled = True Command9.Enabled = True Call TextEnableEnd SubPrivate Sub EmptyText() 清空文本框 Text1.Text = Text2.Text = Text5.Text = Text7.Text = End SubPrivate Sub OkStatus() 设置不在编辑或添加记录时的各控件状态 Command1.Enabled = True Command2.Enabled = True Command3.Enabled = True Command4.Enabled = True Command5.Enabled = True Command6.Enabled = True Command7.Enabled = True Command8.Enabled = False Command9.Enabled = False Call TextDisableEnd Sub2、查看课表窗口及代码（以教室课表为例）Dim Conn As New ADODB.Connection 连接变量Dim SqlString As String 查询字符串Dim Connstring As String 连接字符串Dim RS1 As New ADODB.Recordset 记录集变量Dim Cmd As New ADODB.CommandPrivate Sub Command1_Click() Connstring = Driver=SQL Server;Server=PC2012110412ewr.;Database=GXPK; _ & uid =sa;pwd=t123 SQL Server普通安全模式建立到Pubs数据库的连接的连接字符串 设置连接字符串 With Conn .ConnectionString = Connstring 给ConnectionString属性赋值 .ConnectionTimeout = 10 .Open 给连接的ConnectionString属性赋值，然后使用求带参数的Open方法打开连接 End With MsgBox (连接成功) Command1.Enabled = False 连接按钮不可用 Command2.Enabled = True 显示数据可用End SubPrivate Sub Command2_Click() Set Cmd.ActiveConnection = Conn 设置Command对象的ActiveConnection为某个打开的连接 SqlString = Select 课程名称,教师姓名,上课教室,班级编号,上课时间 from 排课表 Where 上课教室 like & Text1.Text & 形成查询字符串1 Cmd.CommandText = SqlString 设置Command对象执行的命令字符串 Cmd.CommandType = adCmdText 设置命令类型,表示CommandText中为一命令文本 ,童P188 Set RS1 = Cmd.Execute 执行命令得到一个结果记录集 Call ShowData(RS1, MSFlexGrid1) 调用过程显示记录集中的数据 Command1.Visible = TrueEnd SubPrivate Sub ShowData(Rs As ADODB.Recordset, Dgrid As MSFlexGrid) 该过程用来在Dgrid网格中显示记录集RS中的内容 Dim RowNum As Integer RowNum = 1 代表MsFlexGrid控件的行数 Dgrid.Rows = RowNum 设置MsFlexGrid控件的行数 Dgrid.Cols = Rs.Fields.Count 设置MsFlexGrid控件的列数 For j = 0 To Rs.Fields.Count - 1 遍列所有列 Dgrid.TextMatrix(0, j) = Rs.Fields(j).Name 把列名显示在设置MsFlexGrid控件的首行 Next j Do While Not Rs.EOF 如果不是记录集的结尾 RowNum = RowNum + 1 行数加1 Dgrid.Rows = RowNum 设置MsFlexGrid控件的行数 For j = 0 To Rs.Fields.Count - 1 遍列所有列 If Not IsNull(Rs.Fields(j).Value) Then 如果列中的数据不为空,切记不能用如 if Var1=Null来判断变量Var1的值是否为Null Dgrid.TextMatrix(RowNum - 1, j) = Rs.Fields(j).Value 把列的数据显示在MsFlexGrid的相应网格中 End If Next j Rs.MoveNext 记录指针下移 Loop Command2.Enabled = FalseEnd SubPrivate Sub Command3_Click() Form14.Hide Form3.ShowEnd SubPrivate Sub Command4_Click() RS1.Close 关闭记录集 Conn.Close 关闭连接Text1.Text = Command1.Enabled = True 连接按钮不可用 Command2.Enabled = True 显示数据可用End SubPrivate Sub Command5_Click()DataReport1.Show vbModal, MeEnd Sub3、查看初始化窗口及代码Option ExplicitDim SQL As StringDim Conn As New ADODB.Connection 连接变量Dim SqlString As String 查询字符串Dim Connstring As StringDim msg As String 连接字符串Private Sub CmdSave_Click()进行初始化操作 Dim Counnter As Integer Connstring = Driver=SQL Server;Server=PC2012110412ewr.;Database=GXPK; _ & uid =sa;pwd=t123 SQL Server普通安全模式建立到Pubs数据库的连接的连接字符串 设置连接字符串 With Conn .ConnectionString = Connstring 给ConnectionString属性赋值 .ConnectionTimeout = 10 .Open 给连接的ConnectionString属性赋值，然后使用求带参数的Open方法打开连接 End With MsgBox (连接成功) ProgressBar1.Visible = True ProgressBar1.Max = 200 ProgressBar1.Value = ProgressBar1.Min msg = MsgBox(您确实要初始化这些数据吗?, vbYesNo) If msg = vbNo Then Exit Sub For Counnter = ProgressBar1.Min To ProgressBar1.Max ProgressBar1.Value = Counnter If Check1.Value = 1 Then 如果班级信息表被选中 删除全部提醒信息数据 SQL = delete from 班级信息表 Conn.Execute SQL End If If Check2.Value = 1 Then 如果课程信息表被选中 删除全部通讯日志数据 SQL = delete from 课程信息表 Conn.Execute SQL End If If Check3.Value = 1 Then 如果班级课程信息表被选中 删除全部群组信息数据 SQL = delete from 任课表 Conn.Execute SQL End If If Check4.Value = 1 Then 如果教师信息表被选中 删除全部联系人信息数据 SQL = delete from 教师信息表 Conn.Execute SQL End If If Check6.Value = 1 Then 如果排课信息表被选中 删除全部联系人信息数据 SQL = delete from 排课表 Conn.Execute SQL End If Next Counnter ProgressBar1.Visible = False ProgressBar1.Value = ProgressBar1.MinEnd SubPrivate Sub CmdExit_Click()退出操作 Form15.Hide Form3.ShowEnd SubPrivate Sub Form_Unload(Cancel As Integer)退出操作 Form15.Hide Form3.ShowEnd Sub4、查看自动排课窗口及代码Dim Conn As New ADODB.Connection 连接变量Dim SqlString As String 查询字符串Dim Connstring As StringDim dlaytime As IntegerPrivate Sub Command2_Click()Dim SQL As StringDim sc As ADODB.RecordsetSet sc = New ADODB.RecordsetSQL = deletel 排课表 End SubPrivate Sub Command3_Click()Form9.HideForm3.ShowEnd SubPrivate Sub Form_Load() constr = Driver=SQL Server;Server=PC2012110412ewr.;Database=GXPK; _ & uid =sa;pwd=t123 SQL Server普通安全模式建立到Pubs数据库的连接的连接字符串 定义数据库连接 With Conn .ConnectionString = constr 给ConnectionString属性赋值 .ConnectionTimeout = 10 .Open 给连接的ConnectionString属性赋值，然后使用求带参数的Open方法打开连接 End With MsgBox (连接成功) ProgressBar1.Visible = True ProgressBar1.Max = 200 ProgressBar1.Value = ProgressBar1.MinEnd SubPrivate Sub Command1_Click() 开始Dim SQL, sqll, sqlll As StringDim rt As New ADODB.RecordsetDim rm As New ADODB.RecordsetDim rn As New ADODB.RecordsetDim rp As New ADODB.RecordsetDim rq As New ADODB.RecordsetDim i As IntegerSet Rs = New ADODB.RecordsetSQL = select * from 任课表 Set Rs = Conn.Execute(SQL)ProgressBar1.Max = 100Do While ProgressBar1.Value = 100 Then Timer1.Enabled = False Load frmLogin frmLogin.ShowElse dlaytime = dlaytime + 1 P1.Value = dlaytimeEnd IfEnd Sub5、查看手动调课窗口及代码Dim Conn As New ADODB.Connection 连接变量Dim SqlString As String 查询字符串Dim Connstring As StringPrivate Sub Command2_Click()Form11.HideForm3.ShowEnd SubPrivate Sub Command5_Click()Dim Conn As New ADODB.Connection 连接变量Dim SqlString As String 查询字符串Dim Connstring As String 连接字符串Dim RS1 As New ADODB.Recordset 记录集变量Dim Cmd As New ADODB.CommandConnstring = Driver=SQL Server;Server=PC2012110412ewr.;Database=GXPK; _ & uid =sa;pwd=t123 SQL Server普通安全模式建立到Pubs数据库的连接的连接字符串 设置连接字符串 With Conn .ConnectionString = Connstring 给ConnectionString属性赋值 .ConnectionTimeout = 10 .Open 给连接的ConnectionString属性赋值，然后使用求带参数的Open方法打开连接 End With Set Cmd.ActiveConnection = Conn 设置Command对象的ActiveConnection为某个打开的连接 SqlString = Select 课程名称,教师姓名,上课教室,班级编号,上课时间 from 排课表 形成查询字符串1 Cmd.CommandText = SqlString 设置Command对象执行的命令字符串 Cmd.CommandType = adCmdText 设置命令类型,表示CommandText中为一命令文本 ,童P188 Set RS1 = Cmd.Execute 执行命令得到一个结果记录集 Call ShowData(RS1, MSFlexGrid1) 调用过程显示记录集中的数据End SubPrivate Sub Form_Load() constr = Driver=SQL Server;Server=PC2012110412ewr.;Database=GXPK; _ & uid =sa;pwd=t123 SQL Server普通安全模式建立到Pubs数据库的连接的连接字符串 定义数据库连接 With Conn .ConnectionString = constr 给ConnectionString属性赋值 .ConnectionTimeout = 10 .Open 给连接的ConnectionString属性赋值，然后使用求带参数的Open方法打开连接 End With MsgBox (连接成功)End SubPrivate Sub Command1_Click() 调整教室Dim rt As New ADODB.RecordsetIf (Text1.Text = Or Text2.Text = Or Text3.Text = Or Text4.Text = Or Text5.Text = ) ThenMsgBox (请输入完整信息)ElseSet Rs = New ADODB.RecordsetSQL = select * from 排课表Set Rs = Conn.Execute(SQL) If Rs(上课教室) = Text4.Text Then MsgBox (该时间段该教室已被占用，请重新选择) Text4.Text = Text5.Text = ElseSet Rs = New ADODB.RecordsetSQL = update 排课表 set 上课教室= + Text4.Text + where 课程名称= + Text1.Text + and 教师姓名= + Text2.Text + and 上课时间= + Text5.Text + Set Rs = Conn.Execute(SQL) MsgBox (教室调整成功) Text1.Text = Text2.Text = Text3.Text = Text4.Text = Text5.Text = End If End IfEnd SubPrivate Sub Command3_Click() 调整时间Dim rt As New ADODB.RecordsetIf (Text1.Text = Or Text2.Text = Or Text3.Text = Or Text4.Text = Or Text5.Text = ) ThenMsgBox (请输入完整信息)ElseSet Rs = New ADODB.RecordsetSQL = select * from 排课表Set Rs = Conn.Execute(SQL) If Rs(上课时间) = Text5.Text Then MsgBox (该教室该时间段已被占用，请重新选择) Text4.Text = Text5.Text = Else Set Rs = New ADODB.Recordset SQL = update 排课表 set 上课时间= + Text5.Text + where 课程名称= + Text1.Text + and 教师姓名= + Text2.Text + and 教室名称= + Text4.Text + Set Rs = Conn.Execute(SQL) MsgBox (时间调整成功) Adodc1.Refresh Text1.Text = Text2.Text = Text3.Text = Text4.Text = Text5.Text = End IfEnd IfEnd SubPrivate Sub ShowData(Rs As ADODB.Recordset, Dgrid As MSFlexGrid) 该过程用来在Dgrid网格中显示记录集RS中的内容 Dim RowNum As Integer RowNum = 1 代表MsFlexGrid控件的行数 Dgrid.Rows = RowNum 设置MsFlexGrid控件的行数 Dgrid.Cols = Rs.Fields.Count 设置MsFlexGrid控件的列数 For j = 0 To Rs.Fields.Count - 1 遍列所有列 Dgrid.TextMatrix(0, j) = Rs.Fields(j).Name 把列名显示在设置MsFlexGrid控件的首行 Next j Do While Not Rs.EOF 如果不是记录集的结尾 RowNum = RowNum + 1 行数加1 Dgrid.Rows = RowNum 设置MsFlexGrid控件的行数 For j = 0 To Rs.Fields.Count - 1 遍列所有列 If Not IsNull(Rs.Fields(j).Value) Then 如果列中的数据不为空,切记不能用如 if Var1=Null来判断变量Var1的值是否为Null Dgrid.TextMatrix(RowNum - 1, j) = Rs.Fields(j).Value 把列的数据显示在MsFlexGrid的相应网格中 End If Next j Rs.MoveNext 记录指针下移 LoopEnd Sub