• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 875
  • Last Modified:

return type from ExecuteNonQuery other than integer

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!


-brokeMyLegBiking.
0
brokeMyLegBiking
Asked:
brokeMyLegBiking
2 Solutions
 
YZlatCommented:

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

try

 Dim iRet As Integer = cmd.ExecuteNonQuery()

catch ex as exception

.....

ExecuteNonQuery does not return anything but the number of records affected
0
 
brokeMyLegBikingAuthor Commented:
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?


thx,


0
 
MageDribbleCommented:
YZlat is correct.  Only integer or Exception will be returned by ExecuteNonQuery.
0
 
wtconwayCommented:
I recommend using a stored procedure that returns custom messages. I do it the lazy way with SELECTS in my store procedure.

CREATE PROCEDURE dbo.spTemp
(@param AS VARCHAR(20)
)

SET NOCOUNT ON
DECLARE @ret AS VARCHAR(8000)

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

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.
0
 
brokeMyLegBikingAuthor Commented:
ok, interesting option.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now