To get a sequential varchar column in database

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi All,

Below is my requirement.

I have an Identity column in a table called QuoteID and another column QuoteNo.So depending on the QuoteID the QuoteNo should also be displayed in the front end. For eg : i need something like if QuoteID=1 QuoteNo= QNO20000101 ; QuoteID=2 QuoteNo=QNO20000102.

I have done achived this in the front end like getting the max(QuoteID) from database and converting it to int and adding it to 20000100.So that the value keeps on increasing.

I would like to know if two users are saving the data into database.Will there be any problem like same QuoteNo for QuoteID 1 and QuoteID 2.Becuase QuoteID is an identity column so only the textbox text i.e. QuoteNo will be saved in a new row.

Please suggest a better way to achieve the same if my approach is not that good.

Posted 17-Feb-13 7:32am

Prathap Gangireddy

Add a Solution

Comments

RedDk 17-Feb-13 13:45pm

Aren't identities unique? Isn't "identity" the soul of the word "identifier"? I'm probably wrong.

2 solutions

Solution 2

Hi Prathap Gangireddy,

For this you will keep one systemPeram Table and keep Next Quotation No (Running Number).

While Give the data to Front end use following method to frame the string and show.

SQL

SELECT 'QNO'+CAST(20000100+NextQNo AS VARCHAR(10)) FROM sysPeram WHERE Code='QNo'
-- Update SysPeram Table for next QuotationNo

UPDATE sysPeram SET NextQNo=NextQNo+1 WHERE Code='QNo'

Regards,
GVPrabu

Posted 17-Feb-13 22:20pm

gvprabu

Comments

Prathap Gangireddy 18-Feb-13 7:40am

Hi Prabhu,
What is user wants to create a new Quotation, then he has to click the New button right.Then I have no choice but to display the quoteID and quoteNo in the frontEnd.What I have done is when user is saving the details I am checking whether for this ID and NO if already a row exists so that in the front end I am showing a message like conflict with the existing ID and No.Click new to get a New ID and New no.How bout this idea.Does this sound good?

gvprabu 20-Feb-13 1:22am

Hi Prathap,

After generate the QuotationNo then only you will show the Quotation NO, Else in Get SP you will get the Details for example "200001012" for New Quotation, In the same time another user also get the same value for his New Quotation Generation "200001012". If he saved first then you will get error else you save the Quotation he will get error. So If possible change the process to show the Quotation No Once Quotation Registration is Completed.

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)

Mike Meinz · Accepted Answer · 2013-02-17T12:34:00

Solution 1

Use SCOPE_IDENTITY not Max(QuoteID).

Max(QuoteID) will cause problems when simultaneous users create new quotes. It will work most of the time but it will give the wrong results if there are simultaneous INSERT statements from different users.

* See Retrieving Identity or Autonumber Values (ADO.NET)[^] in the Visual Studio Help.
SCOPE_IDENTITY
Returns the last identity value within the current execution scope. SCOPE_IDENTITY is recommended for most scenarios.

* See SCOPE_IDENTITY [^] in the Visual Studio Help.
Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

Posted 17-Feb-13 12:34pm

Mike Meinz

Updated 17-Feb-13 13:16pm

v6

Comments

Prathap Gangireddy 18-Feb-13 0:34am

Hi Mike, Thanks for the help.I almost forgot about scope_identity.But don't you think this will also give wrong results.Because I am getting the value of the identity column in the front end when I click the New button and not while inserting.So when 2 users are clicking on the new button, it will display the same QuoteID and QuoteNo because the data is not yet inserted.I think you got my question.Can you advise on this.

Mike Meinz 18-Feb-13 7:09am

You must insert the new quote first and then get the SCOPE_IDENTITY. Do not show the new QuoteNo to the user until after they have submitted a new quote to be saved in the database. The display of the QuoteNo after the insert is the user's confirmation that the quote has been saved.

NEWQUOTE Stored Procedure
Create a stored procedure to do the INSERT using the values passed from the front-end, get the SCOPE_IDENTITY and then UPDATE QuoteNo with value computed from the SCOPE_IDENTITY value. Have the stored procedure return the computed QuoteNo to the front-end.

Front-End
Gather information from the user. When the user clicks SUBMIT, call the NEWQUOTE stored procedure and pass data to be inserted into the database. When the stored procedure returns the QuoteNo to the front-end, display that to the user as the QuoteNo that was assigned.

Prathap Gangireddy 18-Feb-13 7:39am

What is user wants to create a new Quotation, then he has to click the New button right.Then I have no choice but to display the quoteID and quoteNo in the frontEnd.What I have done is when user is saving the details I am checking whether for this ID and NO if already a row exists so that in the front end I am showing a message like conflict with the existing ID and No.Click new to get a New ID and New no.How bout this idea.Does this sound good?

Mike Meinz 18-Feb-13 7:56am

Best Practice
Clicking on your NEW QUOTE button should just give the user a blank form to fill in with the quote data. Clicking on the SUBMIT button, saves the new quote data and assigns the QuoteNo.

Further Explanation
If you display the QuoteNo to user before they have entered and saved data, you run the risk that the user will not finish entering the new quote data and you will be left with an orphan quote row in the database. If you follow my guidance and only display the QuoteNo to the user after they have entered the new quote data and clicked on SUBMIT to save, you will have less problems. You will not have to check if the QuoteID exists because the QuoteID (IsIdentity column) is guaranteed unique. The display of the new QuoteNo after the new quote data is saved serves to confirm to the user that the new quote has been saved in the database. Also, there is no reason to show the user the QuoteID.

Prathap Gangireddy 18-Feb-13 8:11am

I understand your approach.But the client requirement is that I need to show both the QuoteID and QuoteNo.Regarding (If you display the QuoteNo to user before they have entered and saved data, you run the risk that the user will not finish entering the new quote data and you will be left with an orphan quote row in the database) this, if the user doesn't save the data then the same QuoteID and QuoteNo will be displayed because no data is saved in the database.Hence no problem.

Mike Meinz 18-Feb-13 8:20am

I strongly suggest that you renegotiate the client requirement. It is your duty as a professional programmer to guide the client to best practices. It is not best practice to display a computed value before the transaction is completed. Displaying the QuoteNo first requires more software development effort and could cause problems. For example, what if there is an empty quote in the database caused by a prior abandoned transaction and two users access that row at the same time to update it as a new quote?

Prathap Gangireddy 18-Feb-13 8:23am

Yeah, you got a point. I will try to negotiate and convince them.Thanks mate.

Mike Meinz 18-Feb-13 8:26am

Tell them you received advice from a software developer with 46 years of experience.

Prathap Gangireddy 18-Feb-13 8:30am

Sure :-)