求VB调用SQL server 2000存贮过特程的方法

[liangzhunyu]

还有如何建立它索引来提高速度。请尽量说得详细点。 QQ4872423 [e-mail]

[davytao1018]

Public Function exec_copyRes(ByVal dogNo As String, ByVal sResID As String, ByVal sSourceDBID As String, _
ByVal iSourceNodeID As Integer, ByVal sTargetDBID As String, ByVal iTargetNodeID As Integer) _
As String

Dim i As Integer
Dim j As String
Dim strCnn As String '连接字符串
Dim cnn2 As New ADODB.Connection
Dim mycommand As ADODB.Command '命令
Dim rstByQuery As ADODB.Recordset '结果集
Dim flagError As String

On Error GoTo dealwitherror:

'---------------
flagError = "连接数据库"
strCnn = strConn.strGetConn()

cnn2.Open strCnn
cnn2.CursorLocation = adUseClient '设置为客户端

'-----------------
flagError = "设置存储过程参数"

Set mycommand = New ADODB.Command
Set param = New ADODB.Parameter

Set param = mycommand.CreateParameter("name1", adVarChar, adParamInput, 5, Trim(dogNo))
mycommand.Parameters.Append param

Set param = mycommand.CreateParameter("name2", adVarChar, adParamInput, 16, Trim(sResID))
mycommand.Parameters.Append param

Set param = mycommand.CreateParameter("name3", adVarChar, adParamInput, 3, Trim(sSourceDBID))
mycommand.Parameters.Append param

Set param = mycommand.CreateParameter("name4", adInteger, adParamInput, 4, iSourceNodeID)
mycommand.Parameters.Append param

Set param = mycommand.CreateParameter("name5", adVarChar, adParamInput, 3, Trim(sTargetDBID))
mycommand.Parameters.Append param

Set param = mycommand.CreateParameter("name6", adInteger, adParamInput, 4, iTargetNodeID)
mycommand.Parameters.Append param

Set param = mycommand.CreateParameter("name7", adSmallInt, adParamOutput, 2, iErrNo)
mycommand.Parameters.Append param

Set param = mycommand.CreateParameter("name8", adVarChar, adParamOutput, 200, Trim(sRetMsg))
mycommand.Parameters.Append param

mycommand.ActiveConnection = cnn2 '指定该command 的当前活动连接

mycommand.CommandType = adCmdStoredProc '表明command 为存储过程

mycommand.CommandText = " sp_CopyResToOtherDB " 'myprocedure 是你要调用的存储过程名称

'-------------------------------
flagError = "执行存储过程"

mycommand.Prepared = True

Set rstByQuery = mycommand.Execute()

i = mycommand.Parameters("name7").Value

j = mycommand.Parameters("name8").Value

exec_copyRes = i & j

'-----------------------------------------------------
cnn2.Close
Set cnn2 = Nothing
Set mycommand = Nothing
Set rstByQuery = Nothing
exec_copyRes = i & j

Exit Function

dealwitherror:
exec_copyRes = flagError & "出错，操作失败"

End Function

Public Function strGetConn() As String
strGetConn = "DSN=test;uid=sa;pwd="
End Function