How to increment serial number in sql server

Posted by Delhiegg under Sql Server on 4/4/2012 | Points: 10 | Views : 4956 | Status : [Member] | Replies : 6
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




Responses

Posted by: Sakthi.Singaravel on: 4/4/2012 [Member] Silver | Points: 25

Up
0
Down
Hi,

can i know u use, seprate database for each or single database..?


Regards,
Singaravel M

Delhiegg, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 4/4/2012 [Member] Silver | Points: 25

Up
0
Down
While you call the procedure to insert a record along with product details pass the categoryid , like S,D or J
taking on the input count max value of that category and then increment the value by adding 1 and store the new value as
Categoryid+increment value.

Thanks,
Sanjay

Delhiegg, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Delhiegg on: 4/5/2012 [Member] Starter | Points: 25

Up
0
Down

@ Sakthi i want to use single database for that.

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....
 Download source file

Delhiegg, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Yuvaopens on: 4/5/2012 [Member] Starter | Points: 25

Up
0
Down
Just use if loop and for loop to print the previous data upto 9 and then move on with the two digit.

for(i =0; i<=9; i++)
{
if(previous record)
{
"D00" + a;
a= a+1;
} else if(previous record)
{
"D0" +a;
a = a+1;
} else if(previous record)
{
"D" +a;
a=a+1;
}


Yuvaraj.S

Delhiegg, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sakthi.Singaravel on: 4/5/2012 [Member] Silver | Points: 25

Up
0
Down

i understand ur expectation..

can u send ur code..?


Regards,
Singaravel M

Delhiegg, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Prabhakar on: 4/5/2012 [Member] [MVP] Starter | Points: 25

Up
0
Down
Hi Sakthi..

try to this . . in SQL.. use MAX function for Maximum no of ur Columns . .

Try it . .

set @nextdiamond  = isnull(( select max(Diamonds) from tablename where ssno=@ssno ),0) + 1


Best Regard's
Prabhakar

Delhiegg, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response