调用过程:
Set cnn1 = New ADODB.Connection '生成一个连接
strCnn = "Description=test;DRIVER=SQL Server;SERVER=wts;APP=Microsoft Open Database Connectivity;WSID=BILLGATES;DATABASE=fire_info;QueryLog_On=Yes;QueryLogFile=C:\...\fire_info\QUERY.LOG;StatsLog_On=Yes;StatsLogFile=C:\...\fire_info\STATS.LOG;Trusted_Connection=Yes"
cnn1.Open strCnn '打开连接
Set mycommand = New ADODB.Command
mycommand.ActiveConnection = cnn1 '指定该command 的当前活动连接
mycommand.CommandText = " dt_info_search" '调用的存储过程dt_info_search
mycommand.CommandType = adCmdStoredProc '表明command 为存储过程
Set rstByQuery = New ADODB.Recordset
Set rstByQuery = mycommand.Execute() '执行存储过程,并得到结果集
存储过程的创建:
use fire_info 'fire_info为数据库名,'dt_info_search' 为存储过程名。
if exists(select name from sysobjects where name ='dt_info_search' and xtype='p')
drop PROCEDURE dt_info_search
go
CREATE PROCEDURE dt_info_search
as
select *
from dt_info
GO
Private Sub Command1_Click()
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim param As ADODB.Parameter
Set cmd = New ADODB.Command
cmd.ActiveConnection = mConn
cmd.CommandText = "insert_users"
cmd.CommandType = adCmdStoredProc
Set param = cmd.CreateParameter("truename", adChar, adParamInput, 20, Trim(txttruename.Text))
cmd.Parameters.Append param
Set param = cmd.CreateParameter("regname", adChar, adParamInput, 20, Trim(txtregname.Text))
cmd.Parameters.Append param
Set param = cmd.CreateParameter("pwd", adChar, adParamInput, 20, Trim(txtpwd.Text))
cmd.Parameters.Append param
Set param = cmd.CreateParameter("sex", adChar, adParamInput, 20, Trim(txtsex.Text))
cmd.Parameters.Append param
Set param = cmd.CreateParameter("email", adChar, adParamInput, 20, Trim(txtemail.Text))
cmd.Parameters.Append param
Set rs = cmd.Execute
Command2_Click
End Sub
Private Sub Command2_Click()
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim param As ADODB.Parameter
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
cmd.ActiveConnection = mConn
cmd.CommandText = "select_users"
cmd.CommandType = adCmdStoredProc
mConn.CursorLocation = adUseClient '设置为客户端
Set rs = cmd.Execute()
MsgBox rs.RecordCount
Set DataGrid1.DataSource = rs
DataGrid1.Refresh
End Sub
Private Sub Command3_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "select table1.eid,table1.etype,table1.econtent,table1.edate from table1,(select eid,etype,econtent from table1 where econtent='sdfdsf@dfs.com' ) a,(select eid,etype,econtent from table1 where econtent='kjk@sdf.com') b where table1.eid=a.eid and table1.eid=b.eid and table1.edate>='" & Text1.Text & "' and table1.edate<='" & Text2.Text & "'", mConn, 1, 3
MsgBox rs.RecordCount
'rs.Fields ("")
End Sub
Private Sub Form_Load()
'open the connection
Set mConn = New Connection
mConn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Test;Data Source=yang"
mConn.Open
Dim rs As New ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "users", mConn, adOpenStatic, adLockPessimistic
Public Function GetRst(STR As String) As Recordset '调用存储过程,返回记录集
On Error GoTo Ferr
Dim Cndb As ADODB.Connection
Dim RST As ADODB.Recordset
Set Cndb = New ADODB.Connection
Set RST = New ADODB.Recordset
Cndb.Open CnString
RST.Open STR, Cndb
Set GetRst = RST
'Set RST = Nothing
'Set Cndb = Nothing
Exit Function
Ferr:
If Err.Number = 3146 Then
Set RST = Nothing
Set Cndb = Nothing
MsgBox "操作错误", vbInformation, "虎都服饰"
End If