VB.NET - Auto-Generate Number - Asked By Developer Developer on 16-Dec-09 03:54 AM


I need auto-Generate number like Ticket-0001
Varchar : Ticket-
Int           : 0001

it should me increment next time

How can i do? i am using vb.net & sql server 2005

re - Web Star replied to Developer Developer on 16-Dec-09 04:02 AM

  • Identity Columns - An identity is a common "auto generated" primary key to use in a SQL Server database these days. An identity is simply an integer value that "auto increments" as each new row is added to your table. You can specify when it should start and how it should increment when you add the column to your table:
alter table YourTable add ID int identity(start, increment) 
  • GUID Columns - A "GUID" is a Globally Unique Identifier that can be assigned a unique yet random long string of characters like "B3FA6F0A-523F-4931-B3F8-0CF41E2A48EE". You can either use the NEWID() function when inserting into your table or set a default like this to implement a GUID column in your tables:
alter table YourTable add ID uniqueidentifier default newid() 

However, we often see questions in the forums regarding how to create other types of auto-generated sequences in tables. For example, you might want your customers to automatically be assigned "Customer Numbers" as formatted like this:


In other cases, people would like to use incrementing letters instead of numbers, some combination of both, or for the digits to be "reset" on some specific condition, and so on.

The most important and crucial part of implementing this is not writing the code! It is clearly defining your specification and ensuring that it is logical and works for you. Before you can write code that will automatically generate sequences for you, you must consider:

  • How many numbers will you ever need? Does your specification handle this?
  • What happens when values are deleted? Are they re-used?
  • Are these sequences dependant on data that might ever change? What happens to these values when the data does change? Does it make sense, then, to incorporate this data into your sequence algorithm?
  • If you have a complicated rule (i.e., "AA-00" through "ZZ-99"), is every step and possibility clearly defined? In this example, what comes after AA-99? Is it "BA-00", "AB-00", "BB-00", or something else? What comes after "ZZ-99"?

So, the very first step is to clearly, accurately, and completely define how your sequence values will be generated. You must explicitly map out how to handle all possible situations and you must do some research to ensure that your specification will work for the data you are handling. A primary key of "A0" through "Z9" will only work for 26*10 = 260 values -- is this really what you want?


Auto-Generate Ticket Number - mv ark replied to Developer Developer on 16-Dec-09 04:08 AM

You need not have a varchar field to hold the string "Ticket-". Assuming the unique numbers are generated by specifying a column as Identity, the number can be padded & then the string "Ticket-" can be prefixed to it -

SELECT 'Ticket-' + REPLACE(STR(ticket_id, 4), SPACE(1), '0') FROM tickets
Jonathan VH replied to Developer Developer on 16-Dec-09 07:27 AM

You can do that with a computed column, e.g.:

CREATE TABLE dbo.Tickets(
Incr smallint PRIMARY KEY IDENTITY CHECK (Incr<10000),
YourNumber AS 'Ticket-' + REPLACE(STR(Incr,4),' ','0'));

re - Developer Developer replied to Jonathan VH on 18-Dec-09 02:15 AM
CREATE TABLE dbo.Tickets(
Incr smallint PRIMARY KEY IDENTITY CHECK (Incr<10000),
YourNumber AS 'Ticket-' + REPLACE(STR(Incr,4),' ','0'));

how do i use this code in vb.net tell me pls

Jonathan VH replied to Developer Developer on 18-Dec-09 02:16 PM

That is an example of how you coud design your table in SQL Server. use code like that once when you create the table where the ticket entities are stored. If you do something like this, the number will be automatically incremented each time you insert a new ticket. So, if you need to store, say, date and location for each ticket, you create the table with those columns and the identity column and the computed column, e.g:

CREATE TABLE dbo.Tickets(
Incr smallint PRIMARY KEY IDENTITY CHECK (Incr<10000),
YourNumber AS 'Ticket-' + REPLACE(STR(Incr,4),' ','0')),
OpenedDate datetime DEFAULT(GETDATE()),
LocationID tinyint REFERENCES dbo.Locations);

Then, from your application, you insert just the date and location, or, in this example, you could insert just the location, as the date will default to the date and time of the insertion. The identity column (that I named Incr) will automatically increment and the computed column (YourNumber) will concatenate your text with the number. So when you look at the data:

SELECT t.YourNumber, t.OpenedDate, l.Description
FROM dbo.Tickets t JOIN dbo.Locations l ON t.LocationID = l.ID
ORDER BY t.Incr;

You will see the data in the desired format.

Note that the incremented numbers could conceivably skip over some numbers if a transaction gets rolled back after another process has also inserted.

From VB.NET, there are several ways to work with SQL Server data. You have to decide on what methodology best suits your application and skill set.