Private Sub Command1_Click() '无参数的存储过程. '存储过程www的建立 'CREATE PROCEDURE www AS select * from cwqkDim con As ADODB.Connection Set con = New ADODB.Connection Dim cmd As ADODB.Command Set cmd = New ADODB.Command Dim rs As ADODB.Recordset Set rs = New ADODB.Recordsetcon.CursorLocation = adUseClient '设置连接为客户端con.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=zjsu;Data Source=WEBGROUP1" cmd.ActiveConnection = con '设置Command对象的连接为con cmd.CommandText = "www" '设置存储过程名称. cmd.CommandType = adCmdStoredProc '设置要执行的命令为存储过程. Set rs = cmd.Execute '设置记录集名称为执行Command对象的结果.If rs.RecordCount > 0 Then For i = i To rs.RecordCount MsgBox rs(5)
rs.MoveNext Next i End If'rs.Close con.Close Set rs = Nothing Set con = Nothing Set cmd = NothingEnd SubPrivate Sub Command2_Click() If Text1.Text <> "" And Text2.Text <> "" And Text3.Text <> "" Then '存储过程PJConversion的建立 'CREATE Procedure PJConversion @User_ID Int, @PJDateY Int ,@PJDateM Int As 'Select * From PJ_Official 'Where [user_id] =@User_ID and Datepart(YYYY,RQ)=@PJDateY and Datepart(MM,RQ) =@PJDateM
'有参数的存储过程. Dim con As ADODB.Connection Set con = New ADODB.Connection
Dim cmd As ADODB.Command Set cmd = New ADODB.Command
Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset
Set rs = cmd.Execute MsgBox rs.RecordCount If rs.RecordCount > 0 Then For i = 0 To rs.RecordCount MsgBox rs(3) rs.MoveNext Next i Else MsgBox "没有你要查找的记录,请重新输入要查找的范围.", vbOKOnly + 64, "提示信息" Text1.Text = "" Text2.Text = "" Text3.Text = "" Exit Sub End If
Set rs = Nothing Set cmd = Nothing Set con = Nothing Else MsgBox "必须输入全部查询条件.", vbCritical, "提示信息" Exit Sub End IfEnd Sub
With iCmd
.ActiveConnection = iConc '数据库连接字符串
.CommandType = adCmdStoredProc
.CommandText = "存储过程名"
.Parameters.Refresh '创建存储过程参数
.Parameters("@输入参数1") = "值1" '为参数赋值
.Parameters("@输入参数2") = "值2"
.Execute '执行存储过程
Debug.Print .Parameters("@输出参数1") '显示存储过程的执行结果
Debug.Print .Parameters("@输出参数2")
End With
'无参数的存储过程.
'存储过程www的建立
'CREATE PROCEDURE www AS select * from cwqkDim con As ADODB.Connection
Set con = New ADODB.Connection
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordsetcon.CursorLocation = adUseClient '设置连接为客户端con.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=zjsu;Data Source=WEBGROUP1"
cmd.ActiveConnection = con '设置Command对象的连接为con
cmd.CommandText = "www" '设置存储过程名称.
cmd.CommandType = adCmdStoredProc '设置要执行的命令为存储过程.
Set rs = cmd.Execute '设置记录集名称为执行Command对象的结果.If rs.RecordCount > 0 Then
For i = i To rs.RecordCount
MsgBox rs(5)
rs.MoveNext
Next i
End If'rs.Close
con.Close
Set rs = Nothing
Set con = Nothing
Set cmd = NothingEnd SubPrivate Sub Command2_Click()
If Text1.Text <> "" And Text2.Text <> "" And Text3.Text <> "" Then '存储过程PJConversion的建立
'CREATE Procedure PJConversion @User_ID Int, @PJDateY Int ,@PJDateM Int As
'Select * From PJ_Official
'Where [user_id] =@User_ID and Datepart(YYYY,RQ)=@PJDateY and Datepart(MM,RQ) =@PJDateM
'有参数的存储过程.
Dim con As ADODB.Connection
Set con = New ADODB.Connection
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
con.CursorLocation = adUseClient
con.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=zjsu;Data Source=WEBGROUP1"
cmd.ActiveConnection = con
cmd.CommandText = "PJConversion"
cmd.CommandType = adCmdStoredProc
cmd.Parameters("@User_ID") = Text1.Text '给参数赋值.
cmd.Parameters("@PJDateY") = Text2.Text
cmd.Parameters("@PJDateM") = Text3.Text
Set rs = cmd.Execute
MsgBox rs.RecordCount
If rs.RecordCount > 0 Then
For i = 0 To rs.RecordCount
MsgBox rs(3)
rs.MoveNext
Next i
Else
MsgBox "没有你要查找的记录,请重新输入要查找的范围.", vbOKOnly + 64, "提示信息"
Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Exit Sub
End If
Set rs = Nothing
Set cmd = Nothing
Set con = Nothing
Else
MsgBox "必须输入全部查询条件.", vbCritical, "提示信息"
Exit Sub
End IfEnd Sub