CODEWITHSUNDEEP
sql-servervba
Chippu
Chippu

Reputation: 13

Generate unique ID when adding a row of data to SQL Server database using Visual Basic

I am currently writing an inventory program for my future business. I can already add, delete, update my server.

My problem is that I want to set a unique ID like "ID-0012" or something like that when I try to add the date to my table. Currently I'm only getting ID = 1,2,3,4...

My table consists of ID, Name, Description, Price, Date. I am currently searching for an answer through google, and I have found out that I need to code this in the SQL Server. I tried using uniqueidentifier but I am getting an error of operation type clash.

Upvotes: 1

Views: 4497

Answers (2)

Ashraf Uddin
Ashraf Uddin

Reputation: 82

you can you follow this step.

For table create

CREATE TABLE Inventory
  (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
   InventoryId Varchar(7),
   .... your other columns here....
  )

For Insert Query

INSERT INTO Inventory (Col1, Col2, ..., ColN)
VALUES ('ID-'+ right('0000'+CONVERT(varchar(7), (cast(right((select isnull(InventoryId,0)  from Inventory where ID=(select max(ID) Form Inventory)),4) as int)+1)),4), Val2, ....., ValN)

Upvotes: 0

marc_s
marc_s

Reputation: 754518

The best solution is to use

  • an ID INT IDENTITY(1,1) column to get SQL Server to handle the automatic increment of your numeric value
  • a computed, persisted column to convert that numeric value to the value you need

So try this:

CREATE TABLE dbo.Inventory
  (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
   InventoryId AS 'ID-' + RIGHT('00000' + CAST(ID AS VARCHAR(5)), 5) PERSISTED,
   .... your other columns here....
  )

Now, every time you insert a row into dbo.Inventory without specifying values for ID or InventoryId:

INSERT INTO dbo.Inventory (Col1, Col2, ..., ColN)
VALUES (Val1, Val2, ....., ValN)

then SQL Server will automatically and safely increase your ID value, and InventoryId will contain values like ID-00001, ID-00002,...... and so on - automatically, safely, reliably, no duplicates.

Upvotes: 2

Related Questions