Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2006

    Unanswered: ADO does not add to Errors collection after the second FETCH NEXT in a SP

    I'm having trouble obtaining errors raised in a stored
    procedure via the ADO Errors collection after the second
    FETCH NEXT statement from within that stored procedure.

    Consider the following table created in a SQL Server

    CREATE TABLE TestTable
    TestInt int

    INSERT TestTable(TestInt) values(1)
    INSERT TestTable(TestInt) values(2)
    INSERT TestTable(TestInt) values(3)

    This is a very simple table with one column, and three
    rows containing the values 1, 2 and 3.

    Consider this stored procedure:
    set rowcount 0
    Set NoCount ON

    declare @TestInt int
    declare @ErrMsg char(7)
    declare TestCursor cursor forward_only for
    select * from TestTable

    open TestCursor
    Fetch next from TestCursor into @TestInt

    While @@fetch_status<>-1
    select @ErrMsg = 'Error ' + convert(char, @testint)
    raiserror(@ErrMsg, 16, 1)
    raiserror(@ErrMsg, 16, 1)
    Fetch next from TestCursor into @TestInt

    Close TestCursor
    DeAllocate TestCursor

    This stored procedure simply defines a cursor on all rows
    in TestTable. For each row fetched from the cursor, the
    error message 'Error n' is raised twice, where n is the
    integer that had just been fetched from the cursor.

    Finally, consider this VB code using ADO to execute the
    above stored procedure. After the stored procedure is
    executed, the code loops through the errors collection,
    and creates a message box for each error in the collection:

    Private Sub Form_Load()
    Dim cn As Connection
    Dim cm As Command
    Dim oErr As Error

    On Error Resume Next

    Set cn = CreateObject("ADODB.Connection")
    cn.Open "Data Source=<Some SQL Server>; Initial
    Catalog=<Some Database Name>; Provider=SQLOLEDB; Persist
    Security Info=False; Integrated Security=SSPI"

    Set cm = CreateObject("ADODB.Command")
    Set cm.ActiveConnection = cn
    cm.CommandType = adCmdStoredProc
    cm.CommandText = "TestStoredProc"

    For Each oErr In cn.Errors
    MsgBox oErr.Description

    End Sub

    When this code is executed, only two message boxes appear
    with the message "Error 1".

    Any help on this matter would be greatly appreciated

  2. #2
    Join Date
    Feb 2006
    Does anybody has any suggestions?

  3. #3
    Join Date
    Feb 2004
    One Flump in One Place
    Hi Ivan

    You can bet your bottom dollar the focus of any replies will be on the use of the cursor rather than the ADO errors collection.

    I know this is just an example - is this curiosity about a quirk you have spotted or a serious problem for you? If the latter, would you mind briefly explaining what your production cursor does as there are limited instances where it is as efficient as a set based solution. It may be that your sproc can be made more effective and your ADO errors issue made irrelevent.
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2006
    Of course, it is a serious problem for me. SP where this mechanism is used is a part of the big accounting system. Thus I can’t change it logic.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts