[dba-VB] Parameter objects in C# and ADO.

Hi John:

ADO always returns the number of rows affected in MS SQL SP call. It just
has to be retrieved.

Here is the code that I used as a sample/base before.
http://msdn.microsoft.com/en-us/library/aa302325.aspx

Jim


-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, November 27, 2009 6:11 AM
To: Discussion concerning Visual Basic and related programming issues.
Subject: Re: [dba-VB] Parameter objects in C# and ADO.

RowsAffected is not part of the error stuff and I definitely want that info
in most cases.

I haven't done this yet but I don't think that getting back the error code
and description is 
excessive.  If it is possible to easily and unambiguously look up the error
code and get the exact 
description, each and every time, then passing back the description is not
necessary.

This is exactly the kind of thing I will be doing in the future.


John W. Colby
www.ColbyConsulting.com


Shamil Salakhetdinov wrote:
> Hi Gustav at all,
> 
> BTW, have you ever seen the following below error handling in CRUD SPs as
I
> have found in one of my customers' databases?
> Isn't an overkill?
> Or do you use even more detailed/elaborated error handling in your CRUD
SPs?
> How do you organize error handling in your CRUD SPs? (I usually just
return
> error code in RETURN statement).
> 
> CREATE procedure [dbo].[GettblUserByUsername] 
> @Username varchar(20),
> @ErrorDesc varchar(100) output,
> @ErrorNo int output,
> @RowsAffected int output
> AS
> DECLARE @lErrorNo INTEGER	
> DECLARE @lRowsAffected INTEGER	
> Begin
> 	
> 	SELECT tblUser.tblUserId,
> 		tblUser.tblUserGroupId, 
> 		tblUserGroup.groupCode as UserGroup,
> 		tblUser.Username, 
> 		tblUser.tblUserLevelId
> 	FROM tblUser
> 	left outer join tblUserGroup
> 		on tblUser.tblUserGroupId = tblUserGroup.tblUserGroupId
> 	WHERE tblUser.Username = @username
> 	and tblUser.Authorised = 1
> 
> 	select @lErrorNo = @@ERROR, @lRowsAffected = @@ROWCOUNT
> 
> 	IF @lErrorNo > 0
> 	Begin		
> 		set @ErrorDesc = 'error with get of tblUser'
> 		set @ErrorNo = @lErrorNo
> 		set @RowsAffected = 0
> 		RETURN 
> 	End
> 	Else
> 	Begin
> 		set @ErrorDesc = 'Get was Successful for tblUser'
> 		set @ErrorNo = 0
> 		set @RowsAffected = @lRowsAffected
> 		RETURN
> 	End
> end
> 
> Thank you.
> 
> --
> Shamil
> 
> -----Original Message-----
> From: dba-vb-bounces at databaseadvisors.com
> [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
> Sent: Friday, November 27, 2009 1:45 PM
> To: dba-vb at databaseadvisors.com
> Subject: Re: [dba-VB] Parameter objects in C# and ADO.
> 
> Hi Mark et al
> 
> What's the big deal? Are parameters costly? Hardly in money, but in
> resources?
> 
> /gustav
> 
> 
> <<< snip >>>
>  
> 
> __________ Information from ESET NOD32 Antivirus, version of virus
signature
> database 4641 (20091127) __________
> 
> The message was checked by ESET NOD32 Antivirus.
> 
> http://www.esetnod32.ru
>  
> 
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
> 
> 
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com