Datebase query based on serial number in sql server

M M

Hello,

I am working on a C# application where i have to store values of article in sql server as i have 3 products (stone, diamonds, jewellery). Now during the initial process when i enter the product details let say i have to store value for a diamonds i put in the required details, now i want that in the database a sno value should be stored as a increment counter(D001) next time when i enter details for diamond the sno should be automatically stored as 002. Same is the case with stone , jewellery.

Diamonds: D001, D003, D003.....
Jewellery : J001, J002, J003.....
Stone: S001, S002, S003.....these all should be stored as sno in same database table. is it possible. Please help me through. waiting for are reply.Thanks in anticipation

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Senthilkumar14y ago
Hi,

you can use the stored procedure to insert these things into the table.

Whatever i said in the previous reply follow the same. Because it must need some kind of logic to generate the alphanumeric auto increment for type wise.

Writing the stored procedure is not a big deal, just you need to pass the input parameter from the code.

Hope this will help you.
M M14y ago
additon info i am using dropdown for selecting S,D,J value:- my insert statement is like
string qry = "insert into GemStone (Receipt,DTPbDay,ItemType,Weight,Client,Address,Contact,Amount,OriginalPath,ImageData) values(@Receipt,@DTPbDay,@ItemType,@Weight,@Client,@Address,@Contact,@Amount,@OriginalPath, @ImageData)"; whr ItemType is the type of article(S,D,J)
now the thing is i want to add these details + individual value for artcle type(like D001, D001) for each article on daily basis. if details of diamond is added with this statement above wht should i add to acheive serial number for each item type. Bit confusing....
brunda k14y ago
we can also create sequence ,please find attached URL
http://technet.microsoft.com/en-us/magazine/hh407114.aspx
Senthilkumar14y ago
Hi,

I do not think you have any straight forward way to do this.

You need to have table some thing like this.

CREATE TABLE AlphaIncrement
(
SNO VARCHAR(900) PRIMARY KEY,
Name VARCHAR(100),
Type VARCHAR(100),
CreatedDate DATETIME DEFAULT GETDATE()
)

INSERT INTO AlphaIncrement(SNO,Name,Type) VALUES('D1','D1','D')
INSERT INTO AlphaIncrement(SNO,Name,Type) VALUES('J1','J1','J')

Whenever you insert then you need to follow this logic.

DECLARE @Last VARCHAR(100)
DECLARE @Next INT
SELECT @Last = MAX(SNO) FROM AlphaIncrement WHERE Type='D'
SET @Next = REPLACE(@Last,'D','') + 1

INSERT INTO AlphaIncrement(SNO,Name,Type) VALUES('D'+CONVERT(VARCHAR,@Next),'D2','D')

Hope this will help you.
14y ago
You can only auto increment numeric values by default in SQL - your code would need to set your primary key based on the last key in the database, then making the transaction.