Click here to Skip to main content
15,905,971 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi all

In my table(Form Settings) i have 3 columns
Prefix
Suffix and
SerialNo

Now in my Order from i have to add this 3 and generate Order Number
e.g. ORD-2001-XYZ
so for each order only SerialNo i.e 2001 will increment.
Please tell me how to do this..

Now what i did is i have 3 sql query and i stored the result for each in a variable and at end doing like this

VB
Dim concat As String = a + "-" + c + "-" + b

and assigning the value to OrderNo textbox.

But next record it will not work..

So please tell me how to increment the Number Value each time.

Thank you
Posted
Updated 7-Nov-13 22:59pm
v2

Two thoughts (since you dont say which DB you're using I cant be more specific)

1) instead of SerialNo per se (or even in another table) have an auto increment integer primary key - insert into the table every time, and select the highest record's key/ID to get the next 'SerialNo', with conversion to string and trim to the required digits you require - the cons are the table grows

2) use a trigger/procedure to increment a value

3) Oracle : use a sequence

'g'
 
Share this answer
 
Comments
Snehasish00 8-Nov-13 5:00am    
Oh..I missed that..sorry..i am using SQL SERVER 2008..and for your 1st point, i want to say that that's the format i have to follow to generate Order Number..no other option..
Garth J Lancaster 8-Nov-13 5:03am    
SQL Server 2012 has 'sequences' :-) Im not sure about 2008 .. if not, one of the first two options is going to be the general sort of thing - there's plenty of help out there on google for this, btw
Thanks7872 8-Nov-13 5:15am    
I think you missed to use reply button at OP's comment :-)
Garth J Lancaster 8-Nov-13 5:39am    
bvgger ! oh well - sorry, Fri night, tough week (not over yet by a long shot), tired
Thanks7872 8-Nov-13 5:40am    
:laugh:
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900