I want to call my stored procedure at the SQL server from VB with a new table name each time and get a recordset back. My stored procedure look something like this:
CREATE procedure upSU_CrossCheck
@tbl nvarchar(10)
as
declare @sqlstring nvarchar(500)
declare @paradef nvarchar(500)
declare @paratbl nvarchar(10)
set @sqlstring = N'
SELECT Country, Ship FROM ' + @tbl
set @paratbl = @tbl
set @paradef = N'@tbl nvarchar(10)'
EXEC sp_executesql @sqlstring, @paradef, @tbl = @paratbl
And my VB:
Dim rst As adodb.Recordset
Set cmd.ActiveConnection = con
cmd.CommandText = "test"
cmd.CommandType = adCmdStoredProc
Set rst = cmd.Execute
Dim n As Integer
If (Not rst.EOF) Then
Do Until rst.EOF
Debug.Print rst(0); rst(1); rst(2)
rst.MoveNext
Loop
End If
However, I do not get a recordset back when I execute it. If I run it from VB it tells me that the recordset is not open. How do I do that?
CREATE procedure upSU_CrossCheck
@tbl nvarchar(10)
as
declare @sqlstring nvarchar(500)
declare @paradef nvarchar(500)
declare @paratbl nvarchar(10)
set @sqlstring = N'
SELECT Country, Ship FROM ' + @tbl
set @paratbl = @tbl
set @paradef = N'@tbl nvarchar(10)'
EXEC sp_executesql @sqlstring, @paradef, @tbl = @paratbl
And my VB:
Dim rst As adodb.Recordset
Set cmd.ActiveConnection = con
cmd.CommandText = "test"
cmd.CommandType = adCmdStoredProc
Set rst = cmd.Execute
Dim n As Integer
If (Not rst.EOF) Then
Do Until rst.EOF
Debug.Print rst(0); rst(1); rst(2)
rst.MoveNext
Loop
End If
However, I do not get a recordset back when I execute it. If I run it from VB it tells me that the recordset is not open. How do I do that?