Return pkID of last saved record - ASPUpload & @@Identity

[BigJohnson]

We're using ASPUpload as a tool to upload files to our server and save
the details to SQLServer. However, I have an application where I need
to return the pkID of the just saved file. I'm assuming that I could
use the @@Identity command but cannot get this to function.

Has anyone used this command with ASPUpload with an success, or any
other methods that could be used?

Thanks.

[Erland Sommarskog]

BigJohnson (cr***********@westleigh.co.uk) writes:

We're using ASPUpload as a tool to upload files to our server and save
the details to SQLServer. However, I have an application where I need
to return the pkID of the just saved file. I'm assuming that I could
use the @@Identity command but cannot get this to function.

Has anyone used this command with ASPUpload with an success, or any
other methods that could be used?

Since I've never heard of ASPUpload before, and I don't know ASP, I would
suggest that you either post your question to an ASP forum, or post the
relvant code parts - both ASP and SQL Server here.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[manstein]

Hey BigJ

If you know the table name then you can retreive the last identity
value with the function: IDENT_CURRENT('<table name>'). This works
accross all sessions and scopes. The problem with @@IDENTITY var is
that it is global only to the session where the insert occured. This
may not be the same session where you are quering the @@IDENTITY. If
this doesnt work for you, please post more info about how the details
are inserted into SQL server. thanks

best regards.

[--CELKO--]

What you are asking for sounds like a kludge caused by a bad design.

Wouldn't life be much easier if you used a design with real Relational
keys instead of a proprietary exposed physical locator like IDENTITY
to mimic a sequential file structure?

[Tony Rogerson]

The IDENTITY property is not a physical locator, its simply a means to
generate an automatic incrementing number - check the product spec for more
information.

Other methods of creating incrementing numbers involve using the MAX on the
id column which gives a heavy performance degradation because of locking
over using the IDENTITY property.

Check the current ANSI standard and you will see an implementation around
this.

Many people use an IDENTITY property to populate a surrogate key instead of
bloating tables copying your natural key around.

In this posters case, i've used ASPUpload myself, they are saving a file to
the file system and want to record a locator to the file, often i give
uploaded files FL001.PDF for instance as you can't get at the original file
name, also, even if you could its from mulitple users who may well use the
same filename so the natural key as it would be (filename) is useless,
unless you want to force people to have different file names adding
complexity and spoiling the UI experience.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jc*******@earthlink.net> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

What you are asking for sounds like a kludge caused by a bad design.

Wouldn't life be much easier if you used a design with real Relational
keys instead of a proprietary exposed physical locator like IDENTITY
to mimic a sequential file structure?

[Tony Rogerson]

Hi,

Use SCOPE_IDENTITY() to get the last inserted IDENTITY value for that
statement...

insert blha......

select pkID = SCOPE_IDENTITY()

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"BigJohnson" <cr***********@westleigh.co.uk> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

We're using ASPUpload as a tool to upload files to our server and save
the details to SQLServer. However, I have an application where I need
to return the pkID of the just saved file. I'm assuming that I could
use the @@Identity command but cannot get this to function.

Has anyone used this command with ASPUpload with an success, or any
other methods that could be used?

Thanks.

[pb648174]

After calling the Upload.Save method, loop through the files, saving
them one at a time. As you save each one, retrieve the @@identity
property inside the stored proc you are using to insert the new file
entry. As Celko suggests, You could also use the path and filename as a
key if you want, since ASPUpload takes care of renaming files if they
have the same name. Just using an identity column is a heck of alot
easier though when you have things you want to relate to those files.

Tony Rogerson wrote:

Hi,

Use SCOPE_IDENTITY() to get the last inserted IDENTITY value for that
statement...

insert blha......

select pkID = SCOPE_IDENTITY()

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"BigJohnson" <cr***********@westleigh.co.uk> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

We're using ASPUpload as a tool to upload files to our server and save
the details to SQLServer. However, I have an application where I need
to return the pkID of the just saved file. I'm assuming that I could
use the @@Identity command but cannot get this to function.

Has anyone used this command with ASPUpload with an success, or any
other methods that could be used?

Thanks.

[Tony Rogerson]

You need to use scope_identity(), @@identity is affected by triggers etc...

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"pb648174" <go****@webpaul.net> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

After calling the Upload.Save method, loop through the files, saving
them one at a time. As you save each one, retrieve the @@identity
property inside the stored proc you are using to insert the new file
entry. As Celko suggests, You could also use the path and filename as a
key if you want, since ASPUpload takes care of renaming files if they
have the same name. Just using an identity column is a heck of alot
easier though when you have things you want to relate to those files.

Tony Rogerson wrote:

Hi,

Use SCOPE_IDENTITY() to get the last inserted IDENTITY value for that
statement...

insert blha......

select pkID = SCOPE_IDENTITY()

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"BigJohnson" <cr***********@westleigh.co.uk> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

> We're using ASPUpload as a tool to upload files to our server and save
> the details to SQLServer. However, I have an application where I need
> to return the pkID of the just saved file. I'm assuming that I could
> use the @@Identity command but cannot get this to function.
>
> Has anyone used this command with ASPUpload with an success, or any
> other methods that could be used?
>
> Thanks.
>

[manstein]

like I said earlier, if you know the name of the table where you are
inserting the file details, then the surest way to query the identity
value is with IDENT_CURRENT('<table name>'). The problem with
@@Identity var is that it is global to the session (i.e. connection)
which is why it is affected by triggers. Also, if the insert happens
in a rolled back transaction, the @@identity is NOT reset so it is
possible to have an invalid @@identity value. This may or may not be a
problem for you. Now, the problem with the scope_identity() is this
returns the IDENTITY value that is local to the scope of the insert.
This means you would have to query the IDENTITY value of the last
insert in the same code that the insert occurred (same session, same
local code scope(i.e. job)). I myself use scope_identity() but for
you, you may not be looking to query the IDENTITY in the same session.
For instance, your inserts might happen in one connection to the db and
you might instantiate a separate connection and query the IDENTITY
column. In that case, scope_identity() returns NULL. You would know
better these details so please post code or more info if this doesn't
help. Thanks.