Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Operation is not allowed...

Reply
Thread Tools

Operation is not allowed...

 
 
James Baker
Guest
Posts: n/a
 
      08-24-2004
I'm getting an error: "Operation is not allowed when the object is closed."
on the lines of code below. I can't imagine why the object would be closed
where it says it will be, I've just opened it on the line before. This is
all within a larger loop, but the loop seems to be working fine and other
insert statements within it seem ok. Any suggestions?

Dim cmsRS, cmsSQL
Dim insRS, insSQL

Set cmsRS = Server.CreateObject("ADODB.Recordset")
Set insRS = Server.CreateObject("ADODB.Recordset")

cmsSQL = "SELECT ClientsFileNumber FROM TblOrder WHERE FileNumber = '" &
Request.Form("ref" & intR) & "' AND ClientCode = 'CMSNY'"

cmsRS.Open cmsSQL, "DSN=kasecure1;uid=sa;pwd=pcbs"

If NOT cmsRS.EOF Then
insSQL = "INSERT INTO CMS (FileNumber, StatusDate, StatusTime,
StatusComment) VALUES('" & Request.Form("ref" & intR) & "', '" &
Request.Form("sd" & intR & "_" & intsc & "', '" & adstime & "', 'Inspection
Date has been set for " & isd & "')"

' / Begin Problem
insRS.Open insSQL, "DSN=kasecure1;uid=sa;pwd=pcbs"
insRS.Close ' Errors on this line
' / End Problem
End If

cmsRS.Close
Set cmsRS = Nothing
Set insRS = Nothing


 
Reply With Quote
 
 
 
 
Jeff Dillon
Guest
Posts: n/a
 
      08-24-2004
try passing in a valid connection object to the recordset open, instead of a
connection string.

Jeff

"James Baker" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I'm getting an error: "Operation is not allowed when the object is

closed."
> on the lines of code below. I can't imagine why the object would be

closed
> where it says it will be, I've just opened it on the line before. This is
> all within a larger loop, but the loop seems to be working fine and other
> insert statements within it seem ok. Any suggestions?
>
> Dim cmsRS, cmsSQL
> Dim insRS, insSQL
>
> Set cmsRS = Server.CreateObject("ADODB.Recordset")
> Set insRS = Server.CreateObject("ADODB.Recordset")
>
> cmsSQL = "SELECT ClientsFileNumber FROM TblOrder WHERE FileNumber = '" &
> Request.Form("ref" & intR) & "' AND ClientCode = 'CMSNY'"
>
> cmsRS.Open cmsSQL, "DSN=kasecure1;uid=sa;pwd=pcbs"
>
> If NOT cmsRS.EOF Then
> insSQL = "INSERT INTO CMS (FileNumber, StatusDate, StatusTime,
> StatusComment) VALUES('" & Request.Form("ref" & intR) & "', '" &
> Request.Form("sd" & intR & "_" & intsc & "', '" & adstime & "',

'Inspection
> Date has been set for " & isd & "')"
>
> ' / Begin Problem
> insRS.Open insSQL, "DSN=kasecure1;uid=sa;pwd=pcbs"
> insRS.Close ' Errors on this line
> ' / End Problem
> End If
>
> cmsRS.Close
> Set cmsRS = Nothing
> Set insRS = Nothing
>
>



 
Reply With Quote
 
 
 
 
Aaron [SQL Server MVP]
Guest
Posts: n/a
 
      08-24-2004
OK.

(a) stop using a DSN (http://www.aspfaq.com/2126).

(b) stop using ADODB.Recordset (http://www.aspfaq.com/2191), especially for
AFFECTING data.

(c) don't just arbitrarily insert user input into a SQL string... search
google groups for "SQL Injection" to understand why.

(d) use a stored procedure, if this is SQL Server
(http://www.aspfaq.com/2201).

(e) tell us what database and version you are using, so we don't have to
guess (http://www.aspfaq.com/5009).

(f) please give us complete specs, including DDL, sample data, desired
results (http://www.aspfaq.com/5006).

(g) don't separate date and time... what is the purpose of that? Are you
storing date and time values in VARCHAR columns?

Why don't you try an approach like this instead...

Stored procedure:

CREATE PROCEDURE dbo.InsertFile
@fn VARCHAR(12),
@dt VARCHAR(12),
@tm VARCHAR(12),
@comment VARCHAR(255)
AS
BEGIN
SET NOCOUNT ON
IF EXISTS
(
SELECT 1
FROM TblOrder
WHERE FileNumber = @fn
AND ClientCode = 'CMSNY'
)
INSERT CMS
(
FileNumber,
StatusDate,
StatusTime,
StatusComment
)
SELECT
@fn,
@dt,
@tm,
@comment
END
GO

ASP code:

fn = Replace(Request.Form("ref" & intR), "'", "''")
dt = Replace(Request.Form("sd" & intR & "_" & intsc), "'", "''")
set conn = CreateObject("ADODB.Connection")
conn.open "< use a real connection string here >"
sql = "EXEC dbo.InsertFile " & _
"@fn='" & fn & "'," & _
"@dt='" & dt & "'," & _
"@tm='" & adstime & "'," & _
"@comment='Inspection Date has been set for " & isd & "'"
conn.execute sql,,129
conn.close: set conn = nothing

--
http://www.aspfaq.com/
(Reverse address to reply.)




"James Baker" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I'm getting an error: "Operation is not allowed when the object is

closed."
> on the lines of code below. I can't imagine why the object would be

closed
> where it says it will be, I've just opened it on the line before. This is
> all within a larger loop, but the loop seems to be working fine and other
> insert statements within it seem ok. Any suggestions?
>
> Dim cmsRS, cmsSQL
> Dim insRS, insSQL
>
> Set cmsRS = Server.CreateObject("ADODB.Recordset")
> Set insRS = Server.CreateObject("ADODB.Recordset")
>
> cmsSQL = "SELECT ClientsFileNumber FROM TblOrder WHERE FileNumber = '" &
> Request.Form("ref" & intR) & "' AND ClientCode = 'CMSNY'"
>
> cmsRS.Open cmsSQL, "DSN=kasecure1;uid=sa;pwd=pcbs"
>
> If NOT cmsRS.EOF Then
> insSQL = "INSERT INTO CMS (FileNumber, StatusDate, StatusTime,
> StatusComment) VALUES('" & Request.Form("ref" & intR) & "', '" &
> Request.Form("sd" & intR & "_" & intsc & "', '" & adstime & "',

'Inspection
> Date has been set for " & isd & "')"
>
> ' / Begin Problem
> insRS.Open insSQL, "DSN=kasecure1;uid=sa;pwd=pcbs"
> insRS.Close ' Errors on this line
> ' / End Problem
> End If
>
> cmsRS.Close
> Set cmsRS = Nothing
> Set insRS = Nothing
>
>



 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      08-24-2004
James Baker wrote:
> I'm getting an error: "Operation is not allowed when the object is
> closed." on the lines of code below. I can't imagine why the object
> would be closed where it says it will be, I've just opened it on the
> line before. This is all within a larger loop, but the loop seems to
> be working fine and other insert statements within it seem ok. Any
> suggestions?
>
> Dim cmsRS, cmsSQL
> Dim insRS, insSQL
>
> Set cmsRS = Server.CreateObject("ADODB.Recordset")
> Set insRS = Server.CreateObject("ADODB.Recordset")


Since you are issuing multiple commands to the database, you should be using
a single Connection object instead of opening a new connection for each
command:

Dim cn
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "DSN=kasecure1;uid=sa;pwd=pcbs"

BTW, it is a horrible idea to use sa for your applications. Create a sql
login with limited permissions and use that instead of sa.

Also, you should use OLEDB instead of the obsolete ODBC.

cn.Open "Provider=SQLOLEDB;" & _
"Data Source=your_server_name;" & _
"Initial Catalog=the_database;" & _
"User ID = xxxx;Password=xxxx"

>
> cmsSQL = "SELECT ClientsFileNumber FROM TblOrder WHERE FileNumber =
> '" & Request.Form("ref" & intR) & "' AND ClientCode = 'CMSNY'"
>

cmsRS.Open cmsSQL, cn,,,1 '1=adCmdText


> If NOT cmsRS.EOF Then
> insSQL = "INSERT INTO CMS (FileNumber, StatusDate, StatusTime,
> StatusComment) VALUES('" & Request.Form("ref" & intR) & "', '" &
> Request.Form("sd" & intR & "_" & intsc & "', '" & adstime & "',
> 'Inspection Date has been set for " & isd & "')"
>


Is there any chance at all that this query will return records? No? So why
create and open an expensive recordset?

> ' / Begin Problem
> insRS.Open insSQL, "DSN=kasecure1;uid=sa;pwd=pcbs"
> insRS.Close ' Errors on this line


Since there were no records returned from your query, of course you don't
have an open re cordset at this point. Get rid of that createobject
statement for the second recordset and use this to execute a query that does
not return records:

cn.Execute insSQL,,129 '129=adCmdText(1)+adExecuteNoRecords(12

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


 
Reply With Quote
 
Aaron [SQL Server MVP]
Guest
Posts: n/a
 
      08-24-2004
> BTW, it is a horrible idea to use sa for your applications. Create a sql
> login with limited permissions and use that instead of sa.


Good catch, I was pretty thorough but I missed that one (even though that
was the only clue that this was SQL Server).

--
http://www.aspfaq.com/
(Reverse address to reply.)


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      08-24-2004
Aaron [SQL Server MVP] wrote:
>> BTW, it is a horrible idea to use sa for your applications. Create a
>> sql login with limited permissions and use that instead of sa.

>
> Good catch, I was pretty thorough but I missed that one (even though
> that was the only clue that this was SQL Server).


Thanks. You're right. That was my clue that we were dealing with sql server
(although it could have been sybase, I guess)

Bob
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


 
Reply With Quote
 
James Baker
Guest
Posts: n/a
 
      08-24-2004
I appreciate all of your feedback. The main answer to most of your "WHY?!"
questions is politics. I'm dealing with some of the worst code ever
written, backed by some of the worst SQL 2000 database design I've ever
seen. The head programmer doesn't want anything done differently than the
way he does it, and for the most part, he does it the wrong way.

Some of his code is absolutely insane. He once wrote an insert statement
into a page with a typo, "0rder has been reviewed" instead of "Order has
been reviewed". Well, instead of going back, fixing the typo and fixing any
inserts that were related to it, he decided he'd just query based on 0rder
instead. So now, half the records have Order, half have 0rder and the
confusion is never ending. He never indents his code, everything is flush
left. I've complained about the SA thing since day 1, and he refuses to
create another user. Just had to vent.

Anyway, I'll try rewriting this with a stored procedure, even though my DB
knowledge is pretty slim other than the "relational" aspect. I'll slip in
the removal of the DSN as well and hope it goes unnoticed.

Do you believe this will solve my original problem? I don't know what
exactly was causing that.

Thanks much,
James


"Aaron [SQL Server MVP]" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> > BTW, it is a horrible idea to use sa for your applications. Create a sql
> > login with limited permissions and use that instead of sa.

>
> Good catch, I was pretty thorough but I missed that one (even though that
> was the only clue that this was SQL Server).
>
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>



 
Reply With Quote
 
Aaron [SQL Server MVP]
Guest
Posts: n/a
 
      08-24-2004
Your original problem stemmed from using an ADODB.Recordset object to commit
an INSERT statement. Recordsets are for *retrieving* data, and even then an
explicit ADODB.Recordset is only advantageous in certain scenarios.

--
http://www.aspfaq.com/
(Reverse address to reply.)




"James Baker" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I appreciate all of your feedback. The main answer to most of your

"WHY?!"
> questions is politics. I'm dealing with some of the worst code ever
> written, backed by some of the worst SQL 2000 database design I've ever
> seen. The head programmer doesn't want anything done differently than the
> way he does it, and for the most part, he does it the wrong way.
>
> Some of his code is absolutely insane. He once wrote an insert statement
> into a page with a typo, "0rder has been reviewed" instead of "Order has
> been reviewed". Well, instead of going back, fixing the typo and fixing

any
> inserts that were related to it, he decided he'd just query based on 0rder
> instead. So now, half the records have Order, half have 0rder and the
> confusion is never ending. He never indents his code, everything is flush
> left. I've complained about the SA thing since day 1, and he refuses to
> create another user. Just had to vent.
>
> Anyway, I'll try rewriting this with a stored procedure, even though my DB
> knowledge is pretty slim other than the "relational" aspect. I'll slip in
> the removal of the DSN as well and hope it goes unnoticed.
>
> Do you believe this will solve my original problem? I don't know what
> exactly was causing that.
>
> Thanks much,
> James
>
>
> "Aaron [SQL Server MVP]" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > > BTW, it is a horrible idea to use sa for your applications. Create a

sql
> > > login with limited permissions and use that instead of sa.

> >
> > Good catch, I was pretty thorough but I missed that one (even though

that
> > was the only clue that this was SQL Server).
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >

>
>



 
Reply With Quote
 
James Baker
Guest
Posts: n/a
 
      08-24-2004
If the connection is open, can I execute two statements against it before I
close the connection? I'm opening the one recordset I'm using with it and
I'm also potentially executing the INSERT with it as well. Ultimately I
rewrote my code (pre-Stored Procedure) as:

Dim cmsRS, cmsSQL
Dim insSQL

Dim cn
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "Provider=SQLOLEDB;" & _
"Data Source=XXXXX;" & _
"Initial Catalog=XXXXX;" & _
"User ID = XXXXX;Password=XXXXX"

Set cmsRS = Server.CreateObject("ADODB.Recordset")

cmsSQL = "SELECT ClientsFileNumber FROM TblOrder WHERE FileNumber = '" &
filenumber & "' AND ClientCode = 'CMSNY'"
cmsRS.Open cmsSQL, cn, , , 1

If NOT cmsRS.EOF Then
insSQL = "INSERT INTO CMS (FileNumber, StatusDate, StatusTime,
StatusComment) VALUES('" & cmsRS("ClientsFileNumber") & "', '" & date() &
"', '" & adstime & "', 'Order has entered review process')"
cn.Execute insSQL,,129
End If

cmsRS.Close
Set cmsRS = Nothing
cn.Close


"Aaron [SQL Server MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Your original problem stemmed from using an ADODB.Recordset object to

commit
> an INSERT statement. Recordsets are for *retrieving* data, and even then

an
> explicit ADODB.Recordset is only advantageous in certain scenarios.
>
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
>
>
> "James Baker" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > I appreciate all of your feedback. The main answer to most of your

> "WHY?!"
> > questions is politics. I'm dealing with some of the worst code ever
> > written, backed by some of the worst SQL 2000 database design I've ever
> > seen. The head programmer doesn't want anything done differently than

the
> > way he does it, and for the most part, he does it the wrong way.
> >
> > Some of his code is absolutely insane. He once wrote an insert

statement
> > into a page with a typo, "0rder has been reviewed" instead of "Order has
> > been reviewed". Well, instead of going back, fixing the typo and fixing

> any
> > inserts that were related to it, he decided he'd just query based on

0rder
> > instead. So now, half the records have Order, half have 0rder and the
> > confusion is never ending. He never indents his code, everything is

flush
> > left. I've complained about the SA thing since day 1, and he refuses to
> > create another user. Just had to vent.
> >
> > Anyway, I'll try rewriting this with a stored procedure, even though my

DB
> > knowledge is pretty slim other than the "relational" aspect. I'll slip

in
> > the removal of the DSN as well and hope it goes unnoticed.
> >
> > Do you believe this will solve my original problem? I don't know what
> > exactly was causing that.
> >
> > Thanks much,
> > James
> >
> >
> > "Aaron [SQL Server MVP]" <(E-Mail Removed)> wrote in message
> > news:%(E-Mail Removed)...
> > > > BTW, it is a horrible idea to use sa for your applications. Create a

> sql
> > > > login with limited permissions and use that instead of sa.
> > >
> > > Good catch, I was pretty thorough but I missed that one (even though

> that
> > > was the only clue that this was SQL Server).
> > >
> > > --
> > > http://www.aspfaq.com/
> > > (Reverse address to reply.)
> > >
> > >

> >
> >

>
>



 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      08-24-2004
James Baker wrote:
> If the connection is open, can I execute two statements against it
> before I close the connection?


Absolutely. Did you have a problem doing it?

> I'm opening the one recordset I'm
> using with it and I'm also potentially executing the INSERT with it
> as well. Ultimately I rewrote my code (pre-Stored Procedure) as:


Looks good.

>
> Dim cmsRS, cmsSQL
> Dim insSQL
>
> Dim cn
> Set cn = Server.CreateObject("ADODB.Connection")
> cn.Open "Provider=SQLOLEDB;" & _
> "Data Source=XXXXX;" & _
> "Initial Catalog=XXXXX;" & _
> "User ID = XXXXX;Password=XXXXX"
>
> Set cmsRS = Server.CreateObject("ADODB.Recordset")
>
> cmsSQL = "SELECT ClientsFileNumber FROM TblOrder WHERE FileNumber =
> '" & filenumber & "' AND ClientCode = 'CMSNY'"


Can this return more than one record? If so, you can streamline things by
changing it to:
cmsSQL = "SELECT Count(ClientsFileNumber) FROM TblOrder WHERE " & _
"FileNumber = '" & filenumber & "' AND ClientCode = 'CMSNY'"



> cmsRS.Open cmsSQL, cn, , , 1
>


And change this:
> If NOT cmsRS.EOF Then


To:
If cmsRS(0) > 0 then 'if the first field of the recordset contains a value >
0


> insSQL = "INSERT INTO CMS (FileNumber, StatusDate, StatusTime,
> StatusComment) VALUES('" & cmsRS("ClientsFileNumber") & "', '" &
> date() & "', '" & adstime & "', 'Order has entered review process')"
> cn.Execute insSQL,,129
> End If
>
> cmsRS.Close
> Set cmsRS = Nothing
> cn.Close


Don't forget:
Set cn=nothing

Although some will argue against the need to do that given that you've
destroyed all the child objects first ...

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Boolean operation and arithmetic operation Buzz Lightyear C++ 10 08-12-2009 01:27 PM
I/O operation, file operation behaviou raan C++ 2 08-16-2007 07:13 PM
error:Showing a modal dialog box or form when the application is not running in UserInteractive mode is not a valid operation Henry Lee ASP .Net 1 05-03-2006 10:25 PM
Does bit operation always work more efficiently than math operation? david ullua C Programming 13 03-01-2006 11:02 PM
could not perform this operation because the default mail client is not properly installed erikpaulmiller@gmail.com Javascript 4 01-27-2005 09:41 AM



Advertisments