dcsimg
Results 1 to 5 of 5
  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2015
    Posts
    35

    Error with recordset the object is open

    Hi everyone, I'm having trouble with a recordset that returns 840k lines of information, my code works with querys that return less data than that but when trying with this specific one it just gives error "object is open" each time.

    Code:
        Private Sub Connect(Optional User As String = "", Optional Pass As String = "")
            Connection = New ADODB.Connection
            Connection.ConnectionTimeout = 1000
            Connection.CommandTimeout = 86400
            Connection.Open(Connection_String, User, Pass)
        End Sub
    
    Public Function Connect_TERADATA(ByVal DBC As String, ByVal User As String, ByVal Password As String) As Boolean
            Try
                Connection_String = "Driver={Teradata};DBCName=" & DBC & ";UID=" & User & ";AUTHENTICATION=ldap;AUTHENTICATIONPARAMETER="
                Connect(User, Password)
                Return True
            Catch ex As Exception
                Return False
            End Try
        End Function
    
        Public Function Fill_Recordset(ByVal Query As String) As ADODB.Recordset
            Recordset1 = New ADODB.Recordset
            With Recordset1
            .CursorLocation = ADODB.CursorLocationEnum.adUseClient
            .Open(Query, Connection, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockUnspecified)
            End With
            Return Recordset1
        End Function
    Anyone knows how to solve this? it works perfectly on teradata but using code its failing.

  2. #2
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,538

    Re: Error with recordset the object is open

    You have probably declared Recordset1 globally and then when you try to open it the second time you find it already open. You must use a local variable:

    Code:
        Public Function Fill_Recordset(ByVal Query As String) As ADODB.Recordset
            Dim rs As ADODB.Recordset        ' create a local object variable
            Set rs = New ADODB.Recordset     '
    
            'Recordset1 = New ADODB.Recordset
    
            With rs    'Recordset1
                .CursorLocation = ADODB.CursorLocationEnum.adUseClient
                .Open(Query, Connection, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockUnspecified)
            End With
            Return rs    'Recordset1
        End Function

  3. #3

    Thread Starter
    Member
    Join Date
    Oct 2015
    Posts
    35

    Re: Error with recordset the object is open

    Quote Originally Posted by gibra View Post
    You have probably declared Recordset1 globally and then when you try to open it the second time you find it already open. You must use a local variable:

    Code:
        Public Function Fill_Recordset(ByVal Query As String) As ADODB.Recordset
            Dim rs As ADODB.Recordset        ' create a local object variable
            Set rs = New ADODB.Recordset     '
    
            'Recordset1 = New ADODB.Recordset
    
            With rs    'Recordset1
                .CursorLocation = ADODB.CursorLocationEnum.adUseClient
                .Open(Query, Connection, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockUnspecified)
            End With
            Return rs    'Recordset1
        End Function
    Tried with your code, it ain't working, and doubt thats the problem since the recordset is used once and in specific in this part of code that I already shared.

    The error only shows when getting large amounts of data, because with other querys it works, is there some way or another library I could use to get all this data without problem?

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,933

    Re: Error with recordset the object is open

    As you appear to be using VB.Net, you could use ADO.Net (which is what most people would have used), out of the various options it provides it is most likely that a DataReader would be best here.

    eg:
    Code:
        Using connection As New OLEDBConnection("your connection string")
            Using command As New OLEDBCommand("your Select statement", _
                                              connection)
                connection.Open()
         
                Using reader As OLEDBDataReader = command.ExecuteReader()
                    While reader.Read()
                        MessageBox.Show(reader("name of a field"))
                    End While
                End Using
            End Using
        End Using

  5. #5
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,538

    Re: Error with recordset the object is open

    Quote Originally Posted by Valholy View Post
    Tried with your code, it ain't working, and doubt thats the problem since the recordset is used once and in specific in this part of code that I already shared.
    How you call Fill_Recordset() function?
    Of course, the recordset should be a NEW recordset, each time.
    Example:
    Code:
        Dim myRS As ADODB.Recordset
        Set myRS = New ADODB.Recordset
        Set myRS = Fill_Recordset(<your_query_string>)
    To obtain better performances, you should open a recordset in this way:
    Code:
    .Open Query, Connection, adOpenForwardOnly, adLockReadOnly, adCmdText
    Take also note that if you set the CursorLocation Connection as adUseClient, this property is inherited in every recordset, automatically.


    Quote Originally Posted by Valholy View Post
    The error only shows when getting large amounts of data, because with other querys it works, is there some way or another library I could use to get all this data without problem?
    Probably it could be a mistake in the query?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width