Hi all,
I'm completely new to ASP.NET and trying to feel my way through it before we start using it at work. I'm tinkering with a basic little web form which allows data entry to one SQL Server 2005 table. I've created the form using Visual Web Developer 2008, and it's working ok thus far, but for this: I've got a field which is an identity increment (autonumber) - using the form, it's working fine, but when I try to insert a record which will fail due to having missing data, it does indeed fail to insert, but uses the identity increment anyway.

Example, my last record was ID#5, I then insert a dummy record which fails (which I want it to do), but when I successfully insert my next record, it's ID is #7. How do I stop the increment when an insert fails?

TIA

I'm no SQL server expert but I believe this has something to do with maintaining the integrity of the identity column.

When you start a new insert into a row that has an identity column, the transaction needs to obtain the next value, which needs to be unique. So it gets the current seed value which is the next number in the sequence and the seed is updated. Now, whether or not the insert succeeds, the seed value is updated to the next value. I imagine internally that all the calls to get and update the seed value are blocking calls such that it can only be done by one thread at a time to ensure the value's integrity.

Consider what would happen if you had two long running transactions that were happening concurrently. If both need to insert data to the identity field, they each need a unique number. If one fails but one succeeds (let's say the second one with the higher identity number) it will get saved with a legal value. However, the failed transaction won't commit and you'll thus have a gap in the identity sequence. This is mildly annoying yet perfectly acceptable.

In contrast, if both transactions obtained the same identity value, and then went to commit they would conflict. The identity value of the row needs to be obtained and "locked in" at the point of the initial row insert so that it may be used in successive commands regardless of whether or not the entire transaction, and thus the actual identity value generated, gets committed and saved to the database.

You could certainly change the column to be a regular numeric value and use a MAX() query to get it then manually increment it, etc. but this is where people get themselves into heaps of trouble. That technique is not multi thread friendly. Use the identity column as it's designed and live with the mild inconvenience of potential gaps. From the DB purist's point of view, identity column values are meaningless anyway, just a numeric row identifier, so what is the harm if there are gaps in the sequence?

-Peter
compiledthoughts.com

One of the things I've been working on, is a purchase order register where the user inputs the supplier and value, and is subsequently given a purchase order number (which is the identity-incremented number). I'm guessing missing numbers would cause some confusion should anyone query any orders.

Well, you could use a GUID for an order number. That would offer absolutely no sense of sequentiality so you'll never have gaps. Although, humanly dealing with a 32 character string of seemingly random numbers and letters would probably be more difficult than a nice clean integer.

-Peter
compiledthoughts.com

Hi,

Just a suggestion. Don't INSERT until all of the data has been validated.

You will never be able to get the used identity numbers back again.

You could always create your own order numbers in various ways and just use the identity column for the row.
Peter gave a very good explanation of the pitfalls you will encounter making your own order/identity numbers.

I have been using the identity column for years for order numbers although at times because of the scope of the project been asked to concatenate some the order numbers with letters for the type of order. The identity column remains the identity of that row no mater what else is done. I haven't had a problem with numbers having gaps.

There is a ROLLBACK syntax that you could use but I don't know if it rolls back the identity number.

using (SqlConnection connection =
            new SqlConnection(connectionString))
{
    SqlCommand command = connection.CreateCommand();
    SqlTransaction transaction = null;

    try
    {
        // BeginTransaction() Requires Open Connection
        connection.Open();

        transaction = connection.BeginTransaction();

        // Assign Transaction to Command
        command.Transaction = transaction;

        // Execute 1st Command
        command.CommandText = "Insert ...";
        command.ExecuteNonQuery();

        // Execute 2nd Command
        command.CommandText = "Update...";
        command.ExecuteNonQuery();

        transaction.Commit();
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
    finally
    {
        connection.Close();
    }
}

Richard

I don't believe that the .NET transaction rollback will provide any rollback of the identity column value as the value is managed by the database engine and the .NET transaction is a wrapper around a database transaction. And I know for sure that even if you ROLLBACK TRAN at the DB level, the seed is incremented regardless.

-Peter
compiledthoughts.com

Converted some of the fields to templates, had a play with the RequiredFieldValidator control and got this doing what I need it to do. Thanks for the help, chaps!


Just one other question: is it possible for a form to automatically pick up the username (or computer name) of the person entering data, so it can be saved as part of the record, ie. in an "EnteredBy" field?

I agree with you...

that is right way ...


________________
Web development

A little late to this thread but...

You *CAN* create a SQL Server stored procedure to safely get you a "next number" that doesn't depend on IDENTITY.

You just use a separate table with only one record and do a transaction-safe "get and increment" on that table. This has the advantage that, at the same time, you could "munge" that number to make it look more like (say) in invoice ID. [E.g., you could convert the number 73 into "INV-2009-00073" or similar.]

Not saying this is the best thing to do; just that it is possible. There's no really good corresponding way to do this with an Access DB, though.

***************

Another alternative: *ALWAY* create a record, always getting the autonumber/identity field. Mark the record (in another field, say, as "INCOMPLETE".

Then, instead of using INSERT to put the various values into the record, use UPDATE (where id=your new identity value). Finally, if all that works, you change the "mark" to say the record is "COMPLETE".

Now at least you have an "audit trail" of failed attempts to create a record.

***************

A third alternative: Have another table that has only one field: the autonumber/identity field.

After your main record (yes, which uses its own autonumber/identity field) is complete and validated, *THEN* insert a (dummy) record into the extra table, thus getting a truly sequential value. Again, as with the SP scheme, you can "munge" this sequential value to make a more human friendly identifier.