Need help in generating order Number

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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 7-Nov-13 22:35pm

Snehasish00

Updated 7-Nov-13 22:59pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Garth J Lancaster · Answer 1 · 2013-11-07T22:48:00

Solution 1

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'

Posted 7-Nov-13 22:48pm

Garth J Lancaster

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:

Thanks7872 · Answer 2 · 2013-11-07T23:13:00

Solution 2

See this : http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server[^]

Regards..

Posted 7-Nov-13 23:13pm

Thanks7872