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

    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
    database:


    CREATE TABLE TestTable
    (
    TestInt int
    )
    go


    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:
    CREATE PROCEDURE TestStoredProc
    as
    BEGIN
    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
    Begin
    select @ErrMsg = 'Error ' + convert(char, @testint)
    raiserror(@ErrMsg, 16, 1)
    raiserror(@ErrMsg, 16, 1)
    Fetch next from TestCursor into @TestInt
    end


    Close TestCursor
    DeAllocate TestCursor
    return
    END


    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"
    cm.Execute


    For Each oErr In cn.Errors
    MsgBox oErr.Description
    Next


    End
    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
    Posts
    3
    Does anybody has any suggestions?

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2006
    Posts
    3
    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
  •