Solved

Operation is not allowed on an object referencing a closed or invalid connection.

Posted on 2007-04-02
22
646 Views
Last Modified: 2012-08-13
Hello Everyone,
I’m getting a connection related error, although I think the actual connection works, because if I remove the second part of the code, I get to see the first page of my application...

'******************CODE from my dbConnection.asp page*********************
<!--#include file="adovbs.asp"-->
<%
'***************************FIRST PART OF THE CODE*******************
dim objConn, strConnStr
set objConn = server.createObject("ADODB.Connection")
strConnStr = "PROVIDER=MSDASQL;DRIVER={SQL Server}; SERVER={serverName}; DATABASE=databaseName;Integrated Security=SSPI"
objConn.ConnectionString = strConnStr

'***************************SECOND PART OF THE CODE*****************
'Create a command object for the stored procedures
dim objCommand
set objCommand = Server.CreateObject("ADODB.Command")
set objCommand.ActiveConnection      = objConn
objCommand.CommandType                  = adCmdStoredProc
%>
‘***********************************************************************

When I add the SECOND PART OF THE CODE I receive an error on line 14
( set objCommand.ActiveConnection      = objConn )      

The error is:
 
ADODB.Command error '800a0e7d'
Operation is not allowed on an object referencing a closed or invalid connection.
/login/include/dbConnection.asp, line 14

The problem is that the SECOND PART OF THE CODE is needed to get Stored Procedures to work (I think, I did not write this code…)

************************************************************************
But if I REMOVE the SECOND PART OF THE CODE, I get to see the first page of the application where it gives me the option of creating my own username and password:  
The screen says:   If you do not have a username, please click here.

When I click the link, however, I get this error:

Microsoft VBScript runtime error '800a01f4'
Variable is undefined: 'objCommand'
/login/UserAdd.asp, line 18
**********************************************************************
I must mention that I DID not have any problems when I was using SQL Authentication.  The problems started ONLY AFTER the db administrator changed from SQL to Windows Authentication...

This was the code I used for SQL Authentication, but now that I changed to Windows Authentication (which is what my company is using from now on), I'm getting this error:

Microsoft OLE DB Service Components error '80040e21'

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

/login/include/dbConnection.asp, line 14

Line 14 in this case is:                     .open

ORIGINAL CODE:

<!--#include file="adovbs.asp"-->
<%
dim strConnStr
strConnStr = "PROVIDER=MSDASQL;DRIVER={SQL Server}; SERVER={SERVER NAME}; DATABASE=DatabaseName;Integrated Security=SSPI"

'SQL AUthentication worked without any problems...
'strConnStr = "PROVIDER=MSDASQL;DRIVER={SQL Server}; SERVER=SERVER NAME; DATABASE=DatabaseName;UID=username;PWD=password;"


' Create a connection to the database
dim objConn
set objConn = server.createObject("ADODB.Connection")
with objConn
      .ConnectionString      = strConnStr
      .CursorLocation            = adUseClient
      .open
end with

' Create a command object for the stored procedures
dim objCommand
set objCommand = Server.CreateObject("ADODB.Command")
set objCommand.ActiveConnection      = objConn
objCommand.CommandType                  = adCmdStoredProc

%>
0
Comment
Question by:Gemini532
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 5
  • 3
  • +2
22 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 18837644
Add this between the two parts:
objConn.Open()
0
 
LVL 32

Expert Comment

by:bhess1
ID: 18837648
You need objConn.Open before assigning the connection to the Command object.
0
 
LVL 8

Expert Comment

by:srafi78
ID: 18837672
Is the connection object a global object? Is it visible to the second part of your code, Where are you opening and closing the connection?
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 16

Assisted Solution

by:ThinkPaper
ThinkPaper earned 100 total points
ID: 18837674
looking at http://support.microsoft.com/kb/164485 ..
The only difference I see is that you set the connectionstring but you didn't open it. Not sure if that would be the reason, but its worth a try

Set cn = Server.CreateObject("ADODB.Connection")
   Set cmd = Server.CreateObject("ADODB.Command")
   cn.Open "data source name", "userid", "password" <---
   Set cmd.ActiveConnection = cn
   cmd.CommandText = "sp_test"
   cmd.CommandType = adCmdStoredProc
0
 
LVL 16

Expert Comment

by:ThinkPaper
ID: 18837679
wow everyone got it before i did =)
0
 

Author Comment

by:Gemini532
ID: 18837770
well, not necessarily because this .asp page is called by other pages, and it's possible that the connection is opened in those pages... hmmm... let me try your suggestion first...
0
 

Author Comment

by:Gemini532
ID: 18837781
I added:
objConn.Open()                '<------------- line 9


and I got this error:

Microsoft VBScript runtime error '800a01a8'

Object required: ''

login/include/dbConnection.asp, line 9
0
 
LVL 16

Assisted Solution

by:ThinkPaper
ThinkPaper earned 100 total points
ID: 18837895
is this page being called from different (more than 1) asp pages?
0
 

Author Comment

by:Gemini532
ID: 18838531
Yes, several pages.. whcih is why its' designed this way... so that it can be called whenver a connection needs to be open thoughout the application.
and this application is HUGE!!!
0
 

Author Comment

by:Gemini532
ID: 18838542
it's made up of 3 smaller applcations, which are about 12-14 page applications
0
 

Author Comment

by:Gemini532
ID: 18838564
It has 2 views and about 10 stored procedures
0
 

Author Comment

by:Gemini532
ID: 18838615
Is not good if this connection is called by every single page of the application in an include statment such as this?  Why would this give me this error: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

<!--#include file="include/dbConnection.asp"-->
0
 

Author Comment

by:Gemini532
ID: 18838726
I found a URL that claims to have the solution, the problem is I don't really understand it:
http://support.microsoft.com/kb/269495
It has many suggestions:
1. Do not use the "Persist Security Info" keyword in your ADO connection string.
I don't think I'm doing that so that part is OK
2. Add the OLEDB_SERVICES registry entry as follows:

Note Only create the OLEDB_SERVICES value in the registry keys of providers that support COM aggregation. The Exchange OLE DB provider does not support COM aggregation, and when you create this values, you receive the following error message:
0x80040E22
Non-NULL controlling IUnknown was specified, and either the requested interface was not IUnknown, or the provider does not support COM aggregation.

Warning If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.1. Start Registry Editor.
2. In the registry under HKEY_CLASSES_ROOT\CLSID, find the CLSID of the OLE DB provider. For example, the following registry key is for the Microsoft OLE DB Provider for SQL Server (SQLOLEDB):
HKEY_CLASSES_ROOT\CLSID\{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}
Click the CLSID, and then on the Edit menu, click Add Value and add the following registry value:
Value Name: OLEDB_SERVICES
Data Type: REG_DWORD
Value: 0xFFFFFFFF

Note To find the CLSID for the provider you are using, search for the provider's ProgID (SQLOLEDB, for example) in the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Classes\. Under the ProgID, there will be a key named CLSID.

'****************************************THIS SECOND OPTION I DO NOT UNDERSTAND!!!   :(  ****************
0
 

Author Comment

by:Gemini532
ID: 18839206
I found out from the DB administrator that the following line is incorrect:
strConnStr = "PROVIDER=MSDASQL;DRIVER={SQL Server}; SERVER={serverName}; DATABASE=databaseName;Integrated Security=SSPI"

This is because it uses my OWN log in, but since this is an applicatioin on the INTERNET where people who log in from their differet computer need to use a specific username and password, which is not mind, this is not correct!
Does this make sense?

If this part is incorrect than the entire program fails
Then it's still a connection issue and the error message is:

Microsoft OLE DB Provider for ODBC Drivers error '80040e4d'
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user SUPERFAKE\fake'.
/login/include/dbConnection.asp, line 18

fake being the username of the login  and SUPERFAKE being the DOMAIN
0
 

Author Comment

by:Gemini532
ID: 18839269
The connection string should be similar to the SQL SERVER Authentication according to my db manager therefore should look like this:

strConnStr = "PROVIDER=MSDASQL;DRIVER={SQL Server}; SERVER=SuperServer; DATABASE=OPICForms; UID=SUPERFAKE\fake;PWD=SuperPassword;"

0
 
LVL 42

Accepted Solution

by:
dqmq earned 400 total points
ID: 18840945
You can use SQL SERVER authentication or Windows integrated security--either will work if you get the setup right.  Usually, which one to use is a site policy.  However, for my 2 cents, I would avoid SQL Server authentication because it's too easy to steal the password by viewing the source page.  Whichever you use, you need to create a matching logon on SQL SERVER and grant appropriate permissions.
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 400 total points
ID: 18845284
If you do need to use SQL Server Authentication, then consider storing the Connection information in a config file so that servername, databasename, userid, and password cannot be ascertained by viewing the script
0
 

Author Comment

by:Gemini532
ID: 18846085
We tried to use Windows Authentication but apparently it only works for II6
Is this true?
0
 

Author Comment

by:Gemini532
ID: 18846096
<<consider storing the Connection information in a config file so that servername, databasename, userid, and password cannot be ascertained by viewing the script

How do I do that?
What's a config file?
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 400 total points
ID: 18846941
Basically, a config file is file that gets distributed with your application and contains various settings and whatnot that are possibly site dependent and/or you don't want to "hard code" on your page. If you are developing your page with Visual Studio, you can open the config file from the explorer window.  Alternatively, you can just drag a connection object onto your form and then step through its configuration wizard which prompts you for all the relevant pieces, constructs the connection string, and then stores it in the config file where it can be referenced like a .NET object.
0
 

Author Comment

by:Gemini532
ID: 18850736
Hi dqmq:  I am not using .NET, just ASP(VB Script)

This probably explains a lot, our plan was to create a virtual file with the wizard in IIS and set the connection and any sensitive information outside the webserver....
But if we set the conn.asp file ouside the webserver, can it be created with IIS?
Is that similar to a config file?
0
 
LVL 42

Expert Comment

by:dqmq
ID: 18852577
conn.asp is OK
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question