none
There is already an open DataReader associated with this Command which must be closed first RRS feed

  • Question

  • What can I do to correct the InvalidOperation Exception in red below:

     

    command.CommandText = ("SELECT top 1  * FROM Table")
    DataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
    Dim schemaTable As DataTable = dataReader.GetSchemaTable()
    Dim datatableReturnValue As New DataTable(strTable)
    Dim newDataRow As DataRow = datatableReturnValue.NewRow()
    For i As Integer = 0 To dataReader.FieldCount - 1
    Dim newDataColumn As New DataColumn(dataReader.GetName(i), dataReader.GetFieldType(i))
      datatableReturnValue.Columns.Add(newDataColumn)
    Next
    datatableReturnValue.Rows.Add(newDataRow)
     

    Dim dr As DataRow = SaveFields(datatableReturnValue.Rows(0))   

     

    command.CommandText = "INSERT INTO Table VALUES(" + dr.Item("Name") + ")" 

     

    command.ExecuteNonQuery()   <-- There is already an open DataReader associated with this Command which must be closed first

     

    ****************************************************

        Public Function SaveFields(ByRef dr As DataRow) As DataRow
            dr("Name") = Me.m_strName
            dr("Description") = Me.m_strDesc
            dr("Enabled") = Me.m_bEnabled
            dr("Internal") = Me.m_bInternal

        End Function

    ****************************************************

     

    Monday, November 12, 2007 5:10 PM

Answers

  • You have a single Command object that's you've used to create a DataReader, and you've called ExecuteReader to execute the command and read its result set.  You can't change the CommandText of that command, because the DataReader is still using it.  If you want to execute another command, you have to call the Close() method of the DataReader first, so that it'll release the Command.

     

    When you do this, you'll find that your Command doesn't work any more, because when you created the DataReader you specified that when it gets closed it should also close the connection.  So you should either not specify CommandBehavior.CloseConnection, or you should create a new Command object for your new command.

     

    Also you aren't specifying a column list in your INSERT, so it's going to fail.  And you should be using parameterized SQL instead of formulating commands with string operations, because a) of the risk of SQL injection and b) in the long run, it's easier to maintain.

    Monday, November 12, 2007 6:48 PM

All replies

  •  

    I fixed that error by adding

    1.  the dataReader.close below and

    2,  the command.connection.open before the ExecuteNonQuery at bottom.

     

    command.CommandText = ("SELECT top 1  * FROM Table")
    DataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
    Dim schemaTable As DataTable = dataReader.GetSchemaTable()
    Dim datatableReturnValue As New DataTable(strTable)
    Dim newDataRow As DataRow = datatableReturnValue.NewRow()
    For i As Integer = 0 To dataReader.FieldCount - 1
    Dim newDataColumn As New DataColumn(dataReader.GetName(i), dataReader.GetFieldType(i))
      datatableReturnValue.Columns.Add(newDataColumn)
    Next

    dataReader.Close()

    datatableReturnValue.Rows.Add(newDataRow)
     

    Dim dr As DataRow = SaveFields(datatableReturnValue.Rows(0))   

     

    command.CommandText = "INSERT INTO table(Name) VALUES(" + "'" + dr.Item("Name").ToString + "'" + ")"

    command.Connection.Open()

    command.ExecuteNonQuery()

    Monday, November 12, 2007 6:43 PM
  • You have a single Command object that's you've used to create a DataReader, and you've called ExecuteReader to execute the command and read its result set.  You can't change the CommandText of that command, because the DataReader is still using it.  If you want to execute another command, you have to call the Close() method of the DataReader first, so that it'll release the Command.

     

    When you do this, you'll find that your Command doesn't work any more, because when you created the DataReader you specified that when it gets closed it should also close the connection.  So you should either not specify CommandBehavior.CloseConnection, or you should create a new Command object for your new command.

     

    Also you aren't specifying a column list in your INSERT, so it's going to fail.  And you should be using parameterized SQL instead of formulating commands with string operations, because a) of the risk of SQL injection and b) in the long run, it's easier to maintain.

    Monday, November 12, 2007 6:48 PM
  • Thank you so much Robert. I will refresh myself on the parameterized SQL. Thanks!

    Sorry I edited my question above by inserting the datareader close and then the command.connection.open.

    The insert, even though it needs to be cleaned up, does work.

    And to fix the error below in red I had to make the following changes in orange.

     

        Dim sqlTran As SqlClient.SqlTransaction = conn.BeginTransaction()

        Dim command As SqlClient.SqlCommand = conn.CreateCommand()
        command.Transaction = sqlTran

        SaveObject(obj, m_strTable, conn, command)

        sqlTran.Commit()  <-- This SqlTransaction has completed; it is no longer usable.

     

    Public Function SaveObject(ByRef Obj As Object, ByRef strTable As String, ByRef conn As System.Data.SqlClient.SqlConnection, ByVal command As SqlClient.SqlCommand) As Object

        Dim dataReader As SqlClient.SqlDataReader
          
        command.CommandText = ("SELECT top 1  * FROM cli_SetupAllergies")
        DataReader = command.ExecuteReader(CommandBehavior.default)
        Dim schemaTable As DataTable = dataReader.GetSchemaTable()
        Dim datatableReturnValue As New DataTable(strTable)
        Dim newDataRow As DataRow = datatableReturnValue.NewRow()


        For i As Integer = 0 To dataReader.FieldCount - 1
           Dim newDataColumn As New DataColumn(dataReader.GetName(i), dataReader.GetFieldType(i))
           datatableReturnValue.Columns.Add(newDataColumn)
        Next
        dataReader.Close()


        datatableReturnValue.Rows.Add(newDataRow)

        Dim dr As DataRow = Obj.SaveFields(datatableReturnValue.Rows(0))

        command.CommandText = "INSERT INTO table(Name) VALUES(" + "'" + dr.Item("Name").ToString + "'" + ")"
        command.Connection.Open()   <-- because I changed CommandBehavior above from Close Connection to Default, this line needs to be deleted.
        command.ExecuteNonQuery()

     End Function

     

    Monday, November 12, 2007 7:04 PM