Generate Auto increment number for each group in ASP.Net MVC

bigbear
 
on Jun 16, 2019 11:47 PM
Sample_799165.zip
2771 Views

Hello everyone,

I am splitting my question up into parts so it ll be easier for everyone including myself.

I have a database table with certain columns a group which is like a category and last4numbers which goes from 0-9999 when at 9999 the category is full.

I have a viewmodel that is bascally my db table plus a few extra properties. I have a textbox that has a number and this number is how manytimes this record needs to be added. Each record increments 1 off the last entered last4numbers. I’m using entity framework so I can call my context and do context.add(entity) to create a record.

What are some recommend ways that I can add a new record in the database which the column last4numbers is just +1 off the last record added. All the records will be in the same part group but I need to see if I have x numbers available in the table. Some records skip around so this is where the hard part comes in to make sure the number is available. I get the last4number biggest number with a list orderby Last4Number descending that gives me the biggest last one used.

        //Actual model
        public int ID { get; set; }
        [Required]
        [Display(Name = "Part Book / Category")]
        public string PartBook { get; set; }
        public string PartGroup { get; set; }
        public string Last4Number { get; set; }        
        public string PartNextNumber { get; set; }
        [Display(Name = "Today's Date")]
        public DateTime DateEntered { get; set; }       
        public string Manufacturer { get; set; }
        [Display(Name = "Manufacturer Part Number")]
        public string ManufacturerPartNumber { get; set; }
        [Required]
        [CheckDropDown]
        public string Usage { get; set; }
        [Display(Name = "Required Project(s)")]
        public string RequiredProject { get; set; }
        [Range(0, Int32.MaxValue)]
        //[DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:#}")]
        public int Quantity { get; set; }
        public string Units { get; set; }

 

//view  
@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()
    @Html.DropDownListFor(x => x.PartBook, (IEnumerable<SelectListItem>) ViewBag.PartBookDDL, "", new { @class = "form-control" })
    <div class="col-md-3 col-lg-3">
        @Html.TextBox("txtNumberNeeded", null, new { @class = "form-control", @type = "number" })
    </div>
    <input type="submit" />

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jun 17, 2019 07:54 AM

Hi bigbear,

For inserting based on your TextBox you need to first get the last4number from database. Then increment the number with 1 and loop through the record upto the TextBox value and insert in database.

dharmendr
 
on Jun 18, 2019 06:44 AM
on Jun 18, 2019 06:54 AM

Hi bigbear,

For inserting based on your TextBox you need to first get the last4number from database. Then increment the number with 1 and loop through the record upto the TextBox value and insert in database.

Check this example. Now please take its reference and correct your code according to you table structure.

SQL

CREATE TABLE CategoryDetails(Id INT IDENTITY PRIMARY KEY,Category VARCHAR(50),Last4Numbers CHAR(4))

Model

public class ViewModel
{
    public int ID { get; set; }
    [System.ComponentModel.DataAnnotations.Required]
    [System.ComponentModel.DisplayName("Part Book / Category")]
    public string PartBook { get; set; }
    public string Last4Number { get; set; }
}

Controller

public class HomeController : Controller
{
    // GET: /Home/
    public ActionResult Index()
    {
        TempData["PartBookDDL"] = GetPartBooks();
        TestEntities entities = new TestEntities();
        TempData["Records"] = entities.CategoryDetails.ToList();
        return View();
    }

    [HttpPost]
    public ActionResult Index(int txtNumberNeeded, string PartBook)
    {
        TempData["PartBookDDL"] = GetPartBooks();
        TestEntities entities = new TestEntities();
        List<SelectListItem> positionList = GetPartBooks();
        string category = positionList.Find(p => p.Value == PartBook).Text;
        int lastNumber = 0;
        if (entities.CategoryDetails.Where(x => x.Category == category).Count() > 0)
        {
            lastNumber = Convert.ToInt32(entities.CategoryDetails.Where(x => x.Category == category).Max(x => x.Last4Numbers));
            lastNumber++;
        }
        for (int i = lastNumber; i < txtNumberNeeded + lastNumber; i++)
        {
            if (i != 9999)
            {
                entities.CategoryDetails.AddObject(new CategoryDetail { Category = category, Last4Numbers = i.ToString() });
                entities.SaveChanges();
            }
        }

        TempData["Records"] = entities.CategoryDetails.ToList();
        return View("Index");
    }

    // Get list from database.
    public List<SelectListItem> GetPartBooks()
    {
        List<SelectListItem> partBooks = new List<SelectListItem>();
        partBooks.Add(new SelectListItem { Text = "Category 1", Value = "1" });
        partBooks.Add(new SelectListItem { Text = "Category 2", Value = "2" });
        partBooks.Add(new SelectListItem { Text = "Category 3", Value = "3" });
        partBooks.Add(new SelectListItem { Text = "Category 4", Value = "4" });

        return partBooks;
    }
}

View

<%@ Page Language="C#" Inherits="System.Web.Mvc.ViewPage<_Add_Record_If_Available_MVC.Models.ViewModel>" %>

<%@ Import Namespace="_Add_Record_If_Available_MVC" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Index</title>
</head>
<body>
    <div>
        <%using (Html.BeginForm("Index", "Home", FormMethod.Post))
          { %>
        <%:Html.LabelFor(x => x.PartBook)%> : 
        <%:Html.DropDownListFor(x => x.PartBook,(IEnumerable<SelectListItem>) TempData["PartBookDDL"], "Select", new { @class = "form-control" })%>
        <div class="col-md-3 col-lg-3">
            Last Number : <%:Html.TextBox("txtNumberNeeded", null, new { @class = "form-control", @type = "number" })%>
        </div>
        <input type="submit" /><br /><br />
        <%} %>
        <%if (TempData["Records"] != null)
          {%>
        <table>
            <tr>
                <td>Category</td>
                <td>Last4Numbers</td>
            </tr>
            <%for (int i = 0; i < ((List<CategoryDetail>)TempData["Records"]).Count; i++)
              {%>
            <tr>
                <td><%=((List<CategoryDetail>)TempData["Records"])[i].Category %></td>
                <td><%=((List<CategoryDetail>)TempData["Records"])[i].Last4Numbers %></td>
            </tr>
            <% } %>
        </table>
        <% } %>
    </div>
</body>
</html>

Screenshot