Getting a Return Value from a Stored Procedure

StrataFrame Forum

Back

Home
»
.NET Forums
»
General .NET Discussion
»
Getting a Return Value from a Stored Procedure

Getting a Return Value from a Stored Procedure

Author	Message
Terry Bottorff	Terry Bottorff posted 14 Years Ago HOT Topic Details Share Topic
Group: Forum Members Posts: 448, Visits: 12K	I have the following Stored Proc (I am not sure it is correct). I just am trying to see if there are any duplicates in the table. CREATE PROCEDURE [dbo].[sp_CountSRStockforDups] -- Add the parameters for the stored procedure here @myRowCount int = 0 OUTPUT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here select stock, count(stock) as pen from dbo.SRstocks where stock > 0 group by stock having (count(stock)) > 1 select @myRowCount = @@ROWCOUNT return @myRowCount END I know I have 2 records that repeat but with the following code it returns zero(0).? Dim nret As Integer = 1000 Dim cmd As New SqlCommand("sp_CountSRStockforDups") cmd.CommandType = Data.CommandType.StoredProcedure ' OutPut Parameter cmd.Parameters.Add("@myRowCount", Data.SqlDbType.Int).Direction = Data.ParameterDirection.Output nret = CType(cmd.Parameters("@myRowCount").Value, Integer) Please understand I am not sure any of this is correct except the attached picture seems to indicate that my stored proc is working but any help would be appreciated. TIA Attachments countingstk.png (117 views, 6.00 KB)
	Reply Like 1
Ivan George Borges	Ivan George Borges posted 14 Years Ago Post Details Share Post
Group: StrataFrame MVPs Posts: 1.9K, Visits: 21K	Hi Terry. Haven't you forgotten to execute the query? Guess your code is in a BO Data Retrieval Method, so right after the line where you Add your parameter, put the following code: Me.ExecuteNonQuery(cmd) And then just return your output parameter value the way you are doing and you should be fine.
	Reply Like 1
Terry Bottorff	Terry Bottorff posted 14 Years Ago Post Details Share Post
Group: Forum Members Posts: 448, Visits: 12K	Actually I am running the code in the click of a button and so when I put me.ExecuteNonQuery(cmd) I get an error that says ExecuteNonQuery is not a member of Rodeo.CheckforDupsfrm I know that it probably just in not the correct syntax because it is in the click of a button but I tried a couple of things and they did not work either.?
	Reply Like 1
Terry Bottorff	Terry Bottorff posted 14 Years Ago Post Details Share Post
Group: Forum Members Posts: 448, Visits: 12K	I think I found it. MicroFour.StrataFrame.Data.DataBasics.DataSources("").ExecuteNonQuery(cmd) Thanks for the help.
	Reply Like 1
Ivan George Borges	Ivan George Borges posted 14 Years Ago Post Details Share Post
Group: StrataFrame MVPs Posts: 1.9K, Visits: 21K	You're welcome.
	Reply Like 1
Edhy Rijo	E Edhy Rijo posted 14 Years Ago Post Details Share Post
E Group: StrataFrame Users Posts: 2.4K, Visits: 23K	Hi Terry, Your approach is correct but I prefer to have all data validation code at the business level so it can be use from other places if you need to. So what I do is create a sub or function in the BO that will return the desired value, in your case you want to know if a duplicate record exist, so I would do this: ''' <summary> ''' This a generic function to find duplicate records in tables where the Primary Key is an integer ''' </summary> ''' <returns>True if duplicate records are found.</returns> ''' <remarks>This code will only work for Integer PK, if using other type, simply change the CType in the return statement.</remarks> Public Function DuplicateRecordFound() As Boolean '-- Create the SqlCommand with an Using to automatically dispose it. Using cmd As New SqlCommand cmd.CommandType = CommandType.Text cmd.CommandText = String.Format("SELECT COUNT({0}) FROM {1} WHERE {0} > 0 GROUP BY {0} HAVING COUNT(1) > 0", Me.PrimaryKeyField, Me.TableNameAndSchema) '-- Execute the query and return the value Return CType(Me.ExecuteScalar(cmd), Integer) > 0 End Using End Function Then if you need to use it from any code where you don't have an instance of the BO, you can do this: Using bo As New bizTransactionItems If bo.DuplicateRecordFound = True Then ' Do whatever you want here. End If End Using So if you are using a base BO, simply copy above function and it will work with any BO with an Integer Primary Key. Also noticed the use of "Using bo" in the sample calling code to properly dispose the bo instance and by the way, in this case, no need to create a Stored Procedure because the code is pretty simply and would not make any difference in speed or connection, and remember the idea is to have it generic so it can be used in any BO. Have fun!!! Edhy Rijo
	Reply Like 1
Trent Taylor	Trent Taylor posted 14 Years Ago Post Details Share Post
Group: StrataFrame Developers Posts: 6.6K, Visits: 6.9K	Terry, I agree with Edhy. It is never good Mojo to put your query logic in a button. You are better of encapsulating your query logic in the BO or a Singleton class you use to execute queries in this manner. By embedding your code in the button click and the logic of your application, you break encapsulation which is far more prone to downstream bugs.
	Reply Like 1
Terry Bottorff	Terry Bottorff posted 14 Years Ago Post Details Share Post
Group: Forum Members Posts: 448, Visits: 12K	Oh if my learning curve was not straight up enough. Now it has a curve backwards. Wow Thanks> But on the statement: Using bo As New bizTransactionItems I get the following error: Type bizTransactionItems is not defined. I did replace bo with my BO name? I can not seem to find bizTransactionsItems in the Strataframe Help. TIA.
	Reply Like 1
Edhy Rijo	E Edhy Rijo posted 14 Years Ago Post Details Share Post
E Group: StrataFrame Users Posts: 2.4K, Visits: 23K	Hi Terry, Sorry for the confusion, bizTransationItems is one of my BO. I simply paste one of my code as a sample. You must use your own BO class. We are in a constant learning and since my switch from VFP a couple of years ago to StrataFrame and VB.NET you will not believe how many times I have refactored by code based on posting in these forums. Edhy Rijo
	Reply Like 1
Terry Bottorff	Terry Bottorff posted 14 Years Ago Post Details Share Post
Group: Forum Members Posts: 448, Visits: 12K	Thank you so much. That makes sense. Zowie this stuff is cool.
	Reply Like 1