@@identity selects into what kind of VAR?

Results 1 to 7 of 7

Thread: @@identity selects into what kind of VAR?

  1. #1
    Join Date
    Dec 1969
    Posts
    6

    Default @@identity selects into what kind of VAR?

    I know that the question of selecting the most recent identity from a newly inserted record has been asked, but the question I have is to what do you dim a variable ("address_id") to perform the following line:<BR><BR>Set address_id = dataConn.execute("SELECT @@IDENTITY FROM addresses")<BR><BR>I&#039m using SQL 6.5 and it is stating "Type Mismatch". Thanks for reading.

  2. #2
    Jairo Guest

    Default RE: @@identity selects into what kind of VAR?

    Justin,<BR><BR>dim address_id<BR><BR>Set yourRecordSet = dataConn.execute("SELECT @@IDENTITY as address_id FROM addresses")<BR><BR>address_id = yourRecordSet("address_id")<BR><BR>HTH,<BR>Jairo<B R>http:/www.citywebarchitects.com

  3. #3
    Join Date
    Dec 1969
    Posts
    2,849

    Default RE: @@identity selects into what kind of VAR?

    In ASP, since everything is a variant, you just do:<BR><BR>Dim objAddressIDRS<BR>Set objAddressIDRS = dataConn.Execute("SELECT @@IDENTITY FROM addresses")<BR><BR>Dim iAddressID<BR>iAddressID = CInt(objAddressIDRS(0))<BR><BR>We use two variables here, a variable that becomes a recordset (objAddressIDRS) and a variable to hold a numeric variable (iAddressID). Does this answer your question(s)?

  4. #4
    Join Date
    Dec 1969
    Posts
    6

    Default RE: @@identity selects into what kind of VAR?

    when I run the following query on the database inside Msquery:<BR><BR>insert into addresses (city) values (&#039test&#039)<BR>SELECT @@Identity as ID FROM addresses <BR><BR>here is what I get:<BR><BR>ID <BR>------------------------------------------ <BR>4<BR>4<BR>4<BR>4<BR><BR>It is returning the correct recent ID but four times due to the number of records in addresses. <BR><BR><BR>When I run the code tha you gave me above. I ran it under an insert into addresses and iaddressID errors out to say that it can&#039t CINT a null. Which means that it is not returning a value! Here is the code for that:<BR><BR>&#060;%@ LANGUAGE="VBSCRIPT" @%&#062;<BR>&#060;%<BR><BR>set dataConn = Server.CreateObject("ADODB.Connection")<BR>dataCon n.Open "WEB", "uname", "pword"<BR><BR><BR>set InsertAddress = dataConn.execute ("insert addresses (city) values (&#039testweb&#039)")<BR><BR>Dim objAddressIDRS<BR>Set objAddressIDRS = dataConn.Execute("SELECT @@IDENTITY FROM addresses")<BR><BR>Dim iAddressID<BR>iAddressID = CInt(objAddressIDRS(0))<BR><BR><BR><BR>%&#062;<BR> &#060;html&#062;<BR><BR>&#060;head&#062;<BR>&#060; title&#062;&#060;/title&#062;<BR>&#060;/head&#062;<BR><BR>&#060;body&#062;<BR>&#060;% = iAddressID %&#062;<BR>&#060;/body&#062;<BR>&#060;/html&#062;<BR>


  5. #5
    Join Date
    Dec 1969
    Posts
    6

    Default RE: @@identity selects into what kind of VAR?

    Jairo-<BR><BR>When I run the following code all I get is null. <BR><BR>set InsertAddress = dataConn.execute ("insert addresses (city) values (&#039testweb&#039)")<BR><BR>dim address_id<BR><BR>Set yourRecordSet = dataConn.execute("SELECT @@IDENTITY as address_id FROM addresses")<BR><BR>address_id = yourRecordSet("address_id")<BR><BR><BR><BR><BR>%&# 062;<BR>&#060;html&#062;<BR><BR>&#060;head&#062;<B R>&#060;title&#062;&#060;/title&#062;<BR>&#060;/head&#062;<BR><BR>&#060;body&#062;<BR>&#060;% = address_id %&#062;<BR>&#060;/body&#062;<BR>&#060;/html&#062;

  6. #6
    Jairo Guest

    Default RE: @@identity selects into what kind of VAR?

    Justin,<BR><BR>From the SQL Server 6.5 Books-On-Line:<BR>@@IDENTITY*<BR>Saves the last-inserted IDENTITY value. The @@IDENTITY variable is updated specifically for each user when an INSERT or SELECT INTO statement or bulk copy inserting into a table occurs. If a statement changes the table without an identity column, @@IDENTITY is set to NULL.<BR><BR>So the reason you&#039re getting NULL is because you need to grab the identity value right after you do the insert.<BR>In other words, first you do an insert into your ADDRESS table. Right after that executes, you execute another SQL statement to grab the identity value.<BR><BR>HTH,<BR>Jairo<BR>http://www.citywebarchitects.com

  7. #7
    Jairo Guest

    Default RE: @@identity selects into what kind of VAR?

    Justin,<BR><BR>You need to execute the insert and then directly right after, execute the @@identity code. Don&#039t worry about getting 4 values back. Just grab the first one and you&#039re set.<BR><BR>HTH,<BR>Jairo<BR>http://www.citywebarchitects.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •