用ADO的NextRecordset属性取多结果集:
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset Cmd.ActiveConnection = "DSN=MySamples;UID=sa"
Cmd.CommandText = "MyNextProc"
Cmd.CommandType = adCmdStoredProcSet rs = Cmd.Execute()
While Not rs Is Nothing
If (Not rs.EOF) Then
Debug.Print rs(0)
End If
Set rs = rs.NextRecordset()
Wend
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset Cmd.ActiveConnection = "DSN=MySamples;UID=sa"
Cmd.CommandText = "MyNextProc"
Cmd.CommandType = adCmdStoredProcSet rs = Cmd.Execute()
While Not rs Is Nothing
If (Not rs.EOF) Then
Debug.Print rs(0)
End If
Set rs = rs.NextRecordset()
Wend
Select a from t1
select b,c,....z from t2
可以考虑用如下方法返回:
Select a,0,........0
from t1
union
select 0,b,.........z
from t2
Select a,0,........0
from t1
union
select 0,b,.........z
from t2
create proc p_test
as
select * from 表1
select * from 表2
select * from 表3
select * from 表4
go
set nocount on
Dim rs As ADODB.Recordset Cmd.ActiveConnection = "DSN=MySamples;UID=sa"
Cmd.CommandText = "MyNextProc"
Cmd.CommandType = adCmdStoredProcSet rs = Cmd.Execute()
While Not rs Is Nothing
If (Not rs.EOF) Then
Debug.Print rs(0)
End If
Set rs = rs.NextRecordset()
Wend
-----------------------
----------------------------
哈哈,升级如下:
Dim myCn as new System.data.SqlClient.Sqlconnection("server=.;database=myDB;integrated Security=SSPI;)Dim strSql as String="Select * from Employees;" _
& "select * from Departments;" _
& "select * from .....;"
Dim drReader as sqlDataReader
dim cmd as new SqlCommand(strSql,myCn)
Try
'open the connection
myCn.open()
'open data Reader
drReader=cmd.executeReader()
'loop through the first set of results
Do While drReader.Read
'write the Suname to the console
Console.WriteLine(drReader("..."))'Employees表的列
Loop drReader.NextResult() 'loop through the first set of results
Do While drReader.Read
'write the Suname to the console
Console.WriteLine(drReader("..."))' Departments表的列
Loop
..........
catch
'error handing code goes herefinally
drReader.close()
if not myCn isnothing then
myCn.Disponse()
End if
end try