用记录集阿 比如: Dim rsTemp as new adodb.recordset rsTemp.open "select * from user_info",cn,1,4 查询出来的结果保存在记录集rsTemp中
public function GetRst () as adodb.recordset dim rst as adodb.recordset,sql as string
sql="" set rst=mCnn.excute(sql) set GetRst=rst end function 外部获取 dim a as adodb.recordseta=getrst
'用ado怎样实现 '工程--->引用--->Microsoft ActiveX Data Object 2.x(版本号) '---------------------------------------------------------------------------- Private Sub Form_Load() Dim adoCN As New ADODB.Connection '定义数据库的连接 Dim adoRS As New ADODB.Recordset '连接Access数据库 adoCN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test.MDB;Persist Security Info=False" '连接Sqlserver数据库 adoCN.Open "Provider=SQLOLEDB;Data Source=servername;User Id=sa;PassWord=****;Initial Catalog=test" adoRS.Open "select field1,field2 from table1", adoCN, adOpenKeyset, adLockOptimistic, adCmdText Do While Not adoRS.EOF Debug.Print adoRS!field1 adoRS.MoveNext Loop adoRS.Close
看到你给我留的短消息了。 给你一个我在工作中实际使用的过程吧'函数:function :AllrstGetDataEX '功能:根据一个传入参数(存储过程)取得相应的记录集 '输入:参数StrSQL,colprm '参数:StrSQL调用的存储过程 '参数:colprm储存过程调用的参数 '返回:存储过程里定义的记录集 '修改日志: '日期 修改原因 修改人 '------------------------------------------------------- '2001/10/30 方宏 '******************************************** Public Function AllrstGetDataEX(ByVal strSql As String, ByVal colprm As Collection, ByRef retval As Long, ByRef retmsg As String) As ADODB.Recordset 'On Error Resume Next On Error GoTo ErrHandle Dim cmd As New ADODB.Command Dim rst As ADODB.Recordset Dim strTmp As String Dim lngx As Long Dim lngy As Long Set cmd.ActiveConnection = CnXYXTGL '这是一个已经建立的连接 cmd.CommandTimeout = 18000 cmd.CommandText = strSql cmd(0).Direction = adParamReturnValue lngy = colprm.Count For lngx = 1 To lngy If lngx = lngy Then If colprm.Item(lngy) = "****" Then cmd(lngx).Direction = adParamOutput Else cmd(lngx).value = colprm.Item(lngx) End If Else cmd(lngx).value = colprm.Item(lngx) End If Next Dim i As Integer ' For i = 0 To 4 'Debug.Print cmd(i) ' Next Set rst = cmd.Execute() retval = cmd(0) '在这里取到存储过程返回值 If cmd.Parameters.Count = 6 Then retmsg = cmd(5) Else If retval = 0 Then retmsg = "执行成功" Else retmsg = "执行出错" End If End If Set AllrstGetDataEX = rst Set rst = Nothing Exit Function ErrHandle: 'ErrMessage Err.Number, Err.Description, "AllrstGetData(" & StrSql & ")" retval = Err.Number 'cmd(0) retmsg = Err.Description End Function
上面那个函数的调用举例: Dim retval As Long Dim retmsg as string Dim rst as ADODB.Recordset Dim colprm As New Collection '建立存储过程参数集合 Dim strSql As String colprm.Add mstrJGDM '输入4个存储过程需要的参数参数 colprm.Add mstrUserID colprm.Add db colprm.Add Sql strSql = "{?=call P_GY_WH_RPC(?,?,?,?)}" '将要调用的存储过程 Set rst = AllrstGetDataEX(strSql, colprm, retval, retmsg) 'rst:存储过程返回的记录集 'retval:存储过程返回值 'retmsg:错误信息 Exit Sub
如果在程序中,直接用记录集打开就可以啦,如果在数据库中的存储过程里,可以在VB的数据环境里添加这个存储过程,设定返回纪录。也可以直接用命令执行:set recordset1=connection.execut("exec " & 存储过程名称)
比如:
Dim rsTemp as new adodb.recordset
rsTemp.open "select * from user_info",cn,1,4
查询出来的结果保存在记录集rsTemp中
public function GetRst () as adodb.recordset
dim rst as adodb.recordset,sql as string
sql=""
set rst=mCnn.excute(sql)
set GetRst=rst
end function
外部获取
dim a as adodb.recordseta=getrst
'工程--->引用--->Microsoft ActiveX Data Object 2.x(版本号)
'----------------------------------------------------------------------------
Private Sub Form_Load()
Dim adoCN As New ADODB.Connection '定义数据库的连接
Dim adoRS As New ADODB.Recordset
'连接Access数据库
adoCN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test.MDB;Persist Security Info=False"
'连接Sqlserver数据库
adoCN.Open "Provider=SQLOLEDB;Data Source=servername;User Id=sa;PassWord=****;Initial Catalog=test"
adoRS.Open "select field1,field2 from table1", adoCN, adOpenKeyset, adLockOptimistic, adCmdText
Do While Not adoRS.EOF
Debug.Print adoRS!field1
adoRS.MoveNext
Loop
adoRS.Close
给你一个我在工作中实际使用的过程吧'函数:function :AllrstGetDataEX
'功能:根据一个传入参数(存储过程)取得相应的记录集
'输入:参数StrSQL,colprm
'参数:StrSQL调用的存储过程
'参数:colprm储存过程调用的参数
'返回:存储过程里定义的记录集
'修改日志:
'日期 修改原因 修改人
'-------------------------------------------------------
'2001/10/30 方宏
'********************************************
Public Function AllrstGetDataEX(ByVal strSql As String, ByVal colprm As Collection, ByRef retval As Long, ByRef retmsg As String) As ADODB.Recordset
'On Error Resume Next
On Error GoTo ErrHandle
Dim cmd As New ADODB.Command
Dim rst As ADODB.Recordset
Dim strTmp As String
Dim lngx As Long
Dim lngy As Long
Set cmd.ActiveConnection = CnXYXTGL '这是一个已经建立的连接
cmd.CommandTimeout = 18000
cmd.CommandText = strSql
cmd(0).Direction = adParamReturnValue
lngy = colprm.Count
For lngx = 1 To lngy
If lngx = lngy Then
If colprm.Item(lngy) = "****" Then
cmd(lngx).Direction = adParamOutput
Else
cmd(lngx).value = colprm.Item(lngx)
End If
Else
cmd(lngx).value = colprm.Item(lngx)
End If
Next
Dim i As Integer
' For i = 0 To 4
'Debug.Print cmd(i)
' Next
Set rst = cmd.Execute()
retval = cmd(0) '在这里取到存储过程返回值
If cmd.Parameters.Count = 6 Then
retmsg = cmd(5)
Else
If retval = 0 Then
retmsg = "执行成功"
Else
retmsg = "执行出错"
End If
End If
Set AllrstGetDataEX = rst
Set rst = Nothing
Exit Function
ErrHandle:
'ErrMessage Err.Number, Err.Description, "AllrstGetData(" & StrSql & ")"
retval = Err.Number 'cmd(0) retmsg = Err.Description
End Function
Dim retval As Long
Dim retmsg as string
Dim rst as ADODB.Recordset
Dim colprm As New Collection '建立存储过程参数集合
Dim strSql As String
colprm.Add mstrJGDM '输入4个存储过程需要的参数参数
colprm.Add mstrUserID
colprm.Add db
colprm.Add Sql
strSql = "{?=call P_GY_WH_RPC(?,?,?,?)}" '将要调用的存储过程
Set rst = AllrstGetDataEX(strSql, colprm, retval, retmsg)
'rst:存储过程返回的记录集
'retval:存储过程返回值
'retmsg:错误信息 Exit Sub