Table variable to VB Recordset???

[rmahawaii]

Hello, I am having difficulties with table variables in SQL2000. I have a stored procedures that select * from @tableTmp at the end of the script. Now, suppose I pass this table variable back to vb's recordset. Unfortunately, it said the object or variable is close when I try to use it in VB. Any solutions on how to pass table variable back to VB? thanks in advance.

'strSQL = "EXEC sp_getCustIDChanged"

With rs
.CursorType = adOpenForwardOnly
.CursorLocation = adUseClient
.Open strSQL, conn, adOpenForwardOnly, adLockOptimistic
End With

MsgBox rs.RecordCount '<--------- Error generated here

 

[sunaj]

Hi,

You cannot pass a table variable back to VB, because there are no VB types that match the SQL server Table type. But you can (as you already have done) SELECT * FROM @tableTMP at the end of your Stored Procedure. That will return a recordset to VB with the contents of the table.

Sot why dosn't it work for you? I don't know. One thing I can see in you code is the forward only cursors (by the way, you define that twice) does not support .recordcount.

Try to debug you code while you keep and eye on the .state property, it should change to 1 when the recordset opens. If all fails - paste you SP and VB code here and I'll take a look at it. Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[rmahawaii]

Thanks for your help Sunaj, I didn't know .RecordCount does not work with Forward only cursors. But that didn't solve my problem either and time was running out. So I decided to changed to user function instead. At least that returned a recordset I can open in VB. Thanks for your time anyway.