Alice
Occasional Contributor
Joined 10 years ago
User Profile
User Widgets
Contributions
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, AliceSolved2.2KViews0likes6Comments