Change Identity seeding value from code behind using C# and VB.Net in ASP.Net

fahimahmed
 
on Sep 23, 2019 12:37 AM
Sample_385723.zip
528 Views

How to change identity in asp.net C#/Vb.net behind code?

I will run scheudle task to change 1909000 to 1910000 on every month change

CREATE TABLE [dbo].[Courier] (
    [Id]                   INT           IDENTITY (1909000, 1) NOT NULL,
    [Invoice]              VARCHAR (MAX) NULL,
    [Courier]              VARCHAR (MAX) NULL,
    [AWBNumber]            VARCHAR (MAX) NULL,
    [DispatchEmail]        VARCHAR (MAX) NULL,
    [DispatchFullDateTime] VARCHAR (MAX) NULL,
    [DispatchDate]         DATE          NULL,
    [DispatchTime]         TIME          NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Download FREE API for Word, Excel and PDF in ASP.Net: Download
pandeyism
 
on Sep 23, 2019 12:40 AM

I will get back soon.

pandeyism
 
on Sep 23, 2019 07:27 AM

Hi fahimahmed,

Refer below sample code.

HTML

 <asp:Button Text="Insert" runat="server" OnClick="Insert" />

Namespaces

C#

using System.Data.SqlClient;
using System.Configuration;

VB.Net

Imports System.Data.SqlClient
Imports System.Configuration

Code

C#

protected void Insert(object sender, EventArgs e)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    SqlConnection con = new SqlConnection(constr);
    SqlCommand cmd = new SqlCommand("SELECT MAX(Id) Id FROM Courier", con);
    con.Open();
    int id = Convert.ToInt32(cmd.ExecuteScalar());
    int getMonth = Convert.ToInt32(id.ToString().Substring(2, 2));
    string reseedQuery = "";
    if (DateTime.Today.Month > getMonth)
    {
        string newId = DateTime.Today.Year.ToString().Substring(2, 2) + "0" + DateTime.Today.Month + "999";
        reseedQuery = "DBCC CHECKIDENT ('Courier', RESEED, " + newId + ") ";
        reseedQuery += " INSERT INTO Courier VALUES (@Name)";
        cmd = new SqlCommand(reseedQuery, con);
        cmd.Parameters.AddWithValue("@Name", "Test");
        cmd.ExecuteNonQuery();
    }
    else
    {
        reseedQuery += " INSERT INTO Courier VALUES (@Name)";
        cmd = new SqlCommand(reseedQuery, con);
        cmd.Parameters.AddWithValue("@Name", "Test");
        cmd.ExecuteNonQuery();
    }
    con.Close();
}

VB.Net

Protected Sub Insert(ByVal sender As Object, ByVal e As EventArgs)
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim con As SqlConnection = New SqlConnection(constr)
    Dim cmd As SqlCommand = New SqlCommand("SELECT MAX(Id) Id FROM Courier", con)
    con.Open()
    Dim id As Integer = Convert.ToInt32(cmd.ExecuteScalar())
    Dim getMonth As Integer = Convert.ToInt32(id.ToString().Substring(2, 2))
    Dim reseedQuery As String = ""
    If DateTime.Today.Month > getMonth Then
        Dim newId As String = DateTime.Today.Year.ToString().Substring(2, 2) & "0" + DateTime.Today.Month & "999"
        reseedQuery = "DBCC CHECKIDENT ('Courier', RESEED, " & newId & ") "
        reseedQuery += " INSERT INTO Courier VALUES (@Name)"
        cmd = New SqlCommand(reseedQuery, con)
        cmd.Parameters.AddWithValue("@Name", "Test")
        cmd.ExecuteNonQuery()
    Else
        reseedQuery += " INSERT INTO Courier VALUES (@Name)"
        cmd = New SqlCommand(reseedQuery, con)
        cmd.Parameters.AddWithValue("@Name", "Test")
        cmd.ExecuteNonQuery()
    End If
    con.Close()
End Sub