Forum Discussion

Alice's avatar
Alice
Occasional Contributor
10 years ago

ADO RecordSet is not working

 

I got the error when go to MoveFirst : Operation is not allowed when the object is closed.

However , if I use a simple query like "Select * from location" , it's working fine. Below is code.

Anyone has any idea on this? 

 

strQuery = "USE TableA "&_
"SET NOCOUNT ON "&_
"DECLARE @Name varchar(100) = '<%Name%>' "&_
"DECLARE @Alias varchar(100) = '<%Zone Alias%>' "&_
"DECLARE @ID varchar(10) "&_
"SET @ID = (SELECT id FROM sites WHERE name = @Name) "&_
"SELECT uid "&_
"FROM "&_
"location AS loc, "&_
"zone AS zone, "&_
"epc AS epc "&_
"WHERE "&_
"loc.epc_id = epc.epc_id AND "&_
"loc.zone_id = zone.zone_id AND "&_
"loc.is_removed = '0' AND "&_
"zone.alias = @Alias AND "&_
"zone.id = @ID "&_
"ORDER BY "&_
"epc.epc_urn "

' Create a Connection object
strQuery = Replace(strQuery,"<%Name%>",strName)
strQuery = Replace(strQuery,"<%Alias%>",strAlias)
strQuery= "SELECT site_id FROM [VUE_DM].[dbo].[sites] WHERE site_name ='Site1' "
Set AConnection = ADO.CreateADOConnection
' Set rs = ADO.CreateADODataSet
' Specify the connection string
AConnection.ConnectionString = "Provider=SQLOLEDB;DSN=TableA;Uid=sa;Pwd=" & ProjectSuite.Variables.SQLServerPassword & ";"
' Suppress the login dialog box
AConnection.LoginPrompt = False
AConnection.Open

' Remove if already there
Set oRecSet = AConnection.Execute_(strQuery)
'
' Processes data
oRecSet.MoveFirst 

 

 

Thanks,

Alice

  • Alice's avatar
    Alice
    10 years ago

    haha, I think I just remove that USE command!!! Thanks all the same ;-)

    Have a great day.:smileyhappy:

     

    Alice

  • NisHera's avatar
    NisHera
    Valued Contributor
    10 years ago

    Usually this message gets if you try to handle “recset” after object get closed. Are you returning RecSet to another function/procedure and trying to manipulate it at there? If so try doing same within same function.  Also evaluate AConnection.State when you using RecSet, connection state should be not equal to 0(zero)

    Connection should be closed after finishing everything.

    • Alice's avatar
      Alice
      Occasional Contributor
      10 years ago

      The state is 1. And I just verify these codes, recset is not used anywhere else.

       

      Thanks for your help ;-)

  • Ryan_Moran's avatar
    Ryan_Moran
    Valued Contributor
    10 years ago

    It's likely that your statement is not returning any results and therefore the recordset is closed.

    You should be safe to remove oRecSet.MoveFirst as the recordset will already be at the first record when it's first returned.

    You'll then want to check the .EOF (end of file) while iterating the returned recordset.

    Something like so:

     

    While Not oRecSet.EOF
  'do stuff with record
  oRecSet.MoveNext()
Wend

     

    • Alice's avatar
      Alice
      Occasional Contributor
      10 years ago

      Actually I copied the query to run in the sql server, it returns results.

       

      The problem is USE TableA, if I prefix the database to the table , it's working fine. eg. TableA.dbo.location.

       

      Any idea on this issue? 

       

      Thanks