Solved

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

Posted on 2007-04-02
22
629 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
  • 12
  • 5
  • 3
  • +2
22 Comments
 
LVL 42

Expert Comment

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

Expert Comment

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

Expert Comment

by:srafi78
Comment Utility
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
 
LVL 16

Assisted Solution

by:ThinkPaper
ThinkPaper earned 100 total points
Comment Utility
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
Comment Utility
wow everyone got it before i did =)
0
 

Author Comment

by:Gemini532
Comment Utility
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
Comment Utility
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
Comment Utility
is this page being called from different (more than 1) asp pages?
0
 

Author Comment

by:Gemini532
Comment Utility
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
Comment Utility
it's made up of 3 smaller applcations, which are about 12-14 page applications
0
 

Author Comment

by:Gemini532
Comment Utility
It has 2 views and about 10 stored procedures
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Gemini532
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
We tried to use Windows Authentication but apparently it only works for II6
Is this true?
0
 

Author Comment

by:Gemini532
Comment Utility
<<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
Comment Utility
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
Comment Utility
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
Comment Utility
conn.asp is OK
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now