How do i get the highest ID number of a table and increment it?

Question

I want to get the highest ID number in the table and increment it but it seems to return 0 with how i'm doing it.

private int MaxAuthorValue()
{
    string connectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Library;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        string maxQuery = "SELECT MAX(AuthorId) FROM Author";
        SqlCommand command = new SqlCommand(maxQuery, connection);
        int x = (int)command.ExecuteScalar();
        return x + 1;
    }
}

What I want to do with this is return the inremented ID so that the user can add an Author and doesnt have to write in the ID themself, as that would be pretty unsafe to do (for example if an author have ID=1 and they try to add another author with the same ID number it would create an error.)

Edit: Adding SQLServer table creation code

CREATE TABLE [dbo].[Author] 
(
    [AuthorId] INT NOT NULL IDENTITY(1,1),
    [Name] NVARCHAR (50) NOT NULL,
    [Nationality] NVARCHAR (50) NOT NULL, 
    PRIMARY KEY CLUSTERED ([AuthorId] ASC)
);

_"doesnt seem to work"_ it's not a right way to describe problem. What you expect to get and what you get? — SᴇM, Nov 27 '17 at 13:13
Don't increment the value yourself. Use an `identity` column. — Gordon Linoff, Nov 27 '17 at 13:15
For what purpose are you doing this? It's entirely possible for this value to change after you read it, so you need Transactions to be safe. It's also entirely possible that you don't need to do this, as databases handle autoincrement / identity columns natively. — MatBailie, Nov 27 '17 at 13:16
"What is a purpose" is a secondary question, first let's explain him, why is he getting wrong value, then explain that there is other ways to reach that result. — SᴇM, Nov 27 '17 at 13:17
@anek05 - As you say that this is for inserting a new row with a new and unique id, ***Don't Do It This Way***. Check out `IDENTITY` columns in SQL Server. SQL Server then allocates the new id for you, and you avoid race conditions and a hundred other problems. — MatBailie, Nov 27 '17 at 13:20
Do you want the user to enter the ID or auto generate it automatically so that no two users have the same ID? — Mohamed Najiullah, Nov 27 '17 at 13:20

Mohamed Najiullah · Accepted Answer · 2017-11-27T13:59:53.030

1

If the table isn't very big yet, I would suggest you to drop and recreate the table with

CREATE TABLE dbo.<YOUR-TABLE-NAME>
(
  Id int NOT NULL
         IDENTITY(1, 1),
  --Other Column names and types
)
ON  [PRIMARY]
GO

If not, then add a new column and use it as your ID

ALTER <YOUR-TABLE-NAME>
ADD <NEW-ID-COLUMN-NAME> INT IDENTITY(1, 1)
GO

And then delete your existing ID column

Edit: You are also trying to insert value for the Identity Column. You should not insert any value for AuthorId. That is you should just insert values for the remaining columns except AuthorId.

edited Nov 27 '17 at 13:59

answered Nov 27 '17 at 13:38

Mohamed Najiullah

403
5
12

How do i turn on IDENTITY_INSERT? I get "Cannot insert explicit value for identity column in table Author when IDENTITY_INSERT is set to OFF" `CREATE TABLE [dbo].[Author] ( [AuthorId] INT NOT NULL IDENTITY(1,1), [Name] NVARCHAR (50) NOT NULL, [Nationality] NVARCHAR (50) NOT NULL, PRIMARY KEY CLUSTERED ([AuthorId] ASC) );` – anek05 Nov 27 '17 at 13:47
Can you post the SQL code that you used to create the table? – Mohamed Najiullah Nov 27 '17 at 13:50
`CREATE TABLE [dbo].[Author] ( [AuthorId] INT NOT NULL IDENTITY(1,1), [Name] NVARCHAR (50) NOT NULL, [Nationality] NVARCHAR (50) NOT NULL, PRIMARY KEY CLUSTERED ([AuthorId] ASC) );` – anek05 Nov 27 '17 at 13:51
I see the error you're making. I'm adding additional information to my answer – Mohamed Najiullah Nov 27 '17 at 13:57
Thanks this fixed it! – anek05 Nov 28 '17 at 09:48

score 0 · Answer 2 · answered Nov 27 '17 at 13:27

0

You can use IDENT_CURRENT('<tablename>') to get the last provided identity of that table

It will give you the last one over all scopes and sessions which is important - other users might have open transactions with new inserts in theire session. A select max(id_column) from ... works by default only in in your session.

If you want the next id, use IDENT_INCR ( 'table_or_view' )

Much smarter is an automaticly assigned ID - you just dont provide the ID column in your insert and SqlServer does the right thing for you: Autoincrementing Identities, see f.e. here: Auto increment primary key in SQL Server Management Studio 2012

answered Nov 27 '17 at 13:27

Patrick Artner

50,409
9
43
69

I would welcome feedback with downvotes - I might adjust my answer accordingly. – Patrick Artner Nov 27 '17 at 15:15
I feel that your answer is the closest to what the OP asked. What he originally wanted was to have no duplicate IDs in the AuthorID column and I believe he didn't realise something like marking a column as `Identity` existed. – Mohamed Najiullah Nov 27 '17 at 15:22

How do i get the highest ID number of a table and increment it?

2 Answers2