Solved

Returned value from stored procedure - where is it coming from?

Posted on 2008-06-12
4
181 Views
Last Modified: 2010-04-23
Hi

I'm updating a website and I'm looking at code that I didn't write :)

I see code that inserts data into a table in a database.
It's fairly straightforward.
The table 'news' has a primary key which is an identity - 'news_rid'

In the Visual Basic.Net website I see this line.

_newsid = Convert.ToInt32(SqlHelper.ExecuteScalar(System.Configuration.ConfigurationManager.AppSettings("ConnectionString"), "usp_News_Add", contentid, _homepageflag, UserName))
               
I assumed that this inserted a row and assigned the newly created 'news_rid' into _newsid

Is it right to think that?

If so how does the stored procedure return the news_rid?
I don't see an output parameter in the stored procedure (below)

Thanks!
ALTER PROCEDURE [dbo].[usp_News_Add]
(
	@contentid int,
	@homepageflag bit,
	@updatedby nvarchar(55)
)
AS
 
	DECLARE @Error int
	DECLARE @lineID int
	DECLARE @ptrval binary(16)	
 
	declare @updatedid int
	select @updatedid= users_rid from users where users_login = @updatedby
	SET @Error = @@ERROR
 
            IF @Error != 0 GOTO ERROR_HANDLER
 
             BEGIN TRANSACTION 
 
	Insert INTO news
		(
		content_rid,
		news_homepage,
		UpdatedBy,
		deleted_flag
	)
	Values(
		@contentid,
		@homepageflag,
		@updatedid,
		0
	)
	
            COMMIT TRANSACTION           
 
SELECT @@identity AS ID  
 
ERROR_HANDLER:
 
            IF @@TRANCOUNT != 0 ROLLBACK TRANSACTION
 
            RETURN @Error

Open in new window

0
Comment
Question by:hmcgeehan
  • 2
  • 2
4 Comments
 

Author Comment

by:hmcgeehan
ID: 21767113
Just to help I did this


                HttpContext.Current.Response.Write("[" & _newsid & "]<br>")
                _newsid = Convert.ToInt32(SqlHelper.ExecuteScalar(System.Configuration.ConfigurationManager.AppSettings("ConnectionString"), "usp_News_Add", contentid, _homepageflag, UserName))
                HttpContext.Current.Response.Write("[" & _newsid & "]<br>")
                HttpContext.Current.Response.End()

and got this ..

[0]
[9]

so it is assigning the value I just don't understand how :)
0
 
LVL 12

Expert Comment

by:renjurdevan
ID: 21767137
you will get value as table which contains last inserted row's ID

0
 
LVL 12

Accepted Solution

by:
renjurdevan earned 25 total points
ID: 21767144
ExecuteScalar method will return @@identity value! ie, last inserted row's identity value!
0
 

Author Closing Comment

by:hmcgeehan
ID: 31466462
Easy when you know how!

Thanks so much!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

777 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