Recordset failed to open in a ADO connection

poutz · 2013-04-26

Hello,
I am using an ADO connection to execute a stored procedure from my SqlDatabase, into a code unit
I know that the execution of the procedure is doing well, beacause it upadates a table the way it should .
This procedure also gives back data, that i'm trying to catch into a recordset.
My pb is that I have always this message:

ADODB.EOF is not allowed if the object is closed

And I'm sure I've made it open , by the command : ADODB.open !
Do you have any idea of what is happening or how to make it an other way to catch my data anyhow ?
Thanks

yukon · 2013-04-27

Dear poutz,

Is it this what you want? Or you want to get back return value from your SP.

Name	DataType	Subtype	Length
lobjADORecordsetHdr	Automation	'Microsoft ActiveX Data Objects 2.8 Library'.Recordset	
lobjADOConnection	Automation	'Microsoft ActiveX Data Objects 2.8 Library'.Connection
ltxtSQL	Text		1024

IF ISCLEAR(lobjADOConnection) THEN
  CREATE(lobjADOConnection);

lobjADOConnection.ConnectionString := ''; //Your connection string
lobjADOConnection.Open;

IF ISCLEAR(lobjADORecordsetHdr) THEN
  CREATE(lobjADORecordsetHdr);

ltxtSQL := '';
ltxtSQL += ' SELECT * FROM Your_Table';

lobjADORecordsetHdr.ActiveConnection(lobjADOConnection);
lobjADORecordsetHdr.CursorType := 3;             //adOpenStatic
lobjADORecordsetHdr.LockType := 3;               //adLockOptimistic
lobjADORecordsetHdr.Open(ltxtSQL);

IF lobjADORecordsetHdr.State = 1 THEN BEGIN
  WITH lobjADORecordsetHdr DO BEGIN
    IF NOT BOF AND NOT EOF THEN BEGIN
		//Do Something
	END;
  END;
END;
IF NOT ISCLEAR(lobjADORecordsetDtl) THEN BEGIN
  IF lobjADORecordsetDtl.State = 1 THEN
    lobjADORecordsetDtl.Close();
  CLEAR(lobjADORecordsetDtl);
END;
lobjADOConnection.Close();
CLEAR(lobjADOConnection);

Best Regards,
Yukon

poutz · 2013-05-03

Hello,
I would like to have some information back from my SP .

yukon · 2013-05-03

Hi poutz,

Here, I wrote the sample code for you.

More Info.
Stored Procedures: Returning Data : http://www.sqlteam.com/article/stored-procedures-returning-data

Sample
Note: I used "AdventureWorksLT" SQL Database.

OBJECT Form 50000 50000
{
  OBJECT-PROPERTIES
  {
    Date=05/03/13;
    Time=11:30:53 PM;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    Width=8000;
    Height=1650;
  }
  CONTROLS
  {
    { 1000000001;CommandButton;3740;990;1650;550;CaptionML=ENU=Get Max ID;
                                                 OnPush=BEGIN
                                                          intMaxID := fnGetValue('ProductID','Product');//fnGetValue(fieldname,tablename)
                                                        END;
                                                         }
    { 1000000003;TextBox;3740 ;440  ;1700 ;440  ;Editable=No;
                                                 CaptionML=ENU=Product Max ID;
                                                 SourceExpr=intMaxID }
    { 1000000004;Label  ;330  ;440  ;3300 ;440  ;ParentControl=1000000003 }
  }
  CODE
  {
    VAR
      intMaxID@1000000000 : Integer;

    LOCAL PROCEDURE fnGetConnectionString@1000000006() : Text[1024];
    VAR
      txtConnectionString@1000000001 : Text[1024];
    BEGIN
      txtConnectionString := '';
      txtConnectionString += 'Provider=SQLOLEDB.1';
      txtConnectionString += ';Persist Security Info=TRUE';
      txtConnectionString += ';Data Source=.';
      txtConnectionString += ';Initial Catalog=AdventureWorksLT';
      txtConnectionString += ';Integrated Security=SSPI';
      {
        txtConnectionString += ';User ID=' + 'USER_NAME';
        txtConnectionString += ';Password=' + 'PASSWORD';
      }
      txtConnectionString += ';Trusted Connection=TRUE';
      txtConnectionString += ';Encrypt=FALSE';
      EXIT(txtConnectionString);
    END;

    LOCAL PROCEDURE fnGetValue@1000000007(ptxtFldName@1000000003 : Text[50];ptxtTblName@1000000004 : Text[50]) : Integer;
    VAR
      lobjADOCommand@1000000014 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000507-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Command";
      lobjADOParameter@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{0000050B-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Parameter";
      lobjADOConnection@1000000001 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000514-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Connection";
      lobjADORecordset@1000000005 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000535-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Recordset";
      lvarActiveConnection@1000000015 : Variant;
      lintResultID@1000000002 : Integer;
      ltxtSQL@1000000006 : Text[1024];
    BEGIN
      IF ISCLEAR(lobjADOConnection) THEN
        CREATE(lobjADOConnection);

      lobjADOConnection.ConnectionString := fnGetConnectionString();
      lobjADOConnection.Open;

      {====Get Max ID from SQL}
      IF lobjADOConnection.State = 1 THEN BEGIN
        IF ISSERVICETIER THEN BEGIN
          {===Using Recordset}
          ltxtSQL := ' DECLARE @maxID int';
          ltxtSQL += ' EXEC dbo.[sp_GetMaxID] @fldName=' + ptxtFldName + ',';
          ltxtSQL += ' @tableName=' + ptxtTblName + ',';
          ltxtSQL += ' @maxID = @maxID OUTPUT';
          ltxtSQL += ' SELECT @maxID AS MaxID';

          IF ISCLEAR(lobjADORecordset) THEN
            CREATE(lobjADORecordset);
          lobjADORecordset := lobjADOConnection.Execute(ltxtSQL);
          EVALUATE(lintResultID,FORMAT(lobjADORecordset.Fields().Item('MaxID').Value));
        END ELSE BEGIN
          {===Using Command}
          IF ISCLEAR(lobjADOCommand) THEN
            CREATE(lobjADOCommand);

          {===Set Connection For Command}
          lvarActiveConnection := lobjADOConnection;
          lobjADOCommand.ActiveConnection := lvarActiveConnection;

          {===Set Parameter For Command}
          lobjADOCommand.CommandText := 'sp_GetMaxID';
          lobjADOCommand.CommandType := 4;                //adCmdStoredProc
          lobjADOCommand.CommandTimeout := 0;

          //CreateParameter (name,type,direction,size,value)
          //http:www.w3schools.com~ado~met_comm_createparameter.asp
          {===Creating Parameter}
          {===Input Parameter}

          CLEAR(lobjADOParameter);

          lobjADOParameter:=lobjADOCommand.CreateParameter('@fldName',200,1,30,ptxtFldName);
          lobjADOCommand.Parameters.Append(lobjADOParameter);

          lobjADOParameter:=lobjADOCommand.CreateParameter('@tableName',200,1,30,ptxtTblName);
          lobjADOCommand.Parameters.Append(lobjADOParameter);

          lobjADOParameter:=lobjADOCommand.CreateParameter('@query',200,1,3000);
          lobjADOCommand.Parameters.Append(lobjADOParameter);

          {==Output Parameter}
          lobjADOParameter:=lobjADOCommand.CreateParameter('@maxID',3,2);
          lobjADOCommand.Parameters.Append(lobjADOParameter);

          {===Excuting Command}
          lobjADOCommand.Execute;

          {===Get Return Value}
          lintResultID := lobjADOCommand.Parameters.Item('@maxID').Value;
        END;
      END;
      {===Close Connection and Clear All}
      IF NOT ISCLEAR(lobjADORecordset) THEN
        lobjADORecordset.Close();
      IF NOT ISCLEAR(lobjADOConnection) THEN
        lobjADOConnection.Close();
      CLEAR(lobjADORecordset);
      CLEAR(lobjADOConnection);
      CLEAR(lobjADOCommand);
      EXIT(lintResultID);
    END;

    BEGIN
    END.
  }
}

SQL Script

--USE [AdventureWorksLT]
--GO

/****** Object:  StoredProcedure [dbo].[sp_GetMaxID]    Script Date: 05/03/2013 23:32:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Yukon
-- Create date: 08-08-2012
-- Description:	Get Max ID (Integer key)
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetMaxID]
	-- Add the parameters for the stored procedure here
	@fldName  nvarchar(50), 
	@tableName nvarchar(50) , 
	@query nvarchar(3000) = '',
	@maxID int output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;	
	DECLARE @SQL NVARCHAR(1000)
	DECLARE @ParmDefinition NVARCHAR(100)
	
	SET @fldName = REPLACE(@fldName,'(','');
	SET @fldName = REPLACE(@fldName,')','');
	SET @fldName = REPLACE(@fldName,'[','');
	SET @fldName = REPLACE(@fldName,'[','');
	SET @fldName = '[' + @fldName + ']';

	SET @tableName = REPLACE(@tableName,'(','');
	SET @tableName = REPLACE(@tableName,')','');
	SET @tableName = REPLACE(@tableName,'(','');
	SET @tableName = REPLACE(@tableName,')','');
	SET @tableName = REPLACE(@tableName,'[','');
	SET @tableName = REPLACE(@tableName,']','');
		
	SELECT @tableName = '['+TABLE_SCHEMA+'].['+TABLE_NAME+']'
		   FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @tableName
	
	SET @SQL = 'SELECT @MaxID = MAX(' + @fldName + ') FROM ' + @tableName
	IF (@query<>'')
		SET @SQL = @SQL + @query	
		
	SET @ParmDefinition = N'@tableName NVARCHAR(50), @MaxID BigInt OUTPUT'
	EXECUTE sp_executesql @SQL, @ParmDefinition, @MaxID=@maxID OUTPUT, @tableName = @tableName
    -- Insert statements for procedure here
    IF (@maxID IS NULL)
		SET @maxID = 1
    ELSE
		SET @maxID = @maxID + 1
END

GO

Best Regards,
Yukon

bill · 2013-10-31

Your solution helped me very much, thanks a lot!!!!!!!!!!!!!!!!!

Recordset failed to open in a ADO connection

Comments

Categories