MSSQL databases cannot create new connections manually

I believe that when you use the transaction rollback method to operate multiple table records in the MSSQL database, the error message "cannot create a new connection in the manual or distributed transaction mode" appears frequently, this problem has plagued me for many years.

This time, when developing a large business platform, it involves data computing and must also be updated (or deleted) Multiple tables ). GOOGLE does not find a solution. I have collected an MSDN instruction and an official promptYes. Only SQL statements can be used to perform database operations..

According to the official prompts, this error message is displayed when the Select statement is involved in event processing. It should also be associated with the record pointer (Cursors. Try this way to process the Conn in the original transaction. execute ("select... from... ") to use rs. open... command to open the record set and solve the problem.

　　

Reference content is as follows: MSDN description: Tips for Working with Cursors Some providers, such as SQL Server, implement a forward-scrolling, read-only (or 'firehose') cursor mode, meaning that they can efficiently retrieve data by keeping a connection open. when working with such providers, the connection cocould be blocked by another user's transaction. the following examples demonstrate scenarios That result in errors. DbConn. Open "DSN = SQLForum; UID = sa; PWD = ;" 'Example 1 DbConn. BeginTrans RS. Open "Select * FROM Message", dbConn Set dbCmd. ActiveConnection = dbConn Example 1: The problem is that the command object's ActiveConnection is being set to a connection that is forward-scrolling and in 'firehose' mode. this is the same connection involved in the batch mode. the error from the provider will only appear in the Err object, and it will return as unspecified. for example, with the ODBC Provider, you will get "Unspecified error ". DbConn. Open "DSN = SQLForum; UID = sa; PWD = ;" 'Example 2 RS. Open "Select * FROM Message", dbConn DbConn. BeginTrans Example 2: The problem here is that the connection is forward-scrolling and in firehose mode, so it cannot be put into transaction mode. the error returned in the Errors collection from the provider will indicate that it is operating in firehose mode, and can't work in transaction mode. for example, with the ODBC Provider against Microsoft SQL Server, you will get the error "Cannot start transaction while in firehose mode ". DbConn. Open "DSN = SQLForum; UID = sa; PWD = ;" 'Example 3 RS. Open "Select * FROM Message", dbConn Set dbCmd. ActiveConnection = dbConn DbConn. BeginTrans Example 3: The problem here is that the connection is in forward-scrolling firehose mode, so it cannot also be involved in a batch mode. the error returned in the Errors collection from the provider will indicate that the transaction cocould not be started. for example, with the ODBC Provider against Microsoft SQL Server, you will get the error "Cannot start transaction because more than one hdbc is in use ".