Update from stored procedure error

Report · Nov 15, 2006

Hi all

I'm getting into knots over this - any help much appreciated.

I'm using MSSQL (Dev edition), classic ASP, Dreamweaver 8.

Some time ago, I created an update command (from within the Dreamweaver
bindings panel) to record hits. This worked for a short time, but then
crashed the site - apparently the version I have of MSSQL only allows 8
concurrent users.

I was told that MySQL didn't suffer from this problem, so I created a
database to record the hits, and applied the same technique. However, after
another short time, the same thing happened.

I was then given some accurate advice as to why this was happening - the
connection wasn't being closed after the update was performed. I looked at a
tutorial available from www.charon.co.uk and have applied this - so far so
good!

However, it would be much easier, I thought, to write a stored procedure
that did the update (and keep everything in the one database). So, I duly
created one and applied it to the page. Partial success - the MSSQL
database 'hits' field is incremented by 1 - but I now get the following
error message, which I've looked up on google, but haven't found an answer I
can understand!

ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.

/page.asp, line 429

The line referred to is the 'close recordset' element at the foot of the
page.

Any suggestions or advice on how to carry out this apparently simple thing?

Cheers

Chris

Report · Nov 15, 2006

There is no recordset to close on a command object that issues an update
command. It's a little different for command object versus recordset
object:

yourCommandObject.ActiveConnection.Close()
Set yourCommandObject = Nothing

"Sweens" <info@121technology.com> wrote in message
news:ejf72q$jad$1@forums.macromedia.com...
> Hi all
>
> I'm getting into knots over this - any help much appreciated.
>
> I'm using MSSQL (Dev edition), classic ASP, Dreamweaver 8.
>
> Some time ago, I created an update command (from within the Dreamweaver
> bindings panel) to record hits. This worked for a short time, but then
> crashed the site - apparently the version I have of MSSQL only allows 8
> concurrent users.
>
> I was told that MySQL didn't suffer from this problem, so I created a
> database to record the hits, and applied the same technique. However,
> after another short time, the same thing happened.
>
> I was then given some accurate advice as to why this was happening - the
> connection wasn't being closed after the update was performed. I looked at
> a tutorial available from www.charon.co.uk and have applied this - so far
> so good!
>
> However, it would be much easier, I thought, to write a stored procedure
> that did the update (and keep everything in the one database). So, I duly
> created one and applied it to the page. Partial success - the MSSQL
> database 'hits' field is incremented by 1 - but I now get the following
> error message, which I've looked up on google, but haven't found an answer
> I can understand!
>
> ADODB.Recordset error '800a0e78'
> Operation is not allowed when the object is closed.
>
> /page.asp, line 429
>
> The line referred to is the 'close recordset' element at the foot of the
> page.
>
> Any suggestions or advice on how to carry out this apparently simple
> thing?
>
> Cheers
>
> Chris
>
>
>

Report · Nov 15, 2006

Many thanks - that works a treat! Culdn't find any reference to it
anywhere - thank goodness for user groups!

I hope this solves the concurrent users issue.
Cheers
Chris

"Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
news:ejf91i$ljp$1@forums.macromedia.com...
> There is no recordset to close on a command object that issues an update
> command. It's a little different for command object versus recordset
> object:
>
> yourCommandObject.ActiveConnection.Close()
> Set yourCommandObject = Nothing
>
>
> "Sweens" <info@121technology.com> wrote in message
> news:ejf72q$jad$1@forums.macromedia.com...
>> Hi all
>>
>> I'm getting into knots over this - any help much appreciated.
>>
>> I'm using MSSQL (Dev edition), classic ASP, Dreamweaver 8.
>>
>> Some time ago, I created an update command (from within the Dreamweaver
>> bindings panel) to record hits. This worked for a short time, but then
>> crashed the site - apparently the version I have of MSSQL only allows 8
>> concurrent users.
>>
>> I was told that MySQL didn't suffer from this problem, so I created a
>> database to record the hits, and applied the same technique. However,
>> after another short time, the same thing happened.
>>
>> I was then given some accurate advice as to why this was happening - the
>> connection wasn't being closed after the update was performed. I looked
>> at a tutorial available from www.charon.co.uk and have applied this - so
>> far so good!
>>
>> However, it would be much easier, I thought, to write a stored procedure
>> that did the update (and keep everything in the one database). So, I duly
>> created one and applied it to the page. Partial success - the MSSQL
>> database 'hits' field is incremented by 1 - but I now get the following
>> error message, which I've looked up on google, but haven't found an
>> answer I can understand!
>>
>> ADODB.Recordset error '800a0e78'
>> Operation is not allowed when the object is closed.
>>
>> /page.asp, line 429
>>
>> The line referred to is the 'close recordset' element at the foot of the
>> page.
>>
>> Any suggestions or advice on how to carry out this apparently simple
>> thing?
>>
>> Cheers
>>
>> Chris
>>
>>
>>
>
>