473,219 Members | 1,473 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,219 software developers and data experts.

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

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.

Nov 25 '05 #1
8 2697
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
Nov 26 '05 #2
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.

Nov 27 '05 #3
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?

Nov 27 '05 #4
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?

Nov 27 '05 #5
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.

Nov 27 '05 #6
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.


Nov 27 '05 #7
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.
>

Nov 27 '05 #8
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.

Nov 29 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Newbillian | last post by:
After converting Access 97 databases to 2003 this error msg shows up after a line about not being able to save the record because a related record is needed in another table. The complete msg is: ...
4
by: kieran | last post by:
Hi, I import data every evening through a scheduled task from a text file. Once the data is imported, an email is sent to a user saying how much data etc.. was in the file. In case the file...
1
by: aferoce | last post by:
Hello! I'm new to SQL and i've searched everywhere for the answer to this question but I can't seem to find the anwer anywhere. My question is..... If there are multiple rows for a specific...
4
by: SoulSeeker | last post by:
Hello. How I can start my M$ access form with last edited record? What I must put in "on load" field? Tnx. for advice.
4
ollyb303
by: ollyb303 | last post by:
Hi, I have a problem I need some help with. In my Access 2000 database (used for logging complaints to my company), I'm using the following code to send an email to our finance dept when a...
13
rajiv07
by: rajiv07 | last post by:
Hi to all, I want to know how to select a last inserted record which is the primary key is not an integer. my table ramstr(Primary)----name--service XTC01-------Rajiv---service ...
11
by: andrewdb | last post by:
I have been working with a database that was already created by somebody else, who now no longer works here, so I cant ask any questions. None the less, there is a table 'Ascertainment' which...
4
semanticnotion
by: semanticnotion | last post by:
Hi guys how can i retrieve the last inserted record from datebase. my primary key is not auto_increment its type is bigint because i want to insert SSN no as a primary key. when i retrive the data...
1
by: Bigdaddrock | last post by:
I have a field called DateLastRevised in a Table. I would like that field to be updated to reflect the date and time that the record was last revised. I have a form that edits these records and...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.