Stored procedure

[xykhor]

Dear experts,

I encounter this error below when I run the VB code and stored procedure.

Run-time error '3704'.
Operation is not allowed when the object is closed

The VB code and T-SQL procedure are stated below:

-- VB Code --------------------------------------------------------------------
Dim cd As New ADODB.Command
Dim rs As ADODB.Recordset

cd.CommandType = adCmdStoredProc
cd.CommandText = "GetCustomers"
Set rs = cd.Execute
While Not rs.EOF
dblCustomerNumber = rs!CUSTOMER_ID
strCustomerAddress = rs!CUSTOMER_ADDRESS
rs.MoveNext
Wend
-------------------------------------------------------------------------------



-- T-SQL Procedure ------------------------------------------------------------
CREATE PROCEDURE GetCustomers

AS

BEGIN
DECLARE @customer_id as datetime,
@customer_address as varchar(50)


CREATE TABLE #CUSTOMERS (
CUSTOMER_ID DECIMAL(13) NOT NULL,
CUSTOMER_ADDRESS VARCHAR(50) NULL
)

-- Declare local cursor to retrieve data from table CUSTOMERS
DECLARE cc CURSOR LOCAL FOR
SELECT CUSTOMER_ID, CUSTOMER_ADDRESS
FROM CUSTOMERS
FOR READ ONLY
OPEN cc

-- Loop and change all customer 5 addresses
WHILE 1 = 1 BEGIN
FETCH cc
INTO @customer_id, @customer_address

IF @@FETCH_STATUS <> 0 BREAK

IF @customer_id = 5 BEGIN
@customer_addresss = 'Newcastle'
END

INSERT INTO #CUSTOMERS (CUSTOMER_ID, CUSTOMER_ADDRESS)
VALUES @customer_id, @customer_address
END

SELECT * FROM #CUSTOMERS

CLOSE cc
DEALLOCATE cc
END
-------------------------------------------------------------------------------

I suspect that the object to retrieve the recordset from T-SQL stored procedure to VB code is closed. However I have already declare cursor cc as LOCAL. I suspect that the object is a global cursor and I have no idea why the it was closed. I suspect it has something to do with using cursor at the same time returning recordsets back to the VB code.

Could you please kindly advice me on how to solve this problem? Thank you very much in advance.

[Mc Brain]

I didn't read that much the code... But aren't you forgetting to set the active connection to your command?

[Mc Brain]

I really don't know squat 'bout it. I would like to help you but have no clue what the problem could be. I don't know it the active connection was your problem or not. However, I use the idea of your T-SQL to make one of my own. I needed something like you did. Create a temporary table and change one field of all the records. I MADE IT It works great in the ISQL/w. However, I get the same error as you got. Just after the rs.open Statement, ADOConnection, the recordset appears to be closed.
If you found out what was going on, I would be glad to know your solution. Thanks in advance,