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

hey_mr_art

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?

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

hey_mr_art22y ago
Thanks very much, i'll apply what u just said. :) Arthur
+1
sjones22y ago
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.
+1
hey_mr_art22y ago
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
+1
send2manivannan22y ago
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
+1