Exception occurred with closing the recordset1 ?

See more:

VB

SQL

I have a VB.Net application which was running perfect for like 20 days as a Windows service 24/7 (10 minute periodical times). All of the sudden it started throwing me an error in the log file "Exception Occurred with closing the recordset1 : Operation is not allowed in this context.". Now when I stop the service and starts back the first time it runs without any issue but the next iteration(after 10th minute) it throws me that error.



The log file report as following :
9/23/2021 10:15:28 AM - Wisys Objects Connected to Database!
9/23/2021 10:15:28 AM - DRIVER={SQL Server};Server=STLEDGSQL01;UID=****PWD=*******;DATABASE=100
9/23/2021 10:15:28 AM - Macola Connection State : 1
9/23/2021 10:15:28 AM - Inside the try, cnADOMacola connection closed!
9/23/2021 10:15:28 AM - Macola Connection opened again!
9/23/2021 10:15:28 AM - Exception Occured with closing the recordset1 : Operation is not allowed in this context.
9/23/2021 10:15:28 AM - Exception Occured with opening the recordset to get the company name via cnADOMacola : Operation is not allowed when the object is open.
9/23/2021 10:15:28 AM - Exception Occured with opening the connection ADOEDGE_DSN : Item cannot be found in the collection corresponding to the requested name or ordinal.

What I have tried:

Sub SFCActivityTransactions()
Do
lngST = lngST + 1

Dim p_sRtnErrMsg = ""
If WiSysConn.TestConnectivity(p_sRtnErrMsg) = False Then
If lngST >= 3 Then
strStatus = "Wisys objects failed to connect to database. " & p_sRtnErrMsg
FileIO.WriteLog(strStatus)
Exit Do
End If
Else
strStatus = ""
Exit Do
End If
Loop

strConnectionString = "DRIVER={SQL Server};Server=" & g_sRtnServer & ";UID=" & g_sSQLUser & ";PWD=" & g_sSQLPass & ";DATABASE=" & g_sRtnDatabase

FileIO.WriteLog(strConnectionString)

FileIO.WriteLog("Macola Connection State : " & cnADOMacola.State)

Try
If cnADOMacola.State = 1 Then cnADOMacola.Close()
FileIO.WriteLog("Inside the try, cnADOMacola connection closed!")
Catch ex As Exception
FileIO.WriteLog("Exception occured in closing existing Macola connection : " & ex.Message)
End Try

cnADOMacola.ConnectionString = strConnectionString
FileIO.WriteLog("Macola Connection State : " & cnADOMacola.State)
Try
cnADOMacola.Open(strConnectionString)
lngST = Err.Number
strMsg = Err.Description
FileIO.WriteLog("Macola Connection opened again!")
FileIO.WriteLog("Macola Connection State : " & cnADOMacola.State)
Catch ex As Exception
FileIO.WriteLog("Exception Occured with opening the Macola connection via cnADOMacola : " & ex.Message)
End Try


If lngST <> 0 Then
strStatus = "There was an error trying to connect to the " & g_sRtnDatabase & " database. Error " & lngST & " - " & strMsg & ". Program ends now!"
FileIO.WriteLog(strStatus)
OnStop()
End If

strSelectStatement = "SELECT bedrnm FROM [100].[dbo].[bedryf] WHERE ID = 1"


Try
If rs1 Is Nothing Then
If rs1.State = 1 Then
rs1.Close()
FileIO.WriteLog("Inside the try block after closing the recordset 1")
End If
End If
Catch ex As Exception
FileIO.WriteLog("Exception Occured with closing the recordset1 : " & ex.Message)
End Try

Try
'If rs1 Is Nothing Then
rs1.Open(strSelectStatement, cnADOMacola, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly)
FileIO.WriteLog("Inside the try block after re-opening the recordset 1 with strSelectStatement")
'End If
Catch ex As Exception
FileIO.WriteLog("Exception Occured with opening the recordset to get the company name via cnADOMacola : " & ex.Message)
End Try


If rs1.EOF Then
strStatus = "There was an error finding the company name. Program exits now!"
FileIO.WriteLog(strStatus)
OnStop()
Else
Try
FileIO.WriteLog("Company Name : " & rs1.Fields("bedrnm").Value)
Catch ex As Exception
FileIO.WriteLog("Exception Occured with opening the connection ADOEDGE_DSN : " & ex.Message)
End Try

End If