智能排課系統(tǒng)vb代碼

1、Vb核心代碼設(shè)計(jì)一：輸入界面與代碼（以班級(jí)表為例）Dim Conn As New ADODB.Connection 連接變量Dim SqlString As String 查詢字符串Dim Connstring As String 連接字符串Dim RS1 As New ADODB.Recordset 記錄集變量Dim bm 定義一個(gè)變量用來(lái)存放添加記錄時(shí)原來(lái)記錄的位置Dim tj As BooleanPrivate Sub Command10_Click()Form4.HideForm3.ShowEnd SubPrivate Sub Command1_Click() 移到首記錄 RS1.Mo

2、veFirst Command1.Enabled = False Command2.Enabled = False Command3.Enabled = True Command4.Enabled = True Call DispValue 顯示當(dāng)前記錄的值End SubPrivate Sub Command2_Click() 前移 RS1.MovePrevious If RS1.BOF Then RS1.MoveFirst Command1.Enabled = False Command2.Enabled = False End If Command3.Enabled = True Comm

3、and4.Enabled = True Call DispValue 顯示當(dāng)前記錄的值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 顯示當(dāng)前記錄的值End SubPrivate Sub Command4_Click() 移到末記錄 RS1.Mo

4、veLast Command3.Enabled = False Command4.Enabled = False Command1.Enabled = True Command2.Enabled = True Call DispValue 顯示當(dāng)前記錄的值End SubPrivate Sub Command5_Click() 刪除記錄 If (MsgBox(真的要?jiǎng)h除該記錄嗎？, vbQuestion + vbYesNo, 刪除提示) = vbYes) Then RS1.Delete 刪除 RS1.MoveNext If RS1.EOF Then 如果刪除的是末記錄 RS1.MoveLast

5、End If End If Call DispValue 顯示當(dāng)前記錄的值End SubPrivate Sub Command6_Click() 添加記錄 bm = RS1.Bookmark 用書簽記下當(dāng)前記錄位置 RS1.AddNew 調(diào)用AddNew方法增加新記錄 Call EditStatus 置按鈕和文本框狀態(tài) Call EmptyText 清空文本框 tj = True 是添加新記錄End SubPrivate Sub Command7_Click() 修改記錄 Call EditStatus 置按鈕和文本框狀態(tài) tj = False 不是添加記錄，是修改記錄End SubPriva

6、te Sub Command8_Click() 確定操作 Call SetValue 給各字段賦值 RS1.Update 調(diào)用Update方法保存 Call OkStatus 置按鈕和文本框狀態(tài) MsgBox (保存成功) 給出保存成功提示End SubPrivate Sub Command9_Click() 取消操作 If (MsgBox(真的要取消修改嗎？, vbQuestion + vbYesNo) = vbYes Then RS1.CancelUpdate If tj Then 如果是增加新記錄 RS1.Bookmark = bm 把記錄指針移動(dòng)到原來(lái)的位置 End If Call D

7、ispValue 顯示當(dāng)前記錄的值 Call OkStatus 置按鈕和文本框狀態(tài) End IfEnd SubPrivate Sub Form_Load() Connstring = Driver=SQL Server;Server=PC2012110412ewr.;Database=GXPK; _ & uid =sa;pwd=t123 SQL Server普通安全模式建立到Pubs數(shù)據(jù)庫(kù)的連接的連接字符串 設(shè)置連接字符串 With Conn .ConnectionString = Connstring 給ConnectionString屬性賦值 .ConnectionTimeout = 10

8、 .Open 給連接的ConnectionString屬性賦值，然后使用求帶參數(shù)的Open方法打開連接 End With MsgBox (連接成功) RS1.CursorType = adOpenDynamic RS1.CursorLocation = adUseClient 游標(biāo)服務(wù)的位置、默認(rèn)值adUseServer。游標(biāo)用于：控制記錄定位、控制其他用戶對(duì)數(shù)據(jù)庫(kù)所作的更改的可視性、控制數(shù)據(jù)可更新性 RS1.LockType = adLockOptimistic RS1.Open 班級(jí)信息表, Conn, 3, 3 If RS1.EOF And RS1.BOF Then 如果表為空 MsgB

9、ox 表為空，請(qǐng)?zhí)砑佑涗?Else Call DispValue 顯示當(dāng)前記錄的值 End If Call TextDisable 使文本框不可用 Command8.Enabled = False Command9.Enabled = FalseEnd SubPrivate Sub DispValue() Text1.Text = RS1.Fields(班級(jí)編號(hào)) If IsNull(Trim(RS1.Fields(班級(jí)名稱) Then 解決課程名字段值為空移指針出錯(cuò)（無(wú)效使用NULL）的問(wèn)題，其他字段類推。 Text2.Text = Else Text2.Text = RS1.Fields(

10、班級(jí)名稱) End If Text5.Text = RS1.Fields(所屬系) Text7.Text = RS1.Fields(學(xué)生人數(shù))End SubPrivate Sub SetValue() RS1.Fields(班級(jí)編號(hào)) = Text1.Text RS1.Fields(班級(jí)名稱) = Text2.Text RS1.Fields(所屬系稱) = Text5.Text RS1.Fields(學(xué)生人數(shù)) = Text7.TextEnd SubPrivate Sub TextEnable() 使所有的文本框可用 Text1.Enabled = True Text2.Enabled = T

11、rue Text5.Enabled = True Text7.Enabled = TrueEnd SubPrivate Sub TextDisable() 使所有的文本框不可用 Text1.Enabled = False Text2.Enabled = False Text5.Enabled = False Text7.Enabled = FalseEnd SubPrivate Sub EditStatus() 設(shè)置在編輯或添加記錄時(shí)的各控件狀態(tài) Command1.Enabled = False Command2.Enabled = False Command3.Enabled = False

12、 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() 設(shè)置不在編輯或添加記錄時(shí)的各控件狀

13、態(tài) 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 連接變量

14、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數(shù)據(jù)庫(kù)的連接的連接字符串 設(shè)置連接字符串 With Conn .Co

15、nnectionString = Connstring 給ConnectionString屬性賦值 .ConnectionTimeout = 10 .Open 給連接的ConnectionString屬性賦值，然后使用求帶參數(shù)的Open方法打開連接 End With MsgBox (連接成功) Command1.Enabled = False 連接按鈕不可用 Command2.Enabled = True 顯示數(shù)據(jù)可用End SubPrivate Sub Command2_Click() Set Cmd.ActiveConnection = Conn 設(shè)置Command對(duì)象的ActiveCon

16、nection為某個(gè)打開的連接 SqlString = Select 課程名稱,教師姓名,上課教室,班級(jí)編號(hào),上課時(shí)間 from 排課表 Where 上課教室 like & Text1.Text & 形成查詢字符串1 Cmd.CommandText = SqlString 設(shè)置Command對(duì)象執(zhí)行的命令字符串 Cmd.CommandType = adCmdText 設(shè)置命令類型,表示CommandText中為一命令文本 ,童P188 Set RS1 = Cmd.Execute 執(zhí)行命令得到一個(gè)結(jié)果記錄集 Call ShowData(RS1, MSFlexGrid1) 調(diào)用過(guò)程顯示記錄集中的數(shù)

17、據(jù) Command1.Visible = TrueEnd SubPrivate Sub ShowData(Rs As ADODB.Recordset, Dgrid As MSFlexGrid) 該過(guò)程用來(lái)在Dgrid網(wǎng)格中顯示記錄集RS中的內(nèi)容 Dim RowNum As Integer RowNum = 1 代表MsFlexGrid控件的行數(shù) Dgrid.Rows = RowNum 設(shè)置MsFlexGrid控件的行數(shù) Dgrid.Cols = Rs.Fields.Count 設(shè)置MsFlexGrid控件的列數(shù) For j = 0 To Rs.Fields.Count - 1 遍列所有列 Dg

18、rid.TextMatrix(0, j) = Rs.Fields(j).Name 把列名顯示在設(shè)置MsFlexGrid控件的首行 Next j Do While Not Rs.EOF 如果不是記錄集的結(jié)尾 RowNum = RowNum + 1 行數(shù)加1 Dgrid.Rows = RowNum 設(shè)置MsFlexGrid控件的行數(shù) For j = 0 To Rs.Fields.Count - 1 遍列所有列 If Not IsNull(Rs.Fields(j).Value) Then 如果列中的數(shù)據(jù)不為空,切記不能用如 if Var1=Null來(lái)判斷變量Var1的值是否為Null Dgrid.T

19、extMatrix(RowNum - 1, j) = Rs.Fields(j).Value 把列的數(shù)據(jù)顯示在MsFlexGrid的相應(yīng)網(wǎng)格中 End If Next j Rs.MoveNext 記錄指針下移 Loop Command2.Enabled = FalseEnd SubPrivate Sub Command3_Click() Form14.Hide Form3.ShowEnd SubPrivate Sub Command4_Click() RS1.Close 關(guān)閉記錄集 Conn.Close 關(guān)閉連接Text1.Text = Command1.Enabled = True 連接按鈕不

20、可用 Command2.Enabled = True 顯示數(shù)據(jù)可用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()進(jìn)行初始化操作 Dim C

21、ounnter As Integer Connstring = Driver=SQL Server;Server=PC2012110412ewr.;Database=GXPK; _ & uid =sa;pwd=t123 SQL Server普通安全模式建立到Pubs數(shù)據(jù)庫(kù)的連接的連接字符串 設(shè)置連接字符串 With Conn .ConnectionString = Connstring 給ConnectionString屬性賦值 .ConnectionTimeout = 10 .Open 給連接的ConnectionString屬性賦值，然后使用求帶參數(shù)的Open方法打開連接 End With

22、 MsgBox (連接成功) ProgressBar1.Visible = True ProgressBar1.Max = 200 ProgressBar1.Value = ProgressBar1.Min msg = MsgBox(您確實(shí)要初始化這些數(shù)據(jù)嗎?, vbYesNo) If msg = vbNo Then Exit Sub For Counnter = ProgressBar1.Min To ProgressBar1.Max ProgressBar1.Value = Counnter If Check1.Value = 1 Then 如果班級(jí)信息表被選中 刪除全部提醒信息數(shù)據(jù) SQ

23、L = delete from 班級(jí)信息表 Conn.Execute SQL End If If Check2.Value = 1 Then 如果課程信息表被選中 刪除全部通訊日志數(shù)據(jù) SQL = delete from 課程信息表 Conn.Execute SQL End If If Check3.Value = 1 Then 如果班級(jí)課程信息表被選中 刪除全部群組信息數(shù)據(jù) SQL = delete from 任課表 Conn.Execute SQL End If If Check4.Value = 1 Then 如果教師信息表被選中 刪除全部聯(lián)系人信息數(shù)據(jù) SQL = delete fro

24、m 教師信息表 Conn.Execute SQL End If If Check6.Value = 1 Then 如果排課信息表被選中 刪除全部聯(lián)系人信息數(shù)據(jù) 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(Ca

25、ncel As Integer)退出操作 Form15.Hide Form3.ShowEnd Sub4、查看自動(dòng)排課窗口及代碼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 SubP

26、rivate 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數(shù)據(jù)庫(kù)的連接的連接字符串 定義數(shù)據(jù)庫(kù)連接 With Conn .ConnectionString = constr 給ConnectionString屬性賦值 .ConnectionTimeout = 10 .Open 給連

27、接的ConnectionString屬性賦值，然后使用求帶參數(shù)的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.

28、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

29、 P1.Value = dlaytimeEnd IfEnd Sub5、查看手動(dòng)調(diào)課窗口及代碼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

30、 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數(shù)據(jù)庫(kù)的連接的連接字符串 設(shè)置連接字符串 With Conn .ConnectionString = Connstring 給ConnectionString屬性賦值 .ConnectionTimeout = 1

31、0 .Open 給連接的ConnectionString屬性賦值，然后使用求帶參數(shù)的Open方法打開連接 End With Set Cmd.ActiveConnection = Conn 設(shè)置Command對(duì)象的ActiveConnection為某個(gè)打開的連接 SqlString = Select 課程名稱,教師姓名,上課教室,班級(jí)編號(hào),上課時(shí)間 from 排課表 形成查詢字符串1 Cmd.CommandText = SqlString 設(shè)置Command對(duì)象執(zhí)行的命令字符串 Cmd.CommandType = adCmdText 設(shè)置命令類型,表示CommandText中為一命令文本 ,童P

32、188 Set RS1 = Cmd.Execute 執(zhí)行命令得到一個(gè)結(jié)果記錄集 Call ShowData(RS1, MSFlexGrid1) 調(diào)用過(guò)程顯示記錄集中的數(shù)據(jù)End SubPrivate Sub Form_Load() constr = Driver=SQL Server;Server=PC2012110412ewr.;Database=GXPK; _ & uid =sa;pwd=t123 SQL Server普通安全模式建立到Pubs數(shù)據(jù)庫(kù)的連接的連接字符串 定義數(shù)據(jù)庫(kù)連接 With Conn .ConnectionString = constr 給ConnectionStrin

33、g屬性賦值 .ConnectionTimeout = 10 .Open 給連接的ConnectionString屬性賦值，然后使用求帶參數(shù)的Open方法打開連接 End With MsgBox (連接成功)End SubPrivate Sub Command1_Click() 調(diào)整教室Dim rt As New ADODB.RecordsetIf (Text1.Text = Or Text2.Text = Or Text3.Text = Or Text4.Text = Or Text5.Text = ) ThenMsgBox (請(qǐng)輸入完整信息)ElseSet Rs = New ADODB.Re

34、cordsetSQL = select * from 排課表Set Rs = Conn.Execute(SQL) If Rs(上課教室) = Text4.Text Then MsgBox (該時(shí)間段該教室已被占用，請(qǐng)重新選擇) Text4.Text = Text5.Text = ElseSet Rs = New ADODB.RecordsetSQL = update 排課表 set 上課教室= + Text4.Text + where 課程名稱= + Text1.Text + and 教師姓名= + Text2.Text + and 上課時(shí)間= + Text5.Text + Set Rs = Conn.Execute(SQL) MsgBox (教室調(diào)整成功) Text1.Text = Text2.Text = Text3.Text = Text4.Text = Text5.Text = End If End IfEnd SubPrivate Sub Command3_Click() 調(diào)整時(shí)間Dim rt As New ADODB.RecordsetIf (Text1.Text = Or Text2.Text = Or Text3.Text = Or Text4.Text = Or Text5.Text = ) T