end GO
创建插入学生成绩的AddStuScore存储过程，学生此门课程成绩记录如存在则修改成绩，如不存在则添加这条记录
CREATE PROCEDURE [AddStuScore] (@XH varchar(6), @KCM varchar(16),@CJ int,@XF int) AS begin
declare @KCH varchar(3)
select @KCH=KCH from KC where KCM=@KCM select * from XS_KC where XH=@XH and KCH=@KCH if @@RowCount =1
update XS_KC set CJ=@CJ,XF=@XF where XH=@XH and KCH=@KCH
else insert into XS_KC values(@XH,@KCH,@CJ,@XF) end
GO
创建XS_KC_CJ视图，用来在DataGrid中显示学生、课程、成绩的信息
CREATE VIEW [dbo].[XS_KC_CJ]
AS
SELECT dbo.XS_KC.XH, dbo.KC.KCM, dbo.XS_KC.CJ,
dbo.XS_KC.XF, dbo.XS.XM, dbo.XS.ZY
FROM dbo.KC INNER JOIN GO
创建XS_KC的INSERT,UPDATE触发器，当添加和修改成绩后，根据当前的成绩自动为该学生该课程增加学分，当成绩小于60分时，学分为0；当大于等于60分时，从该门课程表中取出当前课程的学分来更新当前学生在XS_KC表中这门课程的学分
CREATE TRIGGER CHECKXF ON XS_KC INSERT,UPDATE AS
BEGIN SET NOCOUNT ON;
update XS_KC set XF = 0 where CJ < 60
update XS_KC set XF = (select XF from KC where KCH = XS_KC.KCH) where CJ >= 60 END GO
dbo.XS_KC ON dbo.KC.KCH = dbo.XS_KC.KCH INNER JOIN dbo.XS ON dbo.XS_KC.XH = dbo.XS.XH
(2) VB中的主要代码。
General中定义的全局变量
Private SqlCon As New ADODB.Connection Private SqlRes As ADODB.Recordset
Private SqlCmd As New ADODB.Command
Form加载时打开数据库连接，并且通过执行select语句从数据库中提取课程名添加到课程下拉列表中去，执行GetStuZy存储过程，把返回的专业添加到专业下拉列表中去，在DataGrid中显示当前专业，当前课程的的学生、成绩信息，方便修改
Private Sub Form_Load()
SqlCon.Provider = \ SqlCon.Open
\ SqlCmd.ActiveConnection = SqlCon '添加课程
SqlCmd.CommandText = \ SqlCmd.CommandType = adCmdText Set SqlRes = SqlCmd.Execute StuKCM.Text = SqlRes(\ While Not SqlRes.EOF
StuKCM.AddItem (Trim(SqlRes(\ SqlRes.MoveNext Wend '添加专业
SqlCmd.CommandText = \ SqlCmd.CommandType = adCmdStoredProc Set SqlRes = SqlCmd.Execute StuZY.Text = SqlRes(\ While Not SqlRes.EOF StuZY.AddItem (SqlRes(\ SqlRes.MoveNext Wend StuZY_Click StuXH_Click
StuADO.RecordSource = \,XF
from XS_KC_CJ where KCM = '\ and ZY = '\.Text) + \ StuADO.Refresh End Sub
Form卸载时关闭数据库连接
Private Sub Form_Unload(Cancel As Integer)
SqlRes.Close
SqlCon.Close End Sub
StuADO移动记录完成的时候，在学号、姓名、成绩、学分等显示控件中显示当前学生的当前课程的成绩信息
Private Sub StuADO_MoveComplete(ByVal adReason As
ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset) If Not pRecordset.EOF And Not pRecordset.BOF Then StuXH.Text = pRecordset(\ StuXM.Text = pRecordset(\ StuCJ.Text = pRecordset(\
StuXF.Text = CStr(pRecordset(\ End If
End Sub
“删除”Click事件处理，调用Command执行delete删除当前学号，当前课程名的成绩记录
Private Sub StuDel_Click()
Ret = MsgBox(\是否要删除\号学生的\
Trim(StuKCM.Text) + \课的成绩记录！\esNo, \提示\ If Ret = vbYes Then
SqlCmd.ActiveConnection = SqlCon
SqlCmd.CommandText = \
where XH='\
KCH in (select KCH from KC where KCM='\
SqlCmd.CommandType = adCmdText
SqlCmd.Execute
StuADO.RecordSource = \,XF
from XS_KC_CJ where KCM = '\
and ZY = '\.Text) + \ StuADO.Refresh
End If End Sub
“课程” 下拉列表Click事件处理，从视图中查询当前专业、当前课程的学生成绩信息，同时调用ShowScore在姓名、成绩等Edit中来显示当前学生的姓名、成绩等信息
Private Sub StuKCM_Click() Dim StXH, StKCM
'选者当前课程的学生选课信息
StuADO.RecordSource = \,XF
from XS_KC_CJ where KCM = '\
and ZY = '\.Text) + \ StuADO.Refresh
'没有学生选者该门课程
If StuADO.Recordset.RecordCount = 0 Then 'if no record then exit sub
StuXM.Text = \ StuCJ.Text = \ StuXF.Text = \ Exit Sub End If
SqlCmd.CommandText = \
SqlCmd.CommandType = adCmdStoredProc
Set StXH = SqlCmd.CreateParameter(\arChar, adParamInput, 6) SqlCmd.Parameters.Append (StXH)
Set StKCM =
SqlCmd.CreateParameter(\ SqlCmd.Parameters.Append (StKCM) SqlCmd(\ SqlCmd(\
Set SqlRes = SqlCmd.Execute StuXH.Text = SqlRes(\
If Not SqlRes.EOF Then
If SqlRes.Fields.Count = 4 Then
StuXM.Text = SqlRes(\ StuCJ.Text = SqlRes(\
StuXF.Text = CStr(SqlRes(\ End If
End If
SqlCmd.Parameters.Delete (\
SqlCmd.Parameters.Delete (\ End Sub
“更新” Click事件处理,通过调用AddStuScore，来添加当前课程，当前学生的成绩信息
Private Sub StuUpd_Click()
Dim StXH, StKCM, StCJ, StXF
If Trim(StuCJ.Text) = \
MsgBox \输入完整的信息！\提示\ Exit Sub End If
SqlCmd.CommandText = \ SqlCmd.CommandType = adCmdStoredProc '创建参数对象
Set StXH = SqlCmd.CreateParameter(\arChar, adParamInput, 6) SqlCmd.Parameters.Append (StXH)
Set StKCM =
SqlCmd.CreateParameter(\arChar, adParamInput, 16)
SqlCmd.Parameters.Append (StKCM)
相关推荐：
loading