ADODB.Recordset (0x800A0E78) 3

[bryant89]

Hi there I need help with this
I get this error
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/MCERegister/_ScriptLibrary/Recordset.ASP, line 762

I am using a dtc recordset control on my page that is using a SQL stored procedure as its source. I am using SQL Server 2000. I thought it might be due to not have set nocount on but I have it set on and I still get this error. When I run the execute the procedure alone the procedure works great. But when I connect to it through a dtc recordset on my asp page I get the above error.

The SQL is as follows.
Alter Procedure stpInsertMemberStatus
/*
This procedure is adding new member status's to tblMemberStatus
*/
(
@nUserID int,
@nAssociationID int,
@nTermID int,
@nPassed bit
)
AS
set nocount on
DECLARE @ReturnedUserID int,@ReturnedAssociationID int,@ReturnedTermID int,@ReturnedPassed bit

Select @ReturnedUserID=msUsID,@ReturnedAssociationID=msAsID,@ReturnedTermID=msTermID,@ReturnedPassed=msPassed from tblMemberStatus
where msUsID=@nUserID and
msAsID=@nAssociationID and
msTermID=@nTermID

If (@ReturnedUserID is Null or @ReturnedUserID='') and @nUserID<>'0'
Begin

INSERT INTO tblMemberStatus (msUsID,msAsID,msTermID,msPassed)
VALUES (@nUserID,@nAssociationID,@nTermID,@nPassed)
End
Else If @nPassed <> @ReturnedPassed
Begin
UPDATE tblMemberStatus SET msPassed=@nPassed WHERE
msUsID=@nUserID and
msAsID=@nAssociationID and
msTermID=@nTermID
End

 

[link9]

That error stems from you trying to execute some command on an object that has not been opened, but must be opened in order for the operation to take place.

It's definitely your ASP code, and not the SQL that's the culprit.

Examine line 762 of your ASP code, and see what object you are working with. That's the object in question.

Most likely, you have forgotten to open your connection, but that's just a guess.

post back with line762 if your error isn't apparent.


paul

 

[jonscott8]

Post the portion of Recordset.asp related to the object that is attempting to perform an operation on line 762. Jon Hawkins

 

[bryant89]

I think its gotta be something to do with these dtc recordsets. I am using visual interdev so I just drag and drop the recordset onto the page tell its source via a dropdown list of my stored procedures. I set its implementation to open automattically when the page opens. And throughout my loop of code I do not tell the recordset to close. I think the problem is that I pass this recordset different values as I loop through another recordset. If I refresh the page the next record is inserted to the table but I still get the error. I can refresh the page as many times until the loop finishes and the the error dissappears and the page displays and all the appropriate records are added. But I should not get that error and have to refresh x amount of times until the loop finishes. I dont know if maybe the loop is moving to fast and the recordset that accesses the SQL I posted above doesnt have time to run the SQL or whatever. Aparantly it is having trouble synchronizing its EOF or BOF but there should be no EOF or BOF because I am not returning any records. That is why I am questioning the set nocount on.
I thought if I set nocount on that nothing would be returned and therefore the recordset should not be trying to sync the eof or bof.

I hope this makes sense

line 762 of Recordset.asp is
this.EOF = this._rsADO.EOF;
in the following code

function _RS__syncBOFandEOF()
{
if (this.isOpen())
{
this.EOF = this._rsADO.EOF;
this.BOF = this._rsADO.BOF;
this.absolutePosition = this._rsADO.AbsolutePosition;
}
}

 

[bryant89]

Is there some way I can edit the Recorset.asp to ignore this function just for this particular recordset?

 

[link9]

Well, since I don't know DTC, then I'm afraid I can't offer advice there. However, I can tell you exactly what's wrong, and that's this:

Your recordset that the this. operator is referring to in that function is not open at the moment you are asking it for .EOF. For sure.

However, setting nocount = on does only one thing. It reduces your network traffic by not sending back those little record affected (1 record affected) messages, since you don't really need them in your ASP most of the time.

So setting that does not make the recordset return nothing.

And that brings me to my next point. Judging from what you have posted up there, am I correct in assuming that you are simply trying to ALTER a stored procedure in this particular operation? Even if I'm not correct there, ask yourself this question:

&quot;If I want an empty recordset, then why do I need one in the first place?&quot;

If all you are doing is issuing a command to your database, then issue it directly on your connection object like so:

dim sql
sql = &quot;ALTER PROC ....&quot; 'blahblahblah
connectionObject.execute sql

something to that effect, and just bypass the recordset altogether. I might be totally off base here, but it sounds to me like you aren't trying to return any data, but are trying to use a recordset.

hope that helps.
paul

 

[bryant89]

yes that is exactly what I need to do I just need to pass 4 parameters to a stored procedure and thats it.
Would you be able to give me a better example of how to do that. I have just been using recordsets to pass values to a stored procedure because I am just learning and did not know how to do it in asp code
I tried something like this but it did not work for me

dim cn,cmd
Set cn = Server.CreateObject(&quot;ADODB.Connection&quot
Set cmd = Server.CreateObject(&quot;ADODB.Command&quot
cn.Open &quot;MCERegister&quot;
Set cmd.ActiveConnection = cn
cmd.CommandText = &quot;stpInsertMemberStatus&quot;
cmd.CommandType = adCmdStoredProc
' Ask the server about the parameters for the stored proc
cmd.Parameters.Refresh
' Assign a value to the 2nd parameter.
' Index of 0 represents first parameter.
cmd.Parameters(0) = 4
cmd.Parameters(1) = 4
cmd.Parameters(2) = 4
cmd.Parameters(3) = 4

cmd.Execute

You have explained a lot to me.
Thanks

 

[link9]

dim cn,cmd

Set cn = Server.CreateObject(&quot;ADODB.Connection&quot
Set cmd = Server.CreateObject(&quot;ADODB.Command&quot
cn.Open &quot;MCERegister&quot;
Set cmd.ActiveConnection = cn
cmd.CommandText = &quot;stpInsertMemberStatus&quot;
cmd.CommandType = 4 'adCmdStoredProcedure

cmd.Parameters(@nUserID).value = 4
cmd.Parameters(@nAssociationID).value = 4
cmd.Parameters(@nTermID).value = 4
cmd.Parameters(@nPassed).value = 4

cmd.Execute

I axed the .refresh method, because that doesn't ask the stored procedure for anything. It wipes any existing values for the parameters you might have already assigned, and is problematic, at best. Try not to use that unless you must.

I also replaced your ordinals with the names of your parameters. I've never used ordinals in this way, although I don't see why it WOULDN'T work.

Give it a shot this way, and if it doesn't work, post back the error it returns.


paul

 

[bryant89]

ok tks Paul I will post back to you on Monday

 

[bryant89]

I am getting this error.

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
/MCERegister/User/frmCurrentTermtest.asp, line 2605


Here is the contents of line 2605
cn.Open &quot;MCERegister&quot;

I am not sure if I am making a proper connection string or declaring cn and cmd properly.
My code now looks like the following

<%
dim cn,cmd

Set cn = Server.CreateObject(&quot;ADODB.Connection&quot
Set cmd = Server.CreateObject(&quot;ADODB.Command&quot
cn.Open &quot;MCERegister&quot;
Set cmd.ActiveConnection = cn
cmd.CommandText = &quot;stpInsertMemberStatus&quot;
cmd.CommandType = 4 'adCmdStoredProcedure

cmd.Parameters(0) = 4
cmd.Parameters(1) = 4
cmd.Parameters(2) = 4
cmd.Parameters(3) = 4

cmd.Execute
%>

I could not use
cmd.Parameters(@nUserID).value = 4
cmd.Parameters(@nAssociationID).value = 4
cmd.Parameters(@nTermID).value = 4
cmd.Parameters(@nPassed).value = 4

the @nUserID etc was giving me errors.

 

[jonscott8]

PMFJBI, but AFA the connection error, does the MCERegister DSN exist on your webserver? Is it a System DSN? Just to be explicit, you may want to change it to:

cn.Open &quot;DSN=MCERegister&quot;

AFA using the named parameters, enclose them in double quotes:

cmd.Parameters(&quot;@nUserID&quot;).value = 4

AFA Paul's comments on the Refresh method, I for the most part, disagree. AFAIK, you cannot implicitly create the parameter collection as per his suggested code example (The documentation states that if you access the parameters collection prior to calling the Refresh method, ADO internally calls the Refresh method - but I've always found this to be inconsistent, or as he states - problematic).

cmd.Parameters(&quot;@nUserID&quot.Value = 4

@nUserID must already exist in the parameters collection, created using either the Refresh, Append, or CreateParameter methods. Explicit coding is far easier to maintain. Jon Hawkins

 

[bryant89]

ok I think I got it figured out. I just thought I would post to let you know how I got it to work.
I had to create a system dsn. At the current time I was only using a file.dsn. I had to go to Control Panel Select Admin tools then ODBC Source Admin. Then I had to select System DSN. hit add to create a new system dsn based on the database I am trying to connect to. I then used the name of the dsn in my connection. as cn.Open &quot;MCERegisterSystem&quot;. My code now looks as the following
<%
dim cn,cmd
Set cn = Server.CreateObject(&quot;ADODB.Connection&quot
Set cmd = Server.CreateObject(&quot;ADODB.Command&quot
cn.Open &quot;MCERegisterSystem&quot;
Set cmd.ActiveConnection = cn
cmd.CommandText = &quot;stpInsertMemberStatus&quot;
cmd.CommandType = 4 'adCmdStoredProcedure

cmd.Parameters(&quot;@nUserID&quot.value = 4
cmd.Parameters(&quot;@nAssociationID&quot.value = 4
cmd.Parameters(&quot;@nTermID&quot.value = 4
cmd.Parameters(&quot;@nPassed&quot.value = 4


cmd.Execute
%>

Thanks very much for all the help, It is greatly appreciated.

 

[link9]

Glad you got it working, bryant89.

A question for Jon:

As I stated, I have found the .refresh method to be very problematic. Innefficient -- non-recreatable errors, etc...

So, I remember reading at some point in the past a much better way for &quot;refreshing&quot; parameters in your command object, w/out using the .refresh method because of the aforementioned problems.

Would you happen to know this method, and be kind enough to share it here? It was basically a manual method, is about all I can remember.

As far as the implicit call to .refresh with adding the parameters like I have shown... learn something new every day. Wonder why it doesn't cause the same problems as .refresh?

If you would elaborate, I think we would all gain something out of it.

Thanks!
paul

 

[jonscott8]

Just for clarification, you can use implicit parameters but you need to enclose the parameter names in brackets:

cmd.Parameters(&quot;[@nUserID]&quot.Value = 4

Failing to do so, could result in an error like &quot;Item not found in collection&quot;.

IMO, the Refresh method should be used during development to determine parameter attributes. You should use this info to produce the code necessary to create the parameters explictly in your production code.

Paul - no, I do not recollect a way to refresh the parameters collection w/o using the Refresh method. You can rebuild the collection manually

oCmd.Parameters.Append oCmd.Parameters.CreateParameter(&quot;@UserID&quot;,3, 1, 0, 40)
oCmd.Parameters.Append oCmd.CreateParameter(&quot;Return_Value&quot;, 3, 4)

but that's not the same thing.

Side note to above, if you are executing Non-row returning queries, including the adExecuteNoRecords option will increase performance.

oConn.Execute sAlter,,128 Jon Hawkins

 

[link9]

Fair enough. I'll get out there and search out where I read that, and I'll post back to this thread when I find it.

Thanks for the info, Jon.

 

[bryant89]

I enclosed my parameters in brackets as suggested and I got the following error
Item cannot be found in the collection corresponding to the requested name or ordinal.

It seems to work fine without

Bryant