INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Should I Be Using ExecuteNonQuery Like This

Should I Be Using ExecuteNonQuery Like This

(OP)
VB.Net project. I have the following stored procedure in SQL. It works fine but I am executing it with

CODE

' Run Stored Procedure
      ...
myCommand.ExecuteNonQuery()
      myErrorVal = CInt(myCommand.Parameters("@myError").Value)
      NextInvoiceNbr = CInt(myCommand.Parameters("@NextInvoiceNbr").Value) 
Should I beusing something besides ExecuteNonQuery()? I want to return the next invoice number, but I also want to check the error code to confirm the update worked.

CODE

ALTER PROCEDURE [dbo].[Usp_CompanyInvoiceNumber_Select_And_Update]
	@CompanyMasterFK int
	,@NextInvoiceNbr int output
	,@MyError int output
AS
BEGIN
	SET NOCOUNT ON;

	SELECT @NextInvoiceNbr = (SELECT NextInvoiceNbr From dbo.CompanyInvoiceNumber Where CompanyMasterFK = @CompanyMasterFK)

	SELECT @MyError = @@ERROR

	IF @MyError = 0
		BEGIN
			UPDATE dbo.CompanyInvoiceNumber
				Set NextInvoiceNbr = NextInvoiceNbr + 1
				,EditCount = EditCount + 1

				WHERE CompanyMasterFK = @CompanyMasterFK AND NextInvoiceNbr = @NextInvoiceNbr

			SELECT @MyError = @@ERROR
		END
END 

Auguy
Sylvania/Toledo Ohio

RE: Should I Be Using ExecuteNonQuery Like This

Wrong forum, but to answer your question you can indeed use ExecuteNonQuery to return values from the store procedure but you need to set the direction of your return parameters to returnParameter.Direction = ParameterDirection.ReturnValue.

Alternatively you can use ExecuteReader which is used for getting the query results as a dataReader object.

RE: Should I Be Using ExecuteNonQuery Like This

To quote MSDN: Executes a Transact-SQL statement against the connection and returns the number of rows affected.

So the direct return value of ExecuteNonQuery always is number of affected rows (just as side note: if you read the help topic about it, you learn this also includes rows only indirectly affected by triggers)

Typical usage therrefore is with non SELECT T-SQL, like Updates and Inserts. AS your stored proc defines @NextInvoiceNbr int output, you have an in/out parameter and the changes of @NextInvoiceNbr done within the code should refelct to myCommand.Parameters("@NextInvoiceNbr").Value, which works, correct?

You also get your error back this way, if you wnt a more natural channel for error exceptions, you would either do nothing and let the error propagate back to the client side or you would implement TRY/CATCH in T-SQL and use RAISEERROR to raise your own user defined error.

Bye, Olaf.

RE: Should I Be Using ExecuteNonQuery Like This

(OP)
Thanks to both of you for clarification. I do set the direction of the parameters, for some reason I didn't copy that code to the post.

Auguy
Sylvania/Toledo Ohio

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close