Let SQL Server do the work for you.
1. Declare a Primary Key column (TicketNumber) in the "Ticket" table of
INT
or
BIGINT
Data Type with Column Properties Identity Specification
IsIdentity
set to Yes, Identity Increment set to 1 and Identity Seed set to 1.
CREATE TABLE YourTableName (
TicketNumber INT NOT NULL IDENTITY(1,1),
)
or
CREATE TABLE YourTableName (
TicketNumber BIGINT NOT NULL IDENTITY(1,1),
)
2. After an
INSERT
of a new "Ticket" row, use the
SCOPE_IDENTITY
function to get the Primary Key of the inserted row. That value is the "Ticket Number".
* See
Retrieving Identity or Autonumber Values (ADO.NET)[
^].
* See example of retrieving
SCOPE_IDENTITY
at
Obtaining a Single Value from a Database[
^].