none
connection is busy with results for other command Source: Microsoft OLE DB Provider for SQL Server RRS feed

  • Question

  • Hi,

    I use the following code in a webservice to fill a SQL 2008 Express database.

                strConnection = "Provider=SQLNCLI10;Data Source=" & (getPath("database")) & ";Initial Catalog= cmdb_prod;User ID=cmdb_prod;Password=cmdb_prod;;MultipleActiveResultSets=True"
                objConnection = New OleDbConnection(strConnection)
                objConnection.Open()
                objAdapter = New OleDbDataAdapter()
                objAdapter.ContinueUpdateOnError = True
                Dim cb As New OleDbCommandBuilder(objAdapter)
                For Me.intI = 0 To ds.Tables.Count - 1
                    If ds.Tables(intI).Rows.Count > 0 Then
                        cb.RefreshSchema()
                        ListBox1.Items.Add(ds.Tables(intI).TableName)
                        objAdapter.SelectCommand = New OleDbCommand("SELECT * FROM [" + ds.Tables(intI).TableName + "] ", objConnection)
                        objAdapter.Fill(ds, ds.Tables(intI).TableName.ToString)
                        objAdapter.UpdateCommand = cb.GetUpdateCommand()
                        'AddHandler objAdapter.RowUpdating, New OleDbRowUpdatingEventHandler(AddressOf OnRowUpdating)
                        AddHandler objAdapter.RowUpdated, New OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated)
                        objAdapter.Update(ds, ds.Tables(intI).TableName.ToString)
                        RemoveHandler objAdapter.RowUpdating, New OleDbRowUpdatingEventHandler(AddressOf OnRowUpdating)
                        RemoveHandler objAdapter.RowUpdated, New OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated)
                        intK = intK + 1
                    End If
                Next
                objConnection.Close()

     

    Sub OnRowUpdating(ByVal sender As Object, ByVal args As OleDbRowUpdatingEventArgs)
            'intialiseren transaction error counter
            If args.StatementType = StatementType.Insert Then
                For Me.intJ = 0 To args.Row.ItemArray.Length - 1
                    transactionlogToFile(("Table: " & args.TableMapping.ToString & "[Inserted] " & DateTime.Now & " " & args.Row(intJ)))
                    intL = intL + 1
                Next
            End If
        End Sub


        '
        Private Sub OnRowUpdated(ByVal sender As Object, ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs)
            'initialiseren tranaction succes counter
            If e.Status = UpdateStatus.ErrorsOccurred Then
                transactionlogToFile(("Error Message: " & e.Errors.Message.ToString() & ", Source: " & e.Errors.Source.ToString()) & vbCrLf)
                e.Status = UpdateStatus.SkipCurrentRow
                intN = intN + 1
            Else
                'transactionlogToFile(vbTab & "Updated" & vbCrLf)
                intM = intM + 1
            End If
        End Sub

    When I use the eventhandler objAdapter.RowUpdating, I get the error like mentioned in the title of my discussion.

    The eventhandler objadapter.RowUpdated is working correct.

    Anyone an idea how this happens?

    Kind regards,

    Jurgen

    Tuesday, June 8, 2010 10:59 AM

Answers

  • Traditionally, an OleDbConnection object can only do one thing at a time.  During RowUpdating, the OleDbConnection is in the process of streaming all of the rows into the DataTable.  You'll need to have a secondary OleDbConnection (using the same ConnectionString as the main OleDbConnection) that you use for the logging.

    As of SQL 2005, you can also activate MARS (Multiple Active Result Sets) to enable multiple simultaneous commands over the same connection.  Add MarsConn=yes to the connection string.

    Thursday, June 10, 2010 12:27 AM
  • I use the.Net Framework data Provider for SQL server now, and it seems that this is working stable without error(s).
    Thursday, June 10, 2010 12:11 PM
  • > I added already MultipleActiveResultSets=True to the connectionstring. Is this the same as MarsConn=Yes?

    Use MultipleActiveResultSets for SqlClient.  Use MarsConn for OLE DB.  Use MARS_Connection for ODBC.

     

     

    Thursday, June 10, 2010 11:15 PM

All replies

  • Traditionally, an OleDbConnection object can only do one thing at a time.  During RowUpdating, the OleDbConnection is in the process of streaming all of the rows into the DataTable.  You'll need to have a secondary OleDbConnection (using the same ConnectionString as the main OleDbConnection) that you use for the logging.

    As of SQL 2005, you can also activate MARS (Multiple Active Result Sets) to enable multiple simultaneous commands over the same connection.  Add MarsConn=yes to the connection string.

    Thursday, June 10, 2010 12:27 AM
  • Hi,

    I added already MultipleActiveResultSets=True to the connectionstring. Is this the same as MarsConn=Yes?

    Kind regards.

     

    Edit: Yes it is the same

    Note: MarsConn=yes equals
    MultipleActiveResultSets=true equals
    MARS_Connection=yes

     

    Thursday, June 10, 2010 9:55 AM
  • Oke, I understand. But how can i spilt the the process of streaming all of the rows into the DataTable, and the RowUpdating process into two different OleDbconnections?

     

     

    Thursday, June 10, 2010 10:35 AM
  • I use the.Net Framework data Provider for SQL server now, and it seems that this is working stable without error(s).
    Thursday, June 10, 2010 12:11 PM
  • > I added already MultipleActiveResultSets=True to the connectionstring. Is this the same as MarsConn=Yes?

    Use MultipleActiveResultSets for SqlClient.  Use MarsConn for OLE DB.  Use MARS_Connection for ODBC.

     

     

    Thursday, June 10, 2010 11:15 PM
  • Yes, it works now without any errors. Thanks for the support !
    Friday, June 11, 2010 12:51 PM