Error with recordset the object is open

[Valholy]

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.

    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.

[gibra]

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:

    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

[Valholy]

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:

    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?

[si_the_geek]

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:

    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

[gibra]

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:

    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:

.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.

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?

[Valholy]

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:

    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

sorry for the delay was out on a trip, thanks a lot will try and report results.

How you call Fill_Recordset() function?
Of course, the recordset should be a NEW recordset, each time.

This is how Im using it, it creates a new recordset each time I call it.

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

I will try your suggestion about the connection thanks a lot and no, it's not a query error because if I run it using teradata interface it does it without problem