PRB: Recordset DTC Error with Empty Recordset (301437)

SYMPTOMS

When you use the Visual InterDev 6.0 Recordset Design-Time Control (DTC), if you view the Active Server Pages (ASP) page in a Web browser, you may receive one the following error messages:

ADODB.Recordset error '800a0e78'
The operation requested by the application is not allowed if the object is closed.
/Project20/_ScriptLibrary/Recordset.ASP, line xxx

-or-

ADODB.Recordset (0x800a0e78)
Operation is not allowed when the object is closed.
/<Web name>/_ScriptLibrary/Recordset.ASP, line 762

Note that the line number reference in the preceding error may differ according to different versions of Script Library.

RESOLUTION

There are two possible workarounds to this behavior:

Manually code the necessary ActiveX Data Objects (ADO) code to run the SQL statement. This allows a SQL statement that may return an empty recordset to be executed, such as a SQL UPDATE, INSERT, or DELETE statement. For example, the following code adds a value to a field in the specified database:
```
<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=<sqlservername>;" & _
           "UID=sa;PWD=;DATABASE=<database>"
oConn.Execute("INSERT INTO <tablename>(<fieldname>) VALUES (<data>)")
%>
						
```
Note that you must modify this code so that it reflects the correct information for the specific server.
Use a Data Command to run the SQL statement. For example:
1. Add a Data Connection to a Visual InterDev project, and point it to a database.
2. From the Project menu, click Add Data Command.
3. On the General tab, click SQL Statement, and add the following SQL statement:
```
INSERT INTO tblTest (fldTest) VALUES (101);
							
```
  Note that you may need to modify the preceding statement to conform to the table and field names of your database. Click OK after you add the SQL statement.
4. Add a new ASP page to your project.
5. Add the following code to the ASP page:
```
<%
Set DE = Server.CreateObject("DERuntime.DERuntime")
DE.Init(Application("DE"))
DE.Command1
%>
						
```
6. From the View menu, click View In Browser. If you are prompted to save the ASP file, click OK. The information will be added to the database.

MORE INFORMATION

Steps to Reproduce Behavior

Add a Data Connection to a Visual InterDev project, and point it to a database.
Add a new ASP page to your project.
Add a Recordset DTC to the ASP page. Make sure that the Connection property is set to the Data Connection that you created in the first step.
Right-click the Recordset DTC, and then click Properties.
On the General tab, click SQL Statement, and add the following SQL statement:
```
INSERT INTO tblTest (fldTest) VALUES (101);
						
```
Note that you may need to modify the preceding statement to conform to the table and field names of your database. Click Close after you add the SQL statement.
From the View menu, click View In Browser. If you are prompted to save the ASP file, click OK. You receive one of the above-mentioned error messages.

REFERENCES

For additional information on creating database connections, click the article number below to view the article in the Microsoft Knowledge Base:

300382 HOW TO: Create a Database Connection from an ASP Page in IIS

For additional information on similar problems while using stored procedures, click the article number below to view the article in the Microsoft Knowledge Base:

190762 PRB: Cannot Access a Stored Procedure's Return Value from DTC

For more information on Data Commands, see the following MSDN article:

Executing Database Commands Using the Data Environment
http://msdn.microsoft.com/library/en-us/vidref98/html/vihowexecutingdatabasecommands.asp