locked
Error when insert data to sql RRS feed

  • Question

  • hi

    I encountered a problem when entering data, and it is added without problems, but suddenly the number of the auto-numbering field starts with 1000  

    What is the potential mistake ??

     Dim com As New SqlCommand("INSERT INTO client (code,name,company,address,telephon,mail,note,date,active) VALUES (@code,@name,@company,@address,@telephon,@mail,@note,@date,@active)", con) '
                com.Parameters.AddWithValue("@code", CLIENTUPDATE.LAB_CODEVAL.Text)
                com.Parameters.AddWithValue("@name", CLIENTUPDATE.TEXT_CLIENTNAM.Text)
                com.Parameters.AddWithValue("@company", CLIENTUPDATE.TEXT_CLIENTCOMP.Text)
                com.Parameters.AddWithValue("@address", CLIENTUPDATE.TEXT_CLIENTADDRES.Text)
                com.Parameters.AddWithValue("@telephon", CLIENTUPDATE.TEXT_CLIENTTELEPHON.Text)
                com.Parameters.AddWithValue("@mail", CLIENTUPDATE.TEXT_MAIL.Text)
                com.Parameters.AddWithValue("@note", CLIENTUPDATE.TEXT_CLIENTNOT.Text)
                com.Parameters.AddWithValue("@date", CLIENTUPDATE.DATATIM_DATERECOR.Value.ToShortDateString)
                com.Parameters.AddWithValue("@active", True)
                con.Open()
                com.ExecuteNonQuery()
                con.Close()

    Sunday, August 9, 2020 1:53 PM

Answers

  • Hi,
    Carens post and link you can reproduce with following console demo:

    Imports System.Data.SqlClient
    Imports System.ServiceProcess
    
    Module Module1
    
      Sub Main(args As String())
        Try
          If args.Count = 0 Then
            Call (New Demo).Execute1()
          Else
            Call (New Demo).Execute2()
          End If
        Catch ex As Exception
          Console.WriteLine(ex.ToString)
        End Try
        Console.WriteLine("Continue enter key")
        Console.ReadKey()
      End Sub
    
      Friend Class Demo
        Friend Sub Execute1()
          StartSqlService()
          FirstStep()
        End Sub
    
        Friend Sub Execute2()
          StartSqlService()
          SecondStep()
        End Sub
    
        Private Sub FirstStep()
          Using cn As New SqlConnection(My.Settings.cnSQL)
            cn.Open()
            Using cmd As New SqlCommand With {.Connection = cn}
              '
              cmd.CommandText = "ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = ON"
              'cmd.CommandText = "ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF"
              Console.WriteLine($"set Indentity_Cache: {cmd.ExecuteNonQuery()}")
              ' delete previous version
              cmd.CommandText = "If OBJECT_ID('Table1', 'U') IS NOT NULL DROP TABLE IF EXISTS Table1;"
              Console.WriteLine($"delete previous version: {cmd.ExecuteNonQuery()}")
              ' Create Tables
              cmd.CommandText = "CREATE Table [Table1]([ID] int Identity, [ColumnA] int, CONSTRAINT [PK_Table1] PRIMARY KEY ([ID]))"
              Console.WriteLine($"create table: {cmd.ExecuteNonQuery()}")
              Dim par1 As SqlParameter
              With cmd
                .CommandText = "INSERT INTO Table1 (ColumnA) VALUES (@ColumnA);"
                ' define parameters once
                par1 = .Parameters.Add("@ColumnA", SqlDbType.Int)
              End With
              For i = 1 To 2
                par1.Value = i
                Console.WriteLine($"insert row {i}: {cmd.ExecuteNonQuery()}")
              Next
              cmd.CommandText = "SELECT * FROM Table1;"
              Dim rdr = cmd.ExecuteReader
              While rdr.Read
                Console.WriteLine($"{rdr(0)} - {rdr(1)}")
              End While
              rdr.Close()
    
              Dim tr = cn.BeginTransaction
              With cmd
                .Transaction = tr
                .CommandText = "INSERT INTO Table1 (ColumnA) VALUES (@ColumnA);"
              End With
              For i = 3 To 4
                par1.Value = i
                Console.WriteLine($"insert row {i}: {cmd.ExecuteNonQuery()}")
              Next
            End Using
          End Using
          '
          Using cn As New SqlConnection(My.Settings.cnSQL)
            cn.Open()
            Using cmd As New SqlCommand With {.Connection = cn}
              cmd.CommandText = "SHUTDOWN WITH NOWAIT"
              Console.WriteLine($"shutdown: {cmd.ExecuteNonQuery()}")
            End Using
          End Using
          '
          StartSqlService()
          '
          Process.Start("ConsoleApp1.exe", "Step2")
        End Sub
    
        Private Sub SecondStep()
          Using cn As New SqlConnection(My.Settings.cnSQL)
            cn.Open()
            Using cmd As New SqlCommand With {.Connection = cn}
              Dim par1 As SqlParameter
              With cmd
                .CommandText = "INSERT INTO Table1 (ColumnA) VALUES (@ColumnA);"
                ' define parameters once
                par1 = .Parameters.Add("@ColumnA", SqlDbType.Int)
              End With
              For i = 5 To 6
                par1.Value = i
                Console.WriteLine($"insert row {i}: {cmd.ExecuteNonQuery()}")
              Next
              cmd.CommandText = "SELECT * FROM Table1;"
              Dim rdr = cmd.ExecuteReader
              While rdr.Read
                Console.WriteLine($"{rdr(0)} - {rdr(1)}")
              End While
              rdr.Close()
            End Using
          End Using
        End Sub
    
        Private Sub StartSqlService()
          Dim service As ServiceController = New ServiceController("MSSqlServer")
          If ((service.Status.Equals(ServiceControllerStatus.Stopped)) _
              Or (service.Status.Equals(ServiceControllerStatus.StopPending))) Then
            service.Start()
          End If
        End Sub
    
      End Class
    
    End Module


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks


    • Edited by Peter Fleischer Monday, August 10, 2020 3:20 PM
    • Marked as answer by ahmeddc Sunday, September 27, 2020 9:22 PM
    Monday, August 10, 2020 3:15 PM

All replies

  • Hello,

    It's called "jumping", see the following article on why and how to fix it.

    https://blog.sqlauthority.com/2018/01/24/sql-server-identity-jumping-1000-identity_cache/


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, August 9, 2020 4:13 PM
  • Hi,
    Carens post and link you can reproduce with following console demo:

    Imports System.Data.SqlClient
    Imports System.ServiceProcess
    
    Module Module1
    
      Sub Main(args As String())
        Try
          If args.Count = 0 Then
            Call (New Demo).Execute1()
          Else
            Call (New Demo).Execute2()
          End If
        Catch ex As Exception
          Console.WriteLine(ex.ToString)
        End Try
        Console.WriteLine("Continue enter key")
        Console.ReadKey()
      End Sub
    
      Friend Class Demo
        Friend Sub Execute1()
          StartSqlService()
          FirstStep()
        End Sub
    
        Friend Sub Execute2()
          StartSqlService()
          SecondStep()
        End Sub
    
        Private Sub FirstStep()
          Using cn As New SqlConnection(My.Settings.cnSQL)
            cn.Open()
            Using cmd As New SqlCommand With {.Connection = cn}
              '
              cmd.CommandText = "ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = ON"
              'cmd.CommandText = "ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF"
              Console.WriteLine($"set Indentity_Cache: {cmd.ExecuteNonQuery()}")
              ' delete previous version
              cmd.CommandText = "If OBJECT_ID('Table1', 'U') IS NOT NULL DROP TABLE IF EXISTS Table1;"
              Console.WriteLine($"delete previous version: {cmd.ExecuteNonQuery()}")
              ' Create Tables
              cmd.CommandText = "CREATE Table [Table1]([ID] int Identity, [ColumnA] int, CONSTRAINT [PK_Table1] PRIMARY KEY ([ID]))"
              Console.WriteLine($"create table: {cmd.ExecuteNonQuery()}")
              Dim par1 As SqlParameter
              With cmd
                .CommandText = "INSERT INTO Table1 (ColumnA) VALUES (@ColumnA);"
                ' define parameters once
                par1 = .Parameters.Add("@ColumnA", SqlDbType.Int)
              End With
              For i = 1 To 2
                par1.Value = i
                Console.WriteLine($"insert row {i}: {cmd.ExecuteNonQuery()}")
              Next
              cmd.CommandText = "SELECT * FROM Table1;"
              Dim rdr = cmd.ExecuteReader
              While rdr.Read
                Console.WriteLine($"{rdr(0)} - {rdr(1)}")
              End While
              rdr.Close()
    
              Dim tr = cn.BeginTransaction
              With cmd
                .Transaction = tr
                .CommandText = "INSERT INTO Table1 (ColumnA) VALUES (@ColumnA);"
              End With
              For i = 3 To 4
                par1.Value = i
                Console.WriteLine($"insert row {i}: {cmd.ExecuteNonQuery()}")
              Next
            End Using
          End Using
          '
          Using cn As New SqlConnection(My.Settings.cnSQL)
            cn.Open()
            Using cmd As New SqlCommand With {.Connection = cn}
              cmd.CommandText = "SHUTDOWN WITH NOWAIT"
              Console.WriteLine($"shutdown: {cmd.ExecuteNonQuery()}")
            End Using
          End Using
          '
          StartSqlService()
          '
          Process.Start("ConsoleApp1.exe", "Step2")
        End Sub
    
        Private Sub SecondStep()
          Using cn As New SqlConnection(My.Settings.cnSQL)
            cn.Open()
            Using cmd As New SqlCommand With {.Connection = cn}
              Dim par1 As SqlParameter
              With cmd
                .CommandText = "INSERT INTO Table1 (ColumnA) VALUES (@ColumnA);"
                ' define parameters once
                par1 = .Parameters.Add("@ColumnA", SqlDbType.Int)
              End With
              For i = 5 To 6
                par1.Value = i
                Console.WriteLine($"insert row {i}: {cmd.ExecuteNonQuery()}")
              Next
              cmd.CommandText = "SELECT * FROM Table1;"
              Dim rdr = cmd.ExecuteReader
              While rdr.Read
                Console.WriteLine($"{rdr(0)} - {rdr(1)}")
              End While
              rdr.Close()
            End Using
          End Using
        End Sub
    
        Private Sub StartSqlService()
          Dim service As ServiceController = New ServiceController("MSSqlServer")
          If ((service.Status.Equals(ServiceControllerStatus.Stopped)) _
              Or (service.Status.Equals(ServiceControllerStatus.StopPending))) Then
            service.Start()
          End If
        End Sub
    
      End Class
    
    End Module


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks


    • Edited by Peter Fleischer Monday, August 10, 2020 3:20 PM
    • Marked as answer by ahmeddc Sunday, September 27, 2020 9:22 PM
    Monday, August 10, 2020 3:15 PM