@@Identity problem

@@Identity problem

Post by Denis Zodd » Thu, 23 May 2002 19:53:48



I'm running the following code:

 INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)

 set rsID = Server.CreateObject("ADODB.Recordset")
 rsID.ActiveConnection = MM_MSC_STRING

 rsID.CursorType = 3
 rsID.CursorLocation = 2
 rsID.LockType = 3
 rsID.Open()
 response.write "Nuovo utente: " & rsID("myID")

the result is 10

but in my sql db the ID is 11

Every time I execute the code it returns the real ID-1

What's happen?

Thanks

 
 
 

@@Identity problem

Post by Tibor Karasz » Thu, 23 May 2002 20:43:39


Denis,

I can think of two things:


the value from the riggers INSERT statement. Use SCOPE_IDENTITY() instead.


connection. Troubleshoot using profiler. Fix by doing both the INSERT and the SELECT
SCOPE_IDENTITY() inside the same batch/stored procedure.

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...


> I'm running the following code:

>  INSERT INTO jobs (job_desc,min_lvl,max_lvl)
> VALUES ('Accountant',12,125)

>  set rsID = Server.CreateObject("ADODB.Recordset")
>  rsID.ActiveConnection = MM_MSC_STRING

>  rsID.CursorType = 3
>  rsID.CursorLocation = 2
>  rsID.LockType = 3
>  rsID.Open()
>  response.write "Nuovo utente: " & rsID("myID")

> the result is 10

> but in my sql db the ID is 11

> Every time I execute the code it returns the real ID-1

> What's happen?

> Thanks


 
 
 

1. Identity problems

Anybody know why the identity values get confused and an insert begins
to blow-up due to PK constraints?  I have not seen anybody explain WHY
this occurs.  I know that I can run DBCC checkident to fix the problem
but I'd rather the problem not occur.  Also the SA is the only person
who can run DBCC and I'd prefer that the user logged into the system not
have SA rights other than the ability to run DBCC.  Any
suggestions/solutions?  Thanks in advance.

Mike McClellan
Net Explorer, Inc.

2. DTS import from access - Table getting sorted

3. IDENTITY problems

4. SQL2k Array

5. @@Identity problems...

6. URGENT: Which services are necessary for OleDb-Connection?

7. @@IDENTITY problem with VB432/SQL Server 6.5

8. Query Analyzer

9. Identity problem in Table

10. sql server6.5 Identity problem

11. Newbie : IDENTITY Problem...

12. Bulk Load and Identity problem...

13. Identity Problem