Solved

Urgent: Cannot create new connection because in manual or distributed transaction mode.

Posted on 2002-07-30
6
1,708 Views
Last Modified: 2007-11-27
Urgent: 100 more Points will be added if the problems can be solved.

Error occur when execute the following ASP program:

Cannot create new connection because in manual or distributed transaction mode.


Set cn1 = Server.CreateObject("ADODB.Connection")
    cn1.open ....
cn1.BeginTrans
for i= 1 to x


empDesc=FindIdDescrption(ID,cn1)
response.write empDesc


cn1.execute "insert table1...."
cn1.execute "update table3"

next
cn1.CommitTrans

Function FindIdDescription(tmpCode,tmpcn1)
  Dim rstemp

    set rstemp = server.CreateObject("adodb.recordset")
    sqlstm ="select * from table2 "
    rstemp.open sqlstm ,tmpcn1
    If not rstemp.eof then
       FindDescription = rstemp("Desc")
    Else
       FindDescription = "N"
    End If
    rstemp.close
    set rstemp=nothing

End Function

How to solve this problem?
0
Comment
Question by:leo_wh_cheung
6 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7188002
Urgent:  Please maintain your open questions:
Query cannot be updated Date: 11/29/2000 01:35AM PST  
http://www.experts-exchange.com/mssql/Q_12010641.html
ASP command change to  Java Command Date: 06/06/2001 03:09AM PST  
http://www.experts-exchange.com/java/Q_20130580.html
Zip a file in Active Server Pages Date: 01/30/2001 02:01AM PST
http://www.experts-exchange.com/asp/Q_20065389.html
How can I set the page never timeout expired? Date: 11/30/2000 08:06PM PST  
http://www.experts-exchange.com/webdevsoftgen/Q_12037560.html

Thanks,
Anthony

0
 
LVL 32

Expert Comment

by:bhess1
ID: 7188697
I have a feeling that you oversimplified your ASP code here.  There is a known issue with this type of code, but only if there is an open rs with pending actions when you try to open another rs.

This error only occurs if you use the default Firehose cursor.  You can prevent it by setting something other than a read-only, forward-only cursor on the rsTemp open.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7188873
Take a look at this article from MSDN:
PRB: SQLOLEDB Allows Only One Connection in Scope of Transaction
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q272358&

Thank you for maintaining your open questions,
Anthony
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 5

Accepted Solution

by:
CFXPERT earned 250 total points
ID: 7189165
If you have a connection already made make sure you close it and set it to nothing before trying to open a new connection.  Just a thought.  I know I've had problems in the past similiar to this one.
0
 

Author Comment

by:leo_wh_cheung
ID: 7208460
CFXPERT,

I solve this problem by the following method:

1. Define(Dim) a local variable in the function. Otherwise, other function can use this variable and error may occur.

2. Create another connection to update the recordset

3. make sure you close it and set it to nothing before trying to open a new connection.

Thanks!

Leo
0
 
LVL 5

Expert Comment

by:CFXPERT
ID: 7209319
Not a problem.  Glad you got it fixed.

:)
KWK
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
need help in sql 4 65
SQL profiler equivalent in MS-Access 3 42
T-SQL:  I Want "Summary"--Not "Detail" 6 20
Sql Query Datatype 2 16
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

912 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

17 Experts available now in Live!

Get 1:1 Help Now