frmsearch.frm

< SQL SERVER 2000 案例教程>>,冶金工业出版社,这本书的代码

        Dim rsPunish As New Recordset
        
        If cnn.State <> adStateOpen Then
            cnn.Open strCnn
        End If
        cmdPunish.ActiveConnection = cnn
        cmdPunish.CommandType = adCmdStoredProc
        cmdPunish.CommandText = "stu_GetPunishByNO"
        cmdPunish.Parameters.Append cmdPunish.CreateParameter("@stuNo", adChar, adParamInput, 6)
        cmdPunish.Parameters("@stuNo").Value = stuNo
        
        ' 绑定
        With rsPunish
                .ActiveConnection = cnn
                .CursorLocation = adUseClient
                .CursorType = adOpenStatic
        End With
        
        Set rsPunish = cmdPunish.Execute()
        Set MSHFlexGridPunish.DataSource = rsPunish

        cnn.Close
        Set cnn = Nothing
End Sub



' 显示学籍变更列表,学籍变更情况
' row - 记录编号
Private Sub FillChangeTypeAndDes(Row As Integer)

        Dim cmd As New Command
        
        If cnn.State <> adStateOpen Then
            cnn.Open strCnn
        End If
        cmd.ActiveConnection = cnn
        cmd.CommandType = adCmdStoredProc
        cmd.CommandText = "stu_ChangeTypeAndDes"
        
        cmd.Parameters.Append cmd.CreateParameter("@id", Int(4), adParamInput)
        cmd.Parameters.Append cmd.CreateParameter("@des", adVarChar, adParamOutput, 50)
        cmd.Parameters.Append cmd.CreateParameter("@memo", adVarChar, adParamOutput, 1000)
        cmd.Parameters("@id").Value = Row
        cmd.Execute
        
        dtcmbChange.Text = cmd("@des").Value
        txtChangeMemo.Text = cmd("@memo").Value
        
        cnn.Close
        Set cnn = Nothing
End Sub

' 显示奖励情况列表,奖励情况
' row - 记录编号
Private Sub FillRewardTypeAndDes(Row As Integer)

        Dim cmd As New Command
        
        If cnn.State <> adStateOpen Then
            cnn.Open strCnn
        End If
        cmd.ActiveConnection = cnn
        cmd.CommandType = adCmdStoredProc
        cmd.CommandText = "stu_RewardTypeAndDes"
        
        cmd.Parameters.Append cmd.CreateParameter("@id", Int(4), adParamInput)
        cmd.Parameters.Append cmd.CreateParameter("@des", adVarChar, adParamOutput, 50)
        cmd.Parameters.Append cmd.CreateParameter("@memo", adVarChar, adParamOutput, 1000)
        cmd.Parameters("@id").Value = Row
        cmd.Execute
        
        dtcmbReward.Text = cmd("@des").Value
        txtRewardMemo.Text = cmd("@memo").Value
        
        cnn.Close
        Set cnn = Nothing
End Sub

' 显示处罚情况列表,处罚情况
' row - 记录编号
Private Sub FillPunishTypeAndDes(Row As Integer)

        Dim cmd As New Command
        
        If cnn.State <> adStateOpen Then
            cnn.Open strCnn
        End If
        cmd.ActiveConnection = cnn
        cmd.CommandType = adCmdStoredProc
        cmd.CommandText = "stu_PunishTypeAndDes"
        
        cmd.Parameters.Append cmd.CreateParameter("@id", Int(4), adParamInput)
        cmd.Parameters.Append cmd.CreateParameter("@des", adVarChar, adParamOutput, 50)
        cmd.Parameters.Append cmd.CreateParameter("@memo", adVarChar, adParamOutput, 1000)
        cmd.Parameters("@id").Value = Row
        cmd.Execute
        
        dtcmbPunish.Text = cmd("@des").Value
        txtPunishMemo.Text = cmd("@memo").Value
        
        cnn.Close
        Set cnn = Nothing
End Sub

Private Sub cmdBasicCancel_Click()  '响应学生基本情况中的取消命令
        If Len(LTrim(txtName.Text)) <> 0 Then
                strStuNo = txtStuNo.Text
        Else
                
                Exit Sub
        End If

        ' 填充学生基本情况
        FillStuBasic strStuNo
End Sub

Private Sub cmdBasicEdit_Click()  '响应学生基本情况中的修改命令
        UpdateBasicStudent
End Sub



Private Sub cmdChangeCancel_Click() '响应学籍变更中的取消命令
        Dim recNo As Integer
        If MSHFlexGridChange.TextMatrix(MSHFlexGridChange.RowSel, 1) <> "" Then
            recNo = MSHFlexGridChange.TextMatrix(MSHFlexGridChange.RowSel, 1)
            FillChangeTypeAndDes recNo
        End If
End Sub

Private Sub cmdChangeEdit_Click() '响应学籍变更情况中的修改命令
        UpdateChange MSHFlexGridChange.TextMatrix(MSHFlexGridChange.RowSel, 1)
End Sub

Private Sub cmdPunishCancel_Click()  '响应处罚情况中的取消命令
        Dim recNo As Integer
        If MSHFlexGridPunish.TextMatrix(MSHFlexGridPunish.RowSel, 1) <> "" Then
            recNo = MSHFlexGridPunish.TextMatrix(MSHFlexGridPunish.RowSel, 1)
            FillPunishTypeAndDes recNo
        End If
End Sub

Private Sub cmdPunishEdit_Click() '响应处罚情况中的修改命令
        UpdatePunish MSHFlexGridPunish.TextMatrix(MSHFlexGridPunish.RowSel, 1)
End Sub

Private Sub cmdRewardCancel_Click() '响应奖励情况中的取消命令
        Dim recNo As Integer
        If MSHFlexGridReward.TextMatrix(MSHFlexGridReward.RowSel, 1) <> "" Then
            recNo = MSHFlexGridReward.TextMatrix(MSHFlexGridReward.RowSel, 1)
            FillRewardTypeAndDes recNo
        End If

End Sub

Private Sub cmdRewardEdit_Click() '响应奖励情况中的修改命令
        UpdateReward MSHFlexGridReward.TextMatrix(MSHFlexGridReward.RowSel, 1)
End Sub

Private Sub CmdSearch_Click()  '输入待查学号后，响应查询事件
        Search
        cmdBasicEdit.Enabled = True
        cmdBasicCancel.Enabled = True
End Sub

Private Sub Form_Load()
    cmdBasicEdit.Enabled = False
    cmdBasicCancel.Enabled = False
End Sub

Private Sub MSHFlexGridChange_RowColChange()  '在学籍变更列表中移动光标选择相应的记录时，在下方的"学籍变更情况"栏中显示相应的详细情况
        Dim recNo As Integer
        recNo = MSHFlexGridChange.TextMatrix(MSHFlexGridChange.RowSel, 1)
        FillChangeTypeAndDes recNo
End Sub

Private Sub MSHFlexGridReward_RowColChange()  '在奖励情况列表中移动光标选择相应的记录时，在下方的"奖励情况"栏中显示相应的详细情况
        Dim recNo As Integer
        recNo = MSHFlexGridReward.TextMatrix(MSHFlexGridReward.RowSel, 1)
        FillRewardTypeAndDes recNo
End Sub

Private Sub MSHFlexGridPunish_RowColChange()  '在处罚情况列表中移动光标选择相应的记录时，在下方的"处罚情况"栏中显示相应的详细情况
        Dim recNo As Integer
        recNo = MSHFlexGridPunish.TextMatrix(MSHFlexGridPunish.RowSel, 1)
        FillPunishTypeAndDes recNo
End Sub


Private Sub UpdateBasicStudent()  '修改学生基本情况子函数

    On Error GoTo ErrHandle
        
    Dim cmd As New ADODB.Command
    If cnn.State <> adStateOpen Then
        cnn.Open strCnn
    End If

    cmd.ActiveConnection = cnn
    cmd.CommandType = adCmdStoredProc       ' 将命令类型设置为存储过程
    cmd.CommandText = "stu_UpdateAllStudent1"    ' 要执行的存储过程
        
        ' 以下添加存储过程stu_UpdateAllStudent1所需的参数
    cmd.Parameters.Append cmd.CreateParameter("@stuNo", adChar, adParamInput, 6)
    cmd.Parameters.Append cmd.CreateParameter("@name", adVarChar, adParamInput, 20)
    cmd.Parameters.Append cmd.CreateParameter("@sex", adChar, adParamInput, 2)
    cmd.Parameters.Append cmd.CreateParameter("@departName", adVarChar, adParamInput, 50)
    cmd.Parameters.Append cmd.CreateParameter("@className", adVarChar, adParamInput, 50)
    cmd.Parameters.Append cmd.CreateParameter("@birthday", adDBDate)
    cmd.Parameters.Append cmd.CreateParameter("@native", adVarChar, adParamInput, 50)
        
            ' 输入值
    cmd.Parameters("@stuNo").Value = txtStuNo.Text
    cmd.Parameters("@name").Value = txtName.Text
    cmd.Parameters("@sex").Value = cmbSex.Text
    cmd.Parameters("@departName").Value = dtcmbDpeart.Text
    cmd.Parameters("@className").Value = dtcmbClass.Text
    cmd.Parameters("@birthday").Value = txtBirth.Text
    cmd.Parameters("@native").Value = txtNative.Text

    cmd.Execute ' 执行存储过程
        MsgBox "修改成功"
        cnn.Close
        Set cnn = Nothing

        Exit Sub
ErrHandle:
        MsgBox "修改出错" + Err.Description ' 出错处理
End Sub


Private Sub UpdateChange(Row As Integer)  '修改学生学籍变更情况子函数

    On Error GoTo ErrHandle
        
    Dim cmd As New ADODB.Command
    If cnn.State <> adStateOpen Then
        cnn.Open strCnn
    End If

    cmd.ActiveConnection = cnn
    cmd.CommandType = adCmdStoredProc       ' 将命令类型设置为存储过程
    cmd.CommandText = "stu_UpdateChange"    ' 要执行的存储过程
        
        ' 以下添加存储过程stu_UpdateChange所需的参数
    cmd.Parameters.Append cmd.CreateParameter("@id", Int(4), adParamInput)
    cmd.Parameters.Append cmd.CreateParameter("@stuNo", adChar, adParamInput, 6)
    cmd.Parameters.Append cmd.CreateParameter("@des", adVarChar, adParamInput, 50)
    cmd.Parameters.Append cmd.CreateParameter("@memo", adVarChar, adParamInput, 1000)
        
            ' 输入值
    cmd.Parameters("@id").Value = Row
    cmd.Parameters("@stuNo").Value = txtStuNo.Text
    cmd.Parameters("@des").Value = dtcmbChange.Text
    cmd.Parameters("@memo").Value = txtChangeMemo.Text

    cmd.Execute ' 执行存储过程
        MsgBox "修改成功"
        cnn.Close
        Set cnn = Nothing

        Exit Sub
ErrHandle:
        MsgBox "修改出错" + Err.Description ' 出错处理
End Sub



Private Sub UpdateReward(Row As Integer)  '修改学生奖励情况子函数

    On Error GoTo ErrHandle
        
    Dim cmd As New ADODB.Command
    If cnn.State <> adStateOpen Then
        cnn.Open strCnn
    End If

    cmd.ActiveConnection = cnn
    cmd.CommandType = adCmdStoredProc       ' 将命令类型设置为存储过程
    cmd.CommandText = "stu_UpdateReward"    ' 要执行的存储过程
        
        ' 以下添加存储过程stu_UpdateReward 所需的参数
    cmd.Parameters.Append cmd.CreateParameter("@id", Int(4), adParamInput)
    cmd.Parameters.Append cmd.CreateParameter("@stuNo", adChar, adParamInput, 6)
    cmd.Parameters.Append cmd.CreateParameter("@des", adVarChar, adParamInput, 50)
    cmd.Parameters.Append cmd.CreateParameter("@memo", adVarChar, adParamInput, 1000)
        
            ' 输入值
    cmd.Parameters("@id").Value = Row
    cmd.Parameters("@stuNo").Value = txtStuNo.Text
    cmd.Parameters("@des").Value = dtcmbReward.Text
    cmd.Parameters("@memo").Value = txtRewardMemo.Text

    cmd.Execute ' 执行存储过程
        MsgBox "修改成功"
        cnn.Close
        Set cnn = Nothing

        Exit Sub
ErrHandle:
        MsgBox "修改出错" + Err.Description ' 出错处理
End Sub

Private Sub UpdatePunish(Row As Integer)  '修改学生奖励情况子函数

    On Error GoTo ErrHandle
        
    Dim cmd As New ADODB.Command
    If cnn.State <> adStateOpen Then
        cnn.Open strCnn
    End If

    cmd.ActiveConnection = cnn
    cmd.CommandType = adCmdStoredProc       ' 将命令类型设置为存储过程
    cmd.CommandText = "stu_UpdatePunish"    ' 要执行的存储过程
        
        ' 以下添加存储过程stu_UpdatePunish 所需的参数
    cmd.Parameters.Append cmd.CreateParameter("@id", Int(4), adParamInput)
    cmd.Parameters.Append cmd.CreateParameter("@stuNo", adChar, adParamInput, 6)
    cmd.Parameters.Append cmd.CreateParameter("@des", adVarChar, adParamInput, 50)
    cmd.Parameters.Append cmd.CreateParameter("@memo", adVarChar, adParamInput, 1000)
        
            ' 输入值
    cmd.Parameters("@id").Value = Row
    cmd.Parameters("@stuNo").Value = txtStuNo.Text
    cmd.Parameters("@des").Value = dtcmbPunish.Text
    cmd.Parameters("@memo").Value = txtPunishMemo.Text

    cmd.Execute ' 执行存储过程
        MsgBox "修改成功"
        cnn.Close
        Set cnn = Nothing

        Exit Sub
ErrHandle:
        MsgBox "修改出错" + Err.Description ' 出错处理
End Sub