AutoID or Identity

[Sync]

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.

[brouse]

This is a little cheesey, but you could do:

Select top 1 InvoiceID from Invoices order by InvoiceID desc

[Rune Bivrin]

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:

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

[brouse]

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:

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.

[Rune Bivrin]

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:

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

[smile]

how to insert image in oracle

[Sync]

Hi Rune Bivrin,
I think we can't use MAX() func for new id.

[Rune Bivrin]

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

[CiaranG]

how do you use this SCOPE_IDENTITY command

[Rune Bivrin]

SCOPE_IDENTITY() is a system function:

DECLARE @id int

INSERT INTO T1 (c1, c2) VALUES ('foo', 'bar')

SET @id = SCOPE_IDENTITY()

Rune

[CiaranG]

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!

[Rune Bivrin]

This is typically done in a stored procedure. Your "varid" is a VB variable, I believe. That's not possible, unfortunately.

commandString = "Insert Into Companies (CompanyName) Values (" & "'" & Trim(txtCompanyName.Text) & "'); select scope_identity() as new_id;"

varid = connection.ExecuteScalar(commandString)

[CiaranG]

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?

[Rune Bivrin]

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

[CiaranG]

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