Best way to do this

[Tem]

When a user submits a photo on my site, a confirmation page is shown.
Your photo has been submitted.
I want to generate a link to the photo he just posted on the confirmation
page.

what's the best way to do this?

right now there are two queries on the page

- insert photo
- select where user=@user order by date desc

they seem redundant

does sql return the value/id of the newly created row somewhere?

Tem

 

[Tibor Karaszi]

does sql return the value/id of the newly created row somewhere?

Do the table have an identity column and you by "value/id" mean the generated value for this column?
If so, then check out the SCOPE_IDENTITY() function.

 

[Uri Dimant]

Tem
Lookup SCOPE_IDENTITY() in the BOL

 

[Tem]

whats bol?

Tem
Lookup SCOPE_IDENTITY() in the BOL

 

[Tem]

yes it does.

how would I access it with asp.net?

 

[Tem]

If I do

insert.....; select identity_scope();

how do I read the value from the 2nd sql statement with asp.net?

 

[Uri Dimant]

BOL -Books OnLine feature that shipps with SQL Server


create table #t(c int not null identity(1,1))
insert into #t default values
select scope_identity()

 

[Tibor Karaszi]

It depends on how you use ASP.NET and ADO.NET to communicate with SQL Server. If you follow best
practices and use stored procedures, then use an output parameter for your procedure for this. Else,
just do below after the INSERT and read the returned value as a resultset.