CODEWITHSUNDEEP
sqlreturnauto-increment
howdybaby
howdybaby

Reputation: 307

SQL returning auto-incremented ID

I'm trying to pick up SQL and am using it in conjuction with ASP Classic. I'm currently inserting into a database but have come across a problem, the insertion works fine but I want to be able to set a variable to be the value of the ID that is generated as a result of the insertion (it's an auto-incrememnting field so a new ID is created every time I insert) and then return the value of this variable to the user. Here's what I've currently got: (QuestionID is the auto-incremementing ID)

     set rs=Server.CreateObject("ADODB.recordset")
     rs.Open "Select * from Questions", conn
     sql="DECLARE @id int"     
     sql=sql & "INSERT INTO Questions(QuestionText, QuestionType)"
     sql=sql & " VALUES "
     sql=sql & "@id = Inserted.QuestionID;"
     sql=sql & "('" & qtext & "',"
     sql=sql & "'" & "checkbox" & "')"
     Response.Write("<br/>" & @id & "<br/>" )

     on error resume next
     conn.Execute sql,recaffected
     if err<>0 then
     Response.Write("An Error Has Occured")
     else
     Response.Write("Data Added")
     end if
     conn.close

Any help? (again sorry if this is a stupid question, i'm new to this stuff)

Upvotes: 0

Views: 140

Answers (3)

Nagaraj S
Nagaraj S

Reputation: 13484

You can use SCOPE_IDENTITY() or @@identity

SET @id=SCOPE_IDENTITY()
      RETURN  @id

string id = cmd.Parameters["@id"].Value.ToString() ;
Response.Write("data added ID = " +  id);

Upvotes: 0

Quick Joe Smith
Quick Joe Smith

Reputation: 8222

In most cases, you will prefer the result of scope_identity(), not @@identity. They are both very similar, however scope_identity() is restricted to the current session and scope.

Conversely, @@identity is only restricted to the current session. This means it could potentially return the identity value of a row inserted by a DML trigger that you may or may not know exists.

It's an edge case, to be sure, but you are almost always better off with scope_identity().

Upvotes: 1

jean
jean

Reputation: 4350

In the same query just add at the end.

select @@identity as ID

hit the books about this global variable and some nice function do similar thing.

p.s. your app is prone to sql inject attack

Upvotes: 1

Related Questions