return type from ExecuteNonQuery other than integer

Posted on 2006-05-08
Last Modified: 2008-01-09
Is there a way to get a more complete return type than just an integer when you run the ExecuteNonQuery statement? For example if the query produces warnings, or messages, is there a way to capture these?

        Dim iRet As Integer = cmd.ExecuteNonQuery()

I'd like to get back more than just an integer or SqlException! That would be very helpful!

Question by:brokeMyLegBiking
    LVL 35

    Accepted Solution


    the only thing you can do in this case is use try catch blocks:


     Dim iRet As Integer = cmd.ExecuteNonQuery()

    catch ex as exception


    ExecuteNonQuery does not return anything but the number of records affected
    LVL 4

    Author Comment

    hm, I was afraid of that.

    Is there an alternative to ExecuteNonQuery?

    What about the new Sql Stored procedures? Do they offer more return types than just integer? Does anyone know anything about the return types for SqlServer CLR hosted .NET assemblies?


    LVL 5

    Expert Comment

    YZlat is correct.  Only integer or Exception will be returned by ExecuteNonQuery.
    LVL 7

    Assisted Solution

    I recommend using a stored procedure that returns custom messages. I do it the lazy way with SELECTS in my store procedure.

    (@param AS VARCHAR(20)

    DECLARE @ret AS VARCHAR(8000)

    IF @param = 'hey'
           SET @ret = 'You said hey to me!'
    ELSE IF @param = 'hello'
           SET @ret = 'You said hello to me!'

    SELECT @ret

    that's just a sample of what I do. You would, in this case, return a datatable or if it's just one value, use ExecuteScalar(). But if you had a range of messages that you wanted to display to the user, maybe a list of STATUS messages that pertain to multiple inserts or something, I would use a datatable.
    LVL 4

    Author Comment

    ok, interesting option.

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    Article by: Kraeven
    Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now