Create New ID for SQL record

  • Thread starter Thread starter Mr Newbie
  • Start date Start date
M

Mr Newbie

Hi Guys,

Im writing an application presently and I need to create new SQL Records.
The problem is I want to know the last record ID number which I inserted.

The way I have it set up presently is that I set the ID in the DataTable
to -1 this ensures I get a unique ID on the server, however, I have also
generated a set or records in a relation in another DataTable which need to
be inserted into the SQL Database as well.

Does anyone know the correct or best way to acheive this, because I cant
write my related table until I know what ID my new master record has.

Also, the method has to be 100% reliable as this sytem will have multiple
users.


Regards and hope someone can help.
 
Setup the column as a ident
Do the insert and after that

SELECT @@IDENTITY which will return the last inserted identity number.

Regards
Fredrik
 
OK Thanks for that. Is there any chance though that if two concurrent
request went in to create the record that if the subsequent request for
@@IDentity was a litte later than the other request that you would get the
wrong ID back ?
 
Yes, I wondered about the Auto Increment, and I just asked that question.

The problem with GUID's are they are so horrible to work with from a human
perspective, we all like contiguous numbers you know. 1,2,3,4

I wonder if a web service would be a better way of dispensing uninque
numbers ?
 
Mr. Newvie,
The problem with GUID's are they are so horrible to work with from a human
perspective, we all like contiguous numbers you know. 1,2,3,4
Click to expand...

You should never show the Guid as you should never show the autoincrment
key, they are as well horrible after a while (it gives big holes if you
delete one and that is only because of technical reason. You can as well not
set them back after a delete, while you can do that with a Guid).

Just my opinion,


Cor
 
From BOL: "The scope of the @@IDENTITY function is current session on the
local server on which it is executed"

So if you do one insert and do the select within the same connection you are
safe.
At least I have never had any problems with it, using it in many places in a
multi-user app with several hundred clients.
 
Apparently, since posting this I have read that scope_identity() will
allways work on the same connection that initiated the request.

I'm not sure how well this works when someone is working through a firewall
because the have the same IP address ?
 
OK, well it looks like scope_identity() would probably work with no problems
for a natural numbering system originating from the same connection and
alternatively, GUID;s are the answer then if one does not need such a
system.

I'm learning here every day, thank you for your help.

--
Best Regards

The Inimitable Mr Newbie º¿º
 
OK, so is this done on the DataAdapter.Open() connection then rather than
the IP Connectivity ?
 
To be absolutely sure you're getting the right ID back, use
scope_identity(), instead of @@identity
 
Back
Top