如果储存过程有如下的创建临时表的语句vb将得不到返回的值
-- 封装在事务中
begin transaction
-- 创建临时表
select 姓名 into #姓名列表 from 姓名表 where 1=2
...
vb的参照程序是:
Dim i As Integer
Dim j As Integer
Set parm_jobid = New ADODB.Parameter
Set mycommand = New ADODB.Command
parm_jobid.Name = "name1"parm_jobid.Type = adVarChar '参数类型
parm_jobid.Size = 1 '参数长度
parm_jobid.Direction = adParamInput '参数方向,输入或输出
parm_jobid.Value = "G" '参数的值
mycommand.Parameters.Append parm_jobid '加入参数
Set parm_joblvl = New ADODB.Parameter
parm_joblvl.Name = "name2"
parm_joblvl.Type = adInteger
parm_joblvl.Size = 1
parm_joblvl.Direction = adParamInput
parm_joblvl.Value = 1
mycommand.Parameters.Append parm_joblvl
mycommand.ActiveConnection = cnn1
'指定该command 的当前活动连接
mycommand.CommandText = "test2"
'myprocedure 是你要调用的存储过程名称
mycommand.CommandType = adCmdStoredProc
'表明command 为存储过程
Set rstByQuery = New ADODB.Recordset
Set rstByQuery = mycommand.Execute()‘运行到这里就显示连接已经关闭了!i = 0
Do While Not rstByQuery.EOF
i = i + 1 ' i 中保存记录个数
rstByQuery.MoveNext
LoopMSFlexGrid1.Rows = i + 1
'动态设置MSFlexGrid的行和列
MSFlexGrid1.Cols = rstByQuery.Fields.Count + 1
MSFlexGrid1.Row = 0
For i = 0 To rstByQuery.Fields.Count - 1
MSFlexGrid1.Col = i + 1
MSFlexGrid1.Text = rstByQuery.Fields.Item(i).Name
Next '设置第一行的标题,用域名填充i = 0
Set rstByQuery = mycommand.Execute()
Do While Not rstByQuery.EOF
i = i + 1
MSFlexGrid1.Row = i '确定行
For j = 0 To rstByQuery.Fields.Count - 1
MSFlexGrid1.Col = j + 1
MSFlexGrid1.Text = rstByQuery(j)
Debug.Print rstByQuery(j)
'添充所有的列
Next
rstByQuery.MoveNextLoop '这个循环用来填充MSFlexGrid的内容
-- 封装在事务中
begin transaction
-- 创建临时表
select 姓名 into #姓名列表 from 姓名表 where 1=2
...
vb的参照程序是:
Dim i As Integer
Dim j As Integer
Set parm_jobid = New ADODB.Parameter
Set mycommand = New ADODB.Command
parm_jobid.Name = "name1"parm_jobid.Type = adVarChar '参数类型
parm_jobid.Size = 1 '参数长度
parm_jobid.Direction = adParamInput '参数方向,输入或输出
parm_jobid.Value = "G" '参数的值
mycommand.Parameters.Append parm_jobid '加入参数
Set parm_joblvl = New ADODB.Parameter
parm_joblvl.Name = "name2"
parm_joblvl.Type = adInteger
parm_joblvl.Size = 1
parm_joblvl.Direction = adParamInput
parm_joblvl.Value = 1
mycommand.Parameters.Append parm_joblvl
mycommand.ActiveConnection = cnn1
'指定该command 的当前活动连接
mycommand.CommandText = "test2"
'myprocedure 是你要调用的存储过程名称
mycommand.CommandType = adCmdStoredProc
'表明command 为存储过程
Set rstByQuery = New ADODB.Recordset
Set rstByQuery = mycommand.Execute()‘运行到这里就显示连接已经关闭了!i = 0
Do While Not rstByQuery.EOF
i = i + 1 ' i 中保存记录个数
rstByQuery.MoveNext
LoopMSFlexGrid1.Rows = i + 1
'动态设置MSFlexGrid的行和列
MSFlexGrid1.Cols = rstByQuery.Fields.Count + 1
MSFlexGrid1.Row = 0
For i = 0 To rstByQuery.Fields.Count - 1
MSFlexGrid1.Col = i + 1
MSFlexGrid1.Text = rstByQuery.Fields.Item(i).Name
Next '设置第一行的标题,用域名填充i = 0
Set rstByQuery = mycommand.Execute()
Do While Not rstByQuery.EOF
i = i + 1
MSFlexGrid1.Row = i '确定行
For j = 0 To rstByQuery.Fields.Count - 1
MSFlexGrid1.Col = j + 1
MSFlexGrid1.Text = rstByQuery(j)
Debug.Print rstByQuery(j)
'添充所有的列
Next
rstByQuery.MoveNextLoop '这个循环用来填充MSFlexGrid的内容
set nocount on/off