Dataenvironment and ADODB

[aalmeida]

I'm working on this program on VB 6.0 enterprise Edition and I'm having the following issue. The second time I tri to run the procedure it returnes an run time error 3709 stating the the connection can not be use because is either out of context or closed.
Any sugestion will be appreciated.

Here is the Code:

Private Sub TxtFllr(uFilter As Boolean, iFilter As Boolean)


Dim iCount As Integer
Dim iSql As String
Dim InspecId As String

iSql = "select Inspection_ID_Num, " & _
"Inspection_Complete, " & _
"User_ID_Num, " & _
"Inspection_Date_Dtm, " & _
"Remarks " & _
"from qry_inspections"


If InspecId <> &quot;&quot; Then
iSql = iSql & &quot; where Inspection_ID_Num = '&quot; & InspecId & &quot;'&quot;
If uFilter = True Then
iSql = iSql & &quot; and User_ID_Num = &quot; & gUsrId
End If
If SortCheck1.Value = 1 Then
iSql = iSql & &quot;and Inspection_Complete = No&quot;
End If
Else
If SortCheck1.Value = 1 Then
iSql = iSql & &quot; where Inspection_Complete = No&quot;
If uFilter = True Then
iSql = iSql & &quot; and User_ID_Num = &quot; & gUsrId
End If
Else
If uFilter = True Then
iSql = iSql & &quot; where User_ID_Num = &quot; & gUsrId
End If
End If
End If

On Error GoTo GetConn_Fail

'Declares and opens Connection to Db
Dim Conn1 As New ADODB.Connection
Set Conn1 = gSession.iADSysDataConnect
If Conn1 Is Nothing Then Exit Sub
If Conn1.State = adStateClosed Then
Conn1.Open
Else
Conn1.Close
Conn1.Open
End If
' Conn1.Open

'Declares and populates Recordset of data
Dim iAdRs1 As New ADODB.Recordset
Dim iItem As Integer
Set iAdRs1 = gSession.rsqry_inspection
If iAdRs1 Is Nothing Then Exit Sub
If iAdRs1.State = adStateClosed Then
iAdRs1.Open iSql, , , adLockReadOnly
Else
iAdRs1.Close
iAdRs1.Open iSql, , , adLockReadOnly
End If
' iAdRs1.Open iSql, , , adLockReadOnly
iCount = iAdRs1.RecordCount

iItem = 0
If iCount > 0 Then
If Not iAdRs1.EOF Then
If iAdRs1.BOF Then
While Not iAdRs1.EOF
AFDataList1.AddItem (iAdRs1(&quot;Inspection_ID_Num&quot & _
DisplaySpaces(3) & _
DisplayBol(iAdRs1(&quot;Inspection_Complete&quot) & _
DisplaySpaces(3) & _
iAdRs1(&quot;User_ID_Num&quot & _
DisplaySpaces(3) & _
FormatDateTime(iAdRs1(&quot;Inspection_Date_Dtm&quot, vbShortDate) & _
DisplaySpaces(11 - Len(FormatDateTime(iAdRs1(&quot;Inspection_Date_Dtm&quot, vbShortDate))) & _
FormatDateTime(iAdRs1(&quot;Inspection_Date_Dtm&quot, vbShortTime)), iItem

iItem = iItem + 1
Wend
Else
iAdRs1.MoveFirst
While Not iAdRs1.EOF
AFDataList1.AddItem (iAdRs1(&quot;Inspection_ID_Num&quot & _
DisplaySpaces(3) & _
DisplayBol(iAdRs1(&quot;Inspection_Complete&quot) & _
DisplaySpaces(3) & _
iAdRs1(&quot;User_ID_Num&quot & _
DisplaySpaces(3) & _
FormatDateTime(iAdRs1(&quot;Inspection_Date_Dtm&quot, vbShortDate) & _
DisplaySpaces(11 - Len(FormatDateTime(iAdRs1(&quot;Inspection_Date_Dtm&quot, vbShortDate))) & _
FormatDateTime(iAdRs1(&quot;Inspection_Date_Dtm&quot, vbShortTime)), iItem
iAdRs1.MoveNext
iItem = iItem + 1
Wend
End If
Else
iAdRs1.MoveFirst
While Not iAdRs1.EOF
AFDataList1.AddItem (iAdRs1(&quot;Inspection_ID_Num&quot & _
DisplaySpaces(3) & _
DisplayBol(iAdRs1(&quot;Inspection_Complete&quot) & _
DisplaySpaces(3) & _
iAdRs1(&quot;User_ID_Num&quot & _
DisplaySpaces(3) & _
FormatDateTime(iAdRs1(&quot;Inspection_Date_Dtm&quot, vbShortDate) & _
DisplaySpaces(11 - Len(FormatDateTime(iAdRs1(&quot;Inspection_Date_Dtm&quot, vbShortDate))) & _
FormatDateTime(iAdRs1(&quot;Inspection_Date_Dtm&quot, vbShortTime)), iItem
iItem = iItem + 1
Wend
End If
End If

iAdRs1.Close
Set iAdRs1 = Nothing
Conn1.Close
Set Conn1 = Nothing

Exit Sub

GetConn_Fail:

MsgBox Err.Description & &quot; in iAdRs1&quot;, vbCritical, Err.Source

End Sub


AL Almeida
NT/DB Admin
&quot;May all those that come behind us, find us faithfull&quot;

 

[aata]

set the &quot;On Error GoTo GetConn_Fail&quot; line as a comment and see which line exaclty is creating the error.

after you close connection as conn1.close, as far as i know, there is no need to set conn1 as nothing. is your code working fine the first time it's run?

 

[aalmeida]

Yes the code run just fin at the first time but fail here at the second time:

'Declares and populates Recordset of data
Dim iAdRs1 As New ADODB.Recordset
Dim iItem As Integer
Set iAdRs1 = gSession.rsqry_inspection
If iAdRs1 Is Nothing Then Exit Sub
iAdRs1.Open iSql, , , adLockReadOnly ' This is the line that fails at the second run

AL Almeida
NT/DB Admin
&quot;May all those that come behind us, find us faithfull&quot;

 

[aalmeida]

Thanks but I figured the following code will work every time:
'Declares and populates Recordset of data
Dim iAdRs1 As New ADODB.Recordset
Dim iItem As Integer
Set iAdRs1 = gSession.execute(iSql)
If iAdRs1.state = adStateclosed then
iAdRs1.Open
Else
iAdRs1.requery
end

AL Almeida
NT/DB Admin
&quot;May all those that come behind us, find us faithfull&quot;