| Author |
 Topic  |
|
|
kolucoms6
Average Member
845 Posts |
 Posted - 14 August 2007 : 01:50:49
|
After I run below Code, it returns me ZERO.Any Help ?
'+++++++++++++++++++++++++++++++++++++++++
'Get last Generated Unique ID , required to Update
'+++++++++++++++++++++++++++++++++++++++++
Dim lsSQL, loRs
lsSQL = "SELECT @@IDENTITY AS NewID"
Set loRs = adCn.Execute(lsSQL)
llID = loRs.Fields("NewID").value
Response.write "loRs.Fields(NewID).value" & loRs.Fields("NewID").value
'+++++++++++++++++++++++ |
Edited by - kolucoms6 on 14 August 2007 01:51:22 |
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 14 August 2007 : 03:07:22
|
You have to use @@identity right after you do an insert into a table, and the table must have be using an identity/autonumber primary key on the table.
|
======
Doug G
======
Computer history and help at www.dougscode.com |
 |
|
|
kolucoms6
Average Member
845 Posts |
Posted - 14 August 2007 : 05:33:08
|
Just afer Update statement,I have following code.My table has one Primary Key column also.
RS.Update
'+++++++++++++++++++++++++++++++++++++++++
'Get last Generated Unique ID , required to Update
'+++++++++++++++++++++++++++++++++++++++++
Dim lsSQL, loRs
lsSQL = "SELECT @@IDENTITY AS NewID"
Set loRs = adCn.Execute(lsSQL)
llID = loRs.Fields("NewID").value
' Response.write "loRs.Fields(NewID).value" & loRs.Fields("NewID").value
'+++++++++++++++++++++++ |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 14 August 2007 : 05:38:16
|
| it has to be after an insert, @@Identity returns the identity of a newly INSERTED record |
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 14 August 2007 : 06:07:09
|
If it's an UPDATE statement then surely you already have some way of identifying the record?
|
 Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
kolucoms6
Average Member
845 Posts |
Posted - 14 August 2007 : 06:26:55
|
Its like
RS.ADDNew
[Statement]
[Statement]
[Statement]
RS.Update
+++++++++++++++++++++++++++++++++++++++++
'Get last Generated Unique ID , required to Update
'+++++++++++++++++++++++++++++++++++++++++
Dim lsSQL, loRs
lsSQL = "SELECT @@IDENTITY AS NewID"
Set loRs = adCn.Execute(lsSQL)
llID = loRs.Fields("NewID").value
' Response.write "loRs.Fields(NewID).value" & loRs.Fields("NewID").value
'+++++++++++++++++++++++
|
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 14 August 2007 : 06:59:57
|
you are using a recordset which is what is causing your problem. @@Identity is used as follows
lsSQL = "INSERT INTO blah...blah;"
lsSQL = lsSQL & "SELECT @@IDENTITY AS NewID"
Set loRs = adCn.Execute(lsSQL)
llID = loRs.Fields("NewID").value
' Response.write "loRs.Fields(NewID).value" & loRs.Fields("NewID").value
|
|
|
|
kolucoms6
Average Member
845 Posts |
Posted - 14 August 2007 : 07:10:35
|
So, does it means I CANT use Identity with Addnew feature ? |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 14 August 2007 : 07:38:42
|
| to be honest you shouldn't really be using the recorset addnew functionality at all, it is pretty bad, recordsets should be used for retrieving data not inserting it, they can cause all kinds of locks and contentions which are bad for the server, it is much better to use an INSERT SQL statement. |
|
|
|
kolucoms6
Average Member
845 Posts |
Posted - 14 August 2007 : 08:13:47
|
ahh Ok...
I have around 35 fields need to added :-(
If you suggest so, I will change it..
|
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 14 August 2007 : 08:44:19
|
shouldn't make any difference, it is no more work to do something like this instead
lsSQL = "INSERT INTO TABLE (field1,field2,field3.....) VALUES("
lsSQL = lsSQL & "value1,"
lsSQL = lsSQL & "value2," etc etc |
|
|
|
kolucoms6
Average Member
845 Posts |
Posted - 14 August 2007 : 08:59:35
|
| Ok |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 14 August 2007 : 15:21:57
|
| In most cases the new ID value is immediately available in a recordset if you use AddNew and Update, as long as the ID column is included in the recordset. However some recordset cursor types/locations may prevent the ID from showing in your recordset. Using actual SQL as HuwR showed is a better solution in most cases. |
======
Doug G
======
Computer history and help at www.dougscode.com |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 14 August 2007 : 19:15:06
|
| As @@Identity is session-based (so a multiuser system gives accurate results), I believe you need to request it as a part of the same connection session (in the case of a connection-pooled web-db app, that means getting the value as a part of the same db hit) |
|
|
| |
Topic |
|
Topic Locked
