Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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 8:32am
Comments
RedDK at 17-Feb-13 13:45pm
   
Aren't identities unique? Isn't "identity" the soul of the word "identifier"? I'm probably wrong.
Rate this: bad
good
Please Sign up or sign in to vote.

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.
  Permalink  
v6
Comments
Prathap Gangireddy at 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 at 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 at 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 at 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 at 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 at 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 at 18-Feb-13 8:23am
   
Yeah, you got a point. I will try to negotiate and convince them.Thanks mate.
Mike Meinz at 18-Feb-13 8:26am
   
Tell them you received advice from a software developer with 46 years of experience.
Prathap Gangireddy at 18-Feb-13 8:30am
   
Sure :-)
Rate this: bad
good
Please Sign up or sign in to vote.

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.
 
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
  Permalink  
Comments
Prathap Gangireddy at 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 at 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.

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

  Print Answers RSS
0 OriginalGriff 350
1 Jochen Arndt 150
2 PIEBALDconsult 110
3 Richard MacCutchan 105
4 Sergey Alexandrovich Kryukov 104
0 OriginalGriff 6,055
1 DamithSL 4,621
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,500
4 Sergey Alexandrovich Kryukov 3,294


Advertise | Privacy | Mobile
Web04 | 2.8.141220.1 | Last Updated 18 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100