AutoID or Identity


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: AutoID or Identity

  1. #1
    Join Date
    Dec 2004
    Posts
    717

    Question AutoID or Identity

    Hello All,

    It would be great if anyone explain me.
    Eg: There are three records in table that include one Identity Field.So this identity column will be 1,2,3.
    After that I delete all records in that table.
    And then, I insert new one record into the table.So It's identity will come out 4.

    My question is how can I know which number will be next.I mean how can I know the new Id will be 4.
    I would like to use in my application that written in VB.Net.There is one form that use for Invoice Data Entry.Invoice Id will be displayed in this form.After a user save one invoice [ InvoiceID = 1 ], New Invoice ID will be displayed [ InvoiceID =2 ].

    Thanks in advances,
    Regards,
    Michael

    P.S : I think we can use @@Identity.
    Last edited by Sync; 07-04-2006 at 01:17 AM.
    Best Regards,
    Michael Sync
    http://michaelsync.net

    The more you share,The more you get

  2. #2
    Join Date
    Jan 2004
    Location
    Alexandria, VA
    Posts
    392
    This is a little cheesey, but you could do:

    Select top 1 InvoiceID from Invoices order by InvoiceID desc
    Bob Rouse
    Dimension Data

  3. #3
    Join Date
    Nov 2004
    Location
    Huddinge, Sweden
    Posts
    283
    Knowing in advance what identity will be generated assumes it's a single user application. Otherwise, there's no telling what ID you'll get until the Invoice is actually created. Since invoices numbers are usually required to be contigous, you can't reserve a number unless it's really used.

    If you're writing a single user app, you can use the IDENT_CURRENT() function to get the last identity value used for a table.
    So if your table is "dbo.Invoice", you can use this to get the next ID:
    Code:
    SELECT IDENT_CURRENT('dbo.Invoice') + IDENT_INCR(dbo.Invoice') AS NextInvoiceID
    I'd advise to check the actual value of SCOPE_IDENTITY against the value "guessed" by the above code, just in case.

    Rune Bivrin
    If you hit a brick wall, you didn't jump high enough!

  4. #4
    Join Date
    Jan 2004
    Location
    Alexandria, VA
    Posts
    392
    If there will be multiple logins possibly adding entries at nearly the same time, you should do all of it inside a transaction, to ensure that other changes do not cause problems:

    Code:
    BEGIN TRANSACTION AddInvoice
    
    Insert into....
    
    SELECT IDENT_CURRENT('dbo.Invoice') + IDENT_INCR(dbo.Invoice') AS NextInvoiceID
    
    COMMIT TRANSACTION AddInvoice
    If you are using a DB System that allows stored procedures, you could do this in the stored procedure to keep your VB code simple.
    Bob Rouse
    Dimension Data

  5. #5
    Join Date
    Nov 2004
    Location
    Huddinge, Sweden
    Posts
    283
    I'm sorry Bob, but that doesn't quite cut it. However you look at it, there's no knowing that the next Invoice you create will have the ID selected. Transactions only make sure what you read and write is consistent throughout the transaction.

    By not using IDENTITY, but rather incrementing the ID manually:
    Code:
    CREATE PROCEDURE CreateInvoice
    @InvoiceID int,
    ...
    AS
    
    DECLARE @InvoiceID int
    BEGIN TRANSACTION
        INSERT INTO Invoices (InvoiceID, ...) VALUES (@InvoiceID, ...)
    
        IF @Error <> 0 GOTO ErrHandler
    
        SELECT MAX(InvoiceID) + 1 AS NextInvoiceID
        FROM Invoices
    
    COMMIT TRANSACTION
    RETURN
    
    ErrHandler:
        ROLLBACK TRANSACTION
        RAISERROR('Someone stole the ID', 18, 1)
        RETURN
    
    
    GO
    you have the possibility of trying to use the guessed ID and be alerted if that couldn't be done.

    Rune
    If you hit a brick wall, you didn't jump high enough!

  6. #6
    Join Date
    Apr 2005
    Posts
    1
    how to insert image in oracle

  7. #7
    Join Date
    Dec 2004
    Posts
    717
    Hi Rune Bivrin,
    I think we can't use MAX() func for new id.
    Best Regards,
    Michael Sync
    http://michaelsync.net

    The more you share,The more you get

  8. #8
    Join Date
    Nov 2004
    Location
    Huddinge, Sweden
    Posts
    283
    Quote Originally Posted by Sync
    Hi Rune Bivrin,
    I think we can't use MAX() func for new id.
    Yes and no. The "new ID" will always be a guess in a multi user scenario, unless you actually reserve the ID before presenting the user with a blank invoice entry form, but that is a rather dubious practice.
    So MAX() will be as good a guess as any. The point is you can't know the next ID that particular user will get, so I'd definitely suggest yu do not present it at all.

    Rune
    If you hit a brick wall, you didn't jump high enough!

  9. #9
    Join Date
    Jun 2006
    Posts
    8
    how do you use this SCOPE_IDENTITY command

  10. #10
    Join Date
    Nov 2004
    Location
    Huddinge, Sweden
    Posts
    283
    SCOPE_IDENTITY() is a system function:

    Code:
    DECLARE @id int
    
    INSERT INTO T1 (c1, c2) VALUES ('foo', 'bar')
    
    SET @id = SCOPE_IDENTITY()
    Rune
    If you hit a brick wall, you didn't jump high enough!

  11. #11
    Join Date
    Jun 2006
    Posts
    8
    So when you are using it in VB.net
    CommandString = "Insert Into Companies (CompanyName) Values (" & "'" & Trim(txtCompanyName.Text) & "') set varid = scope_identity();"

    is this correct - cos this is returning an error
    my head is melted trying to get this to work!

  12. #12
    Join Date
    Nov 2004
    Location
    Huddinge, Sweden
    Posts
    283
    This is typically done in a stored procedure. Your "varid" is a VB variable, I believe. That's not possible, unfortunately.

    Code:
    commandString = "Insert Into Companies (CompanyName) Values (" & "'" & Trim(txtCompanyName.Text) & "'); select scope_identity() as new_id;"
    
    varid = connection.ExecuteScalar(commandString)
    If you hit a brick wall, you didn't jump high enough!

  13. #13
    Join Date
    Jun 2006
    Posts
    8
    in your example New_ID is this the field containing the new record id in the table or should this remain as New_Id

    where does varid = connection.ExecuteScalar(commandString) get placed?
    here is my coded. is the ExecuteNonQuery replaced by ExecuteScalar?

    dbConn = New OleDbConnection(ConString)
    dbCommand.CommandText = CommandString
    dbCommand.Connection = dbConn
    Try
    dbConn = New OleDbConnection(ConString)
    dbCommand.CommandText = CommandString
    dbCommand.Connection = dbConn
    dbCommand.Connection.Open()
    dbCommand.ExecuteNonQuery()
    dbConn.Close()
    Catch err As SystemException
    MsgBox(err.ToString)
    End Try

    Cheers?

  14. #14
    Join Date
    Nov 2004
    Location
    Huddinge, Sweden
    Posts
    283
    First of all, is there a reason why you use OleDB rather than SqlClient?

    Second, yes ExecuteNonQuery gets replaced by ExecuteScalar. But it should be dbCommand.ExecuteScalar(). My error.

    Third, New_Id is just the name of the column in the result set. It doesn't matter what it's called when you use ExecuteScalar, but I like to give all SELECT columns meaningful names.

    Rune
    If you hit a brick wall, you didn't jump high enough!

  15. #15
    Join Date
    Jun 2006
    Posts
    8
    Well I am not too sure what teh differences is between OleDB and SqlClient. I dont have an sql server. is sql client faster, and if so, how much faster?

    Anyway,
    I am now getting a message saying that "characters found after end of SQL Statement",
    here is my sql command string.
    is this infact 2 sql statements?

    CommandString = "Insert Into Companies (CompanyName) Values (" & "'" & Trim(txtCompanyName.Text) & "'); SELECT Scope_Identity() as CompanyID;"

    Cheers
    CiaranG

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center