ADODB.RecordSet: Operation is not allowed when the object is closed

HCL Domino Domino Forum
1

Hi

I have a problem with ADODB procedure, it send me next error message: “ADODB.RecordSet: Operation is not allowed when the object is closed”. This my code:

Dim objConnection As Variant

Dim objCommand As Variant

Set objConnection = CreateObject(“ADODB.Connection”)

objConnection.Open “Provider=SQLOLEDB;Server=”+servsql+“;database=ADAM;UID=”+ussql+“;PWD=”+pwsql+“”

Set objCommand = CreateObject(“ADODB.Command”)

Set objCommand.ActiveConnection = objConnection

objCommand.CommandText = “AMX_INFORMACION_TRABAJADORES_AMTENE”

objCommand.Execute

Set objRecordSet = objCommand.Execute

If objRecordSet.RecordCount = 0 Then 'Here send me error message

Msgbox("Success")

Else

Msgbox("Failure")

End If

objConnection.Close

Somebody can help me please?

Thanks.

2

Subject: ADODB.RecordSet: Operation is not allowed when the object is closed

For one thing, I don’t see you have a correct query string, which may lead to a null recordset object after the query is executed. Another thing is, don’t use recordCount, it’s not reliable. Instead, use NextRecord(?) combined with EOF or BOF.

3

Subject: RE: ADODB.RecordSet: Operation is not allowed when the object is closed

Thanks for your answer Shigang. I need to execute a SQL procedure called “amx_informacion_trabajadores_AMTENE”, this it code:

CREATE PROC amx_informacion_trabajadores_AMTENE AS

CREATE TABLE #TABPASO(trabajador VARCHAR(10),

					 nombre     VARCHAR(100),

					 estatus    VARCHAR(1),

					 puesto     VARCHAR(50),

					 division   VARCHAR(50),

             direccion  VARCHAR(50),

					 ccostos    VARCHAR(50),

					 estado     VARCHAR(50),

					 e_mail     VARCHAR(40))

INSERT INTO #TABPASO(trabajador,nombre,estatus,puesto,division,direccion,ccostos,estado,e_mail)

SELECT tr.trabajador,REPLACE(tr.nombre,‘/’,’ ‘),tg.sit_trabajador,pu.descripcion,’‘,’‘,’',

   tr.estado_provincia,tr.e_mail

FROM trabajadores tr,trabajadores_grales tg,

   plazas pl,puestos pu

WHERE tg.compania = ‘0001’

AND tg.trabajador = tr.trabajador

AND tg.trabajador >= ‘MX00020’

AND tg.compania = pl.compania

AND tg.trabajador = pl.trabajador

AND pl.puesto = pu.puesto

UPDATE #TABPASO

SET #TABPASO.direccion = rel_trab_agr.dato

FROM #TABPASO,rel_trab_agr

WHERE #TABPASO.trabajador = rel_trab_agr.trabajador

AND rel_trab_agr.agrupacion = ‘MXAT003’

UPDATE #TABPASO

SET #TABPASO.division = rel_trab_agr.dato

FROM #TABPASO,rel_trab_agr

WHERE #TABPASO.trabajador = rel_trab_agr.trabajador

AND rel_trab_agr.agrupacion = ‘MXAT005’

UPDATE #TABPASO

SET #TABPASO.ccostos = rel_trab_agr.dato

FROM #TABPASO,rel_trab_agr

WHERE #TABPASO.trabajador = rel_trab_agr.trabajador

AND rel_trab_agr.agrupacion = ‘MXAT007’

UPDATE #TABPASO

SET estatus = ‘A’

WHERE estatus = ‘1’

UPDATE #TABPASO

SET estatus = ‘B’

WHERE estatus = ‘2’

SELECT *

FROM #TABPASO

ORDER BY trabajador

It´s so long. I tried to execute with this code: "objCommand.CommandText = “EXEC AMX_INFORMACION_TRABAJADORES_AMTENE”, but it doesný work. SQL procedure create a temporaly table with information that I need to retrieve. what´s better way for to do it?.

Thanks in advance.

4

Subject: RE: ADODB.RecordSet: Operation is not allowed when the object is closed

You’ve never used SQL procedures. May be you have a good reason, but it seems to me from the code it merely adds an extra layer of code for you.

Can you use change the procedure of “Inser Into” which create a tempory table, to “select …” which will create a RecordSet? Creating a recordset with data should be faster than creating a tempory table with data table. A recordset can be re-used just like a tempory table.

My 2-cents.