Why do I get a 0 value for my @@identity field?

Question

Good Day I have a form that retrieves the next available auto-number value of a field in MSACCESS 2000 every time it loads unfortunately it returns a 0 value. Here is my code: connection.Open(); int newid; OleDbCommand command = new OleDbCommand(Select @@identity From IssuanceHeader connection); newid = (int) command.ExecuteScalar(); txtIssuanceNo.Text = newid.ToString(); connection.Close(); Is there something wrong?

hey_mr_art · Answer

Thanks very much i'll apply what u just said. :) Arthur

sjones · Answer

You can't do it. The number is not generated until it is actually needed. The server does this for a reason. If you asked for the next number but didn't use it right away and then someone else asks for the next number you would both have the same number. When you both finally got around to using it you would have duplicates. That's the whole idea of having a server based Identity number. To do what you want you should actually Insert a new record when your form opens and then Update the record later. If your user abandonds the record you would have to delete it on your way out. You could wrap the whole form in a transaction and Rollback at the end but I don't suggest leaving a transaction open that long. This does not guarantee the sequence of numbers. You will have gaps. But chances are if records can be deleted from the table you will end up with gaps anyway. If you don't need the numbers to be sequential I would suggest you use a GUID instead. By definition they are guaranteed unique and since your client app creates it you know what it is before you ever contact the DB server. Indentity columns were a great thing in the connected world of client server but now in this disconnected world GUID's are the way to go.

hey_mr_art · Answer

Hi Actually my real concern is how to get the next available value for an auto-generated field. I have a data-entry form that adds a new transaction. So everytime the form loads I want the user to see the next available auto-generated value without actually adding the record yet. Regarding option 1 would i really get the Current Identity value? How come my code generated a zero value when I have existing records in my table? Thanks for your reply :) Arthur

send2manivannan · Answer

Hi Check these points 1. Select @@identity Neednot the Table Name. If you use the Table name it will return the Current Identity value with n - number of rows(n=total rows in the Table) 2. Identity value should be fetched with in the current Connection/Transaction. Before call the  Select @@identity  you should execute the insert statement. 3. @@Identitiy is global vairable for the Current DB. Not for the Table. So it always store the current Identity value in one place-globaly to all. So you need not to give the Table Name -Manivannan.D