Getting and Displaying 'Next available number'

[modbuilder]

I am stumped on this one. I have a winforms application in .Net 4.5. I need to generate a 'project number', however this is not at random, it is the next available number.

What stumps me is how to get the number, and display it in a textbox in the format I need.

Basically, end user will open the form from within the main application, and click the button conveniently labeled "Generate Project Number", the application will get the next available number from the database, in this instance the table name being "Tracking". If this was simply a number, I wouldn't be having issues. The number is displayed YY-####. YY being the two digit year, then #### being the next available number.

As we will be implementing in 2015, our first number would be 15-0001, then 15-0002 etc. Some guidance is highly appreciated.

[Arjay]

How you retrieve the number from the database depends on how it is stored in the database. If it is an identity column, be careful there is something to be aware of if you try to guess the next number. That is, a failed insert will eat up the next identity number. In other words, if you have 10 records in the database, try to insert another record and it fails and then go to guess the next number, it might end up being 12 instead of 11 depending on the database.

Of course, you won't have this problem if the tracking table only has one row with a value that you increment. If this is the case, just retrieve the incremented number from the database and then format the string on the client.

var trackingNumber = 15; // retrieve from database

var display = String.Format("{0}-{1:0000}", DateTime.Now.Year.ToString(CultureInfo.InvariantCulture).Substring(2), trackingNumber);

This approach will work for another 85 years.

[modbuilder]

How you retrieve the number from the database depends on how it is stored in the database. If it is an identity column, be careful there is something to be aware of if you try to guess the next number. That is, a failed insert will eat up the next identity number. In other words, if you have 10 records in the database, try to insert another record and it fails and then go to guess the next number, it might end up being 12 instead of 11 depending on the database.

Of course, you won't have this problem if the tracking table only has one row with a value that you increment. If this is the case, just retrieve the incremented number from the database and then format the string on the client.

var trackingNumber = 15; // retrieve from database

var display = String.Format("{0}-{1:0000}", DateTime.Now.Year.ToString(CultureInfo.InvariantCulture).Substring(2), trackingNumber);

This approach will work for another 85 years.

Thank you for the response. It is not particularly the year prefix I am worried about, it is the trailing numbers. Stored in the database now my table is layed out as the following:

	
[id] [int] IDENTITY(1,1) NOT NULL,
[type] [char](2) NOT NULL,
[value] [bigint] NOT NULL,

The identity doesn't affect much of anything, the current values in here for example purposes are:

Type Value
PD 4537
SN 891

How the number generation works now is end-user clicks an assign button for lets say the SN number, the following is run:

        public static int GetNextSNNumber() {
            SqlCommand command = new SqlCommand("dbo.usp_General_Tracking_Get_Next_Value_By_Type", m_SqlConnection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@p_Type", "SN");
            command.Parameters.Add("@p_Value", SqlDbType.Int, 4).Direction = ParameterDirection.Output;

            Sql.ExecuteNonQuery(command);

            return (int)command.Parameters["@p_Value"].Value;
        }

The stored procedure is as follows:

ALTER PROCEDURE [dbo].[usp_dbo.usp_General_Tracking_Get_Next_Value_By_Type]
	@p_Type	CHAR(2),
	@p_Value	INT OUTPUT
AS
	SET NOCOUNT ON

	SET @p_Value = (SELECT value FROM Tracking WHERE type=@p_Type)
	UPDATE HB10Tracking_005 SET value=value+1 WHERE type=@p_Type

So the stored procedure gets the value by type, sets the value, then adds 1 digit to that number.

I should have explained the process better from the start. What we are doing is eliminating 1 project having two different numbers, so the "type" PN for project number will replace SN and PD with the new format of the year and the trailing digits.

Where I am stuck is the string for the year (YY format) and the actual value of the next available number (####). When we start in 2015, the first number being 0001, in which I am getting '15-1' no matter what I try, as oppose to '15-0001'.

[Arjay]

Where I am stuck is the string for the year (YY format) and the actual value of the next available number (####). When we start in 2015, the first number being 0001, in which I am getting '15-1' no matter what I try, as oppose to '15-0001'.

My code snippet should do what you are asking. Have you tried running the code and looking at the output? P.S. The output of my snippet if run today will be:

14-0015

If you change the tracking number to 3, it will output:

14-0003

If you run it next year and the tracking number is 106, it will output:

15-0106

If you need to retrieve the year from the database (or convert existing codes into a year), then just replace the DateTime.Now part of the String.Format with the desired value.