Custom Auto increment?

Question

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

Priyanka Jain · 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

Amit Gupta · Answer

You can use PadRight function string num ="1"; var result = "ORD-"+ num.PadRight(5, '0');

Priyanka Jain · Answer

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

Priyanka Jain · Answer

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

Denmark Puso · Answer

Hi! Priyanka Jain Thank you agein it works fine now :-) your a big help

Denmark Puso · Answer

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 Jain · Answer

Are you passing your ReferenceNumber column along with your insert query?

Denmark Puso · Answer

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 Puso · Answer

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?

Denmark Puso · Answer

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 Mohanty · Answer

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.

Custom Auto increment?

Answers (11)