Unusual Problem with SQL Server SP and ASP

[py1rdj]

I have this sub and this SP. SOMETIMES I get the message:

"THE OPERATION IS NOT ALLOWED WHEN THE OBJECT IS CLOSED"

I'm using SQL Server 7 and IIS on a Windows 2000 Server.
I can't understand why it happens only sometimes. And when it fails I just refresh the page and it works with the very same data and parameters.
I have several other similar Subs running similar SP's and I don't have any problems. All the other SP's use the sp_Download procedure.

That's the Sub:

*********************************************************

Sub btnCartanClick

On Error Resume Next

dim id, ncartao, oper, nome, comp

id = parent("frmMain&quot.inpCH05CardHolderID.value
ncartao = inpCartao.value
oper = parent("frmMain&quot.operador.value
nome = inpCH11Visitee.value
comp = vis.inpCH11UF20.value

dim server
server = parent("frmMain&quot.ServerName.value

If ncartao <> &quot;&quot; Then

If inpCH11FirstName.value = &quot;&quot; Then
parent(&quot;frmGlobal&quot.SetStationMessage &quot;O campo 'Nome' deve ser preenchido&quot;
Exit Sub
ElseIf inpCHPreferredName.value = &quot;&quot; Then
parent(&quot;frmGlobal&quot.SetStationMessage &quot;O campo 'Documento' deve ser preenchido&quot;
Exit Sub
ElseIf inpCH11Company.value = &quot;&quot; Then
parent(&quot;frmGlobal&quot.SetStationMessage &quot;O campo 'Empresa' deve ser preenchido&quot;
Exit Sub
'ElseIf inpCH11UF19.value <= 0 Then
'parent(&quot;frmGlobal&quot.SetStationMessage &quot;O campo 'Fone para contato' deve ser preenchido&quot;
'Exit Sub
ElseIf inpCH11Visitee.value = &quot;&quot; and vis.inpCH11UF20.value = &quot;&quot; Then
parent(&quot;frmGlobal&quot.SetStationMessage &quot;O campo 'Visitado ou Visitado - Complemento' deve ser preenchido&quot;
Exit Sub
Else

dim objConn, rs, sql, strConn
set objConn = CreateObject(&quot;ADODB.Connection&quot
strConn = &quot;Provider=SQLOLEDB;Server=&quot;&server&&quot;;Database=CMS;UID=cms;PWD=ebi;&quot;
objConn.open strConn

sql = &quot;EXEC sp_AssociaCartaoVisitante &quot;&id&&quot;, &quot;&ncartao&&quot;, '&quot;&oper&&quot;', '&quot;&nome&&quot;', '&quot;&comp&&quot;' &quot;

objConn.execute(sql)
'####I SEE THE MESSAGE USING THIS CODE:
If Err.Number <> 0 Then
parent(&quot;frmGlobal&quot.SetStationMessage Err.Description
Err.Clear
Exit Sub
End If

objConn.Close
set objConn = Nothing

'Salva o cadastro e atualiza a tela
SaveRecord
window.parent.navigate &quot;

http://&quot;&server&&quot;/cms/WebPages/framesearch.asp?CardHolderID=&quot;&id&&quot;&quot;

End If

End If

End Sub

*********************************************************

And that's the SP:

#########################################################

--Script para automatizar o fornecimento de crachás de visitantes:

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'sp_AssociaCartaoVisitante' AND type = 'P')
DROP PROCEDURE sp_AssociaCartaoVisitante
GO

USE CMS
GO
CREATE PROCEDURE sp_AssociaCartaoVisitante
@ID nchar(6),
@CARD nchar(8),
@OPER nchar(10),
@VISITADO varchar(50),
@VISITADOCOMP varchar(500)
WITH ENCRYPTION
AS

SET NOCOUNT ON

--Grava o número real do cartão a ser associado
DECLARE @cartaoreal nchar(8)
SET @cartaoreal = (SELECT CardNumber FROM TB_CartaoVisitantes WHERE CardAlias = @CARD)
--SET @cartaoreal = @CARD

IF EXISTS (SELECT CardNumber FROM TB_CartaoVisitantes WHERE CardAlias = @CARD)
AND EXISTS (SELECT CardNumber FROM Cards WHERE CardNumber = @cartaoreal)
--IF EXISTS (SELECT CardNumber FROM Cards WHERE CardNumber = @cartaoreal)
BEGIN

IF (SELECT COUNT(*) FROM Cards WHERE CardHolderID = @ID AND CardStateID = 0) = 0
BEGIN
IF ((SELECT CardHolderID FROM Cards WHERE CardNumber = @cartaoreal) <> 0
AND (SELECT CardStateID FROM Cards WHERE CardNumber = @cartaoreal) <> 0)
OR (SELECT CardHolderID FROM Cards WHERE CardNumber = @cartaoreal) = 0
BEGIN
--Associa o cartão e efetua download:

--Marca o CardHolder para Download e o coloca Ativo:
UPDATE CardHolders
SET HolderDownloadRequired = 1 WHERE CardHolderID = @ID
UPDATE CardHolders
SET CHStateID = 0 WHERE CardHolderID = @ID
UPDATE CardHolders
SET CHCommencementDateTime = getdate() WHERE CardHolderID = @ID
UPDATE CardHolders
SET CHExpiryDateTime = getdate()+ 1 WHERE CardHolderID = @ID

--Ativa o cartão:
UPDATE Cards
SET CardHolderID = 0 WHERE CardHolderID = @ID
UPDATE Cards
SET CardHolderID = @ID, CardStateID = 0 WHERE CardNumber = @cartaoreal
UPDATE Cards
SET CardCommencementDateTime = getdate() WHERE CardHolderID = @ID
UPDATE Cards
SET CardExpiryDateTime = getdate()+ 1 WHERE CardHolderID = @ID

EXEC sp_Download @cartaoreal, @ID

INSERT INTO TB_VisitantesLog (CardHolderID, NCartao, DataLiberacao, LibPor, Visitado, VisitadoComp)
VALUES(@ID, @cartaoreal, getdate(), @OPER, @VISITADO, @VISITADOCOMP)

UPDATE CardHolders
SET LastModifBy = @OPER, LastModifDateTime = getdate() WHERE CardHolderID = @ID
UPDATE Cards
SET LastModifBy = @OPER, LastModifDateTime = getdate() WHERE CardHolderID = @ID

END
ELSE
RAISERROR ('O cartão já está sendo utilizado', 16, 1)

END
ELSE
RAISERROR ('O visitante possui cartão associado', 16, 1)

END
ELSE
RAISERROR ('Cartão inválido', 16, 1)

GO

###########################################################

Thanks in advance,

André Silva

 

[vbkris]

can u point out the line where the error happens?
(i guess objConn.execute(sql) will give the error)

Known is handfull, Unknown is worldfull

 

[py1rdj]

That's correct.

Thanks.

 

[vbkris]

there maybe two possiblities:
either the connection does not open (problem with the cons tring)
or
this maybe a bit tiresome but then
comment objConn.Close

now try refreshing the page... slowly we can find out the problem...

Known is handfull, Unknown is worldfull

 

[py1rdj]

The problem was with the SaveRecord sub called from the end of the sub above.

Thank you very much.