Custom Auto increment?

Denmark Puso

7y4.1k.NET

i searched for the solution for custom increment with prefix. im using mvc5 asp.net. my problem is to generate like this.

in example 1 as you can see we have 4 zero, and if we reach the example 2 we have 3 zero,

please help how to format like this.

example 1:

ORD-00001

ORD-00002

.....

example2

ORD-00010

....

example3

ORD-00100

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Priyanka Jain7y ago
Accepted answer
I tried code in mvc to insert in database from controller as below,
using mvcapplication.Models;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web.Mvc;
namespace mvcapplication.Controllers
{
    public class OrderController : Controller
    {
        private SqlConnection con;
        //To Handle connection related activities
        private void connection()
        {
            string constr = @"";
            con = new SqlConnection(constr);

        }
        // GET: orderloyee/AddOrder
        public ActionResult AddOrder()
        {
            return View();
        }

        // POST: orderloyee/AddOrder
        [HttpPost]
        public ActionResult AddOrder(OrderModel order)
        {
            try
            {
                if (ModelState.IsValid)
                {
                    if (AddOrderDetails(order))
                    {
                        ViewBag.Message = "Order added successfully";
                    }
                }

                return View();
            }
            catch(Exception e)
            {
                return View();
            }
        }

        //To Add orderloyee details
        public bool AddOrderDetails(OrderModel obj)
        {

            connection();
            SqlCommand com = new SqlCommand("AddNewOrder", con);
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.AddWithValue("@Test", obj.Test);
            con.Open();
            int i = com.ExecuteNonQuery();
            con.Close();
            if (i >= 1)
            {

                return true;

            }
            else
            {

                return false;
            }
        }

    }
}
and create table as ,
CREATE TABLE [Order]
(
                Id int identity not null primary key,
                AutoId VARCHAR(15),
                Test VARCHAR(10)
)

GO

CREATE FUNCTION NextOrderNumber()
RETURNS CHAR(10)
AS
BEGIN
    DECLARE @lastval INT
    SET @lastval = (SELECT MAX(Id) FROM [Order])
    IF @lastval IS NULL SET @lastval = 0
    RETURN 'ORD' + RIGHT('0000'+ CONVERT(VARCHAR(5),(@lastval+1)),5)
END
GO

ALTER TABLE [Order] ADD CONSTRAINT df_Order_AutoId DEFAULT dbo.NextOrderNumber() FOR AutoId
GO
and sp as below,
Create procedure [dbo].[AddNewOrder]
(
   @Test varchar (50)
)
as
begin
   Insert into [Order] (Test) values (@Test)
End
and it displayed result as
Denmark Puso7y ago
Hi!
Priyanka Jain
Thank you agein it works fine now :-) your a big help
Denmark Puso7y ago
hi!
Priyanka Jain
thank you again for response. i didnt include ReferenceNumber in my insert function since its autogenerated right? and that error show this is my Code.
else
                   {
                       var order = new Order
                       {
                           ClientId = model.ClientId,
                           StateId = model.StateId,
                           CityId = model.CityId,
                           TownId = model.TownId,
                           Postcode = model.Postcode,
                           Address = model.Address,
                           DatePosted = Convert.ToDateTime(model.DatePosted),
                           DeliveryDate = Convert.ToDateTime(model.DeliveryDate),
                           Quantity = model.Quantity,
                           Status = "Lined-up",
                           DateDelivered = null,
                           Notes = model.Notes ?? ""
                       };

                       _orderService.Add(order);
                       TempData["msg"] = "";
                       return RedirectToAction("add");
                   }
Priyanka Jain7y ago
Are you passing your ReferenceNumber column along with your insert query?
Denmark Puso7y ago
Hi! Amit Mohanty

if used Priyanka Jain said, i got this error in my software. but when i manually insert in sql in works fine. you mean that Priyanka said is for sql only? im using asp.net mvc

The column "ReferenceNumber" cannot be modified because it is either a computed column or is the result of a UNION operator.
Denmark Puso7y ago
Hi!
Priyanka Jain
Thank you im moving forward now :-) one last question what i if the number reach 6 digits what happen if my autoId i set into 5 dgits?
Priyanka Jain7y ago
For existing record you can manually update existing records and for new record you can add below code
CREATE TABLE [Order]
(
                Id int identity not null primary key,
                AutoId VARCHAR(8),
                Test VARCHAR(10)
)

GO

CREATE FUNCTION NextOrderNumber()
RETURNS CHAR(10)
AS
BEGIN
    DECLARE @lastval INT
    SET @lastval = (SELECT MAX(Id) FROM [Order])
    IF @lastval IS NULL SET @lastval = 1
    RETURN 'ORD' + RIGHT('0000'+ CONVERT(VARCHAR(5),(@lastval+1)),5)
END
GO

ALTER TABLE [Order] ADD CONSTRAINT df_Order_AutoId DEFAULT dbo.NextOrderNumber() FOR AutoId
GO
Denmark Puso7y ago
Hi!
Priyanka Jain
my table already created how can i update my table like what you said without affecting my data?

im new bee in programming :-)
Amit Mohanty7y ago
Hey if u are using sql server then you can use Priyanka Jain's solutions and if you want it in frontend then use Amit Gupta's solutions.
Priyanka Jain7y ago
You can easily define your requirement in SQLServer as below
CREATE TABLE #Order
(
                Id INT NOT NULL IDENTITY(1,1),
                AutoId AS 'ORD' + RIGHT('0000'+ CONVERT(VARCHAR(5),Id),5)
)
while inserting record output will be displayed as below
Amit Gupta7y ago
You can use PadRight function string num ="1"; var result = "ORD-"+ num.PadRight(5, '0');

Custom Auto increment?

Priyanka Jain

Priyanka Jain

Priyanka Jain

Priyanka Jain