Reseed identity column in database using localDB(MDF) in visual studio

  • 309 Views
  • Last Post 07 May 2020
Riyadh Bandar posted this 05 May 2020

I'm trying to build a project using for database, but while deleted some rows the ID column is showing nonsequential numbers, is there any solution to create code to reseed ID column "value 1".

Thanks..

Order By: Standard | Newest | Votes
lucy posted this 06 May 2020

Can you describe more details?

Riyadh Bandar posted this 06 May 2020

Ok then...I'll make this close, I made database using MS access target, then while deleting any row, I call query that is created in same access as like this (ALTER TABLE [EQ_Serv] ALTER COLUMN [ID] COUNTER(1,1) ), so the calling that I mad  by this is code :


using System.Data.OleDb;

public class DB_Query
{
    public static void EQS_Query()
    {
        OleDb.OleDbConnection conn = new OleDb.OleDbConnection($@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Data\EquipmentRecordDB.accdb;Persist Security Info=True");
        OleDbCommand cmd = new OleDbCommand() { CommandType = CommandType.StoredProcedure, CommandText = $"EQ_Query", Connection = conn };
        conn.Open();
        cmd.ExecuteNonQuery();
        cmd.Dispose();
        conn.Close();
    }
}

, so my weldering is here with MDF a localDB it's different for call trough "StoredProcedure" so my questions :

1- how to create script inside MDF by only using Visual Studio for Reseed the auti number of ID.

2-  what does it looks ike the final code in C#.

I hope this will be clear... Thanks

 

lucy posted this 07 May 2020

You can run the sql script below to modify your table

ALTER TABLE [EQ_Serv] DROP COLUMN [ID]

ALTER TABLE [EQ_Serv] ADD ID INT IDENTITY NOT NULL PRIMARY KEY

Close