Wednesday, February 15, 2012

Error accessing stored procedure containing a temp table from VB

I am getting an error trying to generate a disconnected recordset from a SQL 2000 database. The application is in VB 6. I am using an ADO Command object.

This code works fine for most of my storer procedures. 2 procedures make use of temporary tables. The results set being passed top VB is a select from the temp table.

When I use the temp table I get an error that states:
"Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source."

If I check the recordcount of rstResults, in the immediate window, after the .open method I get an error stating that the object is not open.

Again this is only for the stored procedure that Selects from a temp table.

Here is the code:

On Error GoTo ErrHandler

Dim rstResults As ADODB.Recordset
Dim conSQL As ADODB.Connection
Dim comFetch As ADODB.Command

Set conSQL = New Connection
conSQL.Open gstrAdminConn

'-- Associate the Command object to stored procedure --
Set comFetch = New ADODB.Command

With comFetch
Set .ActiveConnection = conSQL
.CommandType = adCmdStoredProc
.CommandText = "FetchChildren"
.Parameters("@.PageCode") = strPageCode
.Execute
End With

Set rstResults = New ADODB.Recordset
With rstResults

'-- Open a disconnected recordset off of the command --
.CursorLocation = adUseClient
.Open comFetch, , adOpenKeyset, adLockBatchOptimistic

'-- Dissociate command from connection --
Set comFetch.ActiveConnection = Nothing
Set comFetch = Nothing

'-- Dissociate recordset from connection --
'Set .ActiveConnection = Nothing

End With

Set GetChildren = rstResults

Exit Function

ErrHandler:
err.Raise vbObjectError + 1001, "basPages.GetChildren", Error$try using a

"set nocount on" at the begining of your procedure.|||Thanks. The SET NOCOUNT ON worked.

Why was that causing the error?

No comments:

Post a Comment