Using @@Identity From ASP page 1

[waggers]

We are inserting records into a SQL Server 7.0 table and are needing to be able to determine the identity value used by the system.

@@IDENTITY appears to be the way to go but we are not able to place this value into a variable when it is called from a ASP page.

I would be grateful for any ideas on how to access this value so that I can insert it into a variable.

Waggers [sig][/sig]

 

[jnicho02]

When you say that you can't place the value of @@IDENTITY into a variable do you mean that you don't get a correct value returned? If so, it could be because the statements are in different transactions....

so (and these are just guesses) could you either send a single transaction from ASP, or put the insert functionality into a stored procedure and make @@IDENTITY the return value? [sig]<p> <br><a href=mailto: > </a><br><a href=

http://www.geocities.com/jnicho02/>my

home</a><br> [/sig]

 

[waggers]

I don't seem to be able to get any value returned - although it works well is query analyser. How do I address a returned value from such an exercise? [sig][/sig]

 

[TomasDill]

waggers, when you need to place it into VB script variable (in ASP page), you cannot do this directly. You need to query this value from server immediately after you used insert command (or updated ADO recordset with new record in case you use ADO recordset). Use following query to get value:
SELECT @@IDENTITY as nID
This returns read-only recordset with 1 row and 1 nID field that contains ID number.
Finally, some types of ADO recordsets have built-in ability to refresh ID of new record after updating, so you do not need to do this manually.
[sig]<p>Vlad Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]

 

[waggers]

TomasDill,

I have tried this approach but the recordset is apparently being closed before I can reference the returned value.

The code I am using is...

sql = &quot;INSERT INTO <table-name> (<fields>) VALUES (<corresponding values>) SELECT @@IDENTITY as ID&quot;

SET rs_XXXXX = Server.CreateObject(&quot;ADODB.Recordset&quot
rs_XXXXX.Open sql, conn, 3, 3

strNewID = rs_XXXXX.Fields(&quot;ID&quot.Value


The response I am getting indicates that no object in the collection matches the name. When interrogating one the recordset properties the indication is that the recordset is already closed.

I would be grateful if you could have a look at my code and see if you can see what I am doing wrong. As I said, this sql statement works really well in query analyser and your earlier suggestions matches the documentation, but I cannot seem to make it work.

Thanks

Waggers
[sig][/sig]

 

[TomasDill]

Well, maybe problem is in ADO. In VFP 6 result returned as cursor without a problem.
Try to split these commands and run them in the same connection (maybe INSERT command processed differently by ADO). Try to use different record set modes for the cursor that should be returned by SELECT SQL command (probably it should be read-only). I don't know exactly because I'm not expert in ADO, just tried it in one VB project and one ASP project. Maybe someone else know? [sig]<p>Vlad Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br><a href=

http://www.softserve.lviv.ua>Soft

Serve</a><br>The professional level of programmer could be determined by level of stupidity of his/her bugs[/sig]

 

[TomasDill]

Well, maybe problem is in ADO. In VFP 6 result returned as cursor without a problem.
Try to split these commands and run them in the same connection (maybe INSERT command processed differently by ADO). Try to use different record set modes for the cursor that should be returned by SELECT SQL command (probably it should be read-only). I don't know exactly because I'm not expert in ADO, just tried it in one VB project and one ASP project. Maybe someone else know? Try to ask this question in other forums. At least you have SQL Server part of answer [sig]<p>Vlad Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br><a href=

http://www.softserve.lviv.ua>Soft

Serve</a><br>The professional level of programmer could be determined by level of stupidity of his/her bugs[/sig]