SQL Server, how to the next value of identity(1,1) before insert

[qwedster]

Folks,

How to the next value of identity(1,1) before insert?

SELECT IDENT_CURRENT('ExampleTable') + IDENT_INCR('ExampleTable');
--does not work when the table is empty!

USE [master]
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = 'ExampleDatabase')
DROP DATABASE [ExampleDatabase];
GO

CREATE DATABASE [ExampleDatabase];
GO

USE [ExampleDatabase];
GO

IF EXISTS (SELECT NAME FROM SYS.TABLES WHERE NAME = 'ExampleTable')
DROP TABLE dbo.ExampleTable;
GO

CREATE TABLE
dbo.ExampleTable
(
ID INT IDENTITY(1,1) NOT NULL,
Name NVARCHAR(50) NULL
);
GO

SELECT * FROM dbo.ExampleTable;
GO

--FAILS IN THIS CASE:
SELECT IDENT_CURRENT('ExampleTable') + IDENT_INCR('ExampleTable');
GO

INSERT INTO ExampleTable
VALUES
(
'Bill'
);
GO

SELECT * FROM dbo.ExampleTable;
GO

--WORKS IN THIS CASE:
SELECT IDENT_CURRENT('ExampleTable') + IDENT_INCR('ExampleTable');
GO

INSERT INTO ExampleTable
VALUES
(
'Steve'
);
GO

SELECT * FROM dbo.ExampleTable;
GO

--WORKS IN THIS CASE:
SELECT IDENT_CURRENT('ExampleTable') + IDENT_INCR('ExampleTable');
GO

[ck9663]

Identity auto-increment itself. Why do you need to add one? Anyway, you might want to do a

isnull(IDENT_CURRENT('ExampleTable'),0)
 

Good luck!!!

--- CK

[qwedster]

Hi!

It doesn't help, because I want to query from C# code. Every time when I want add a row, I call this method to get the next identity value like:

private int GetNextRowID()
{
int NextRowID =0;

try
{

using (SqlConnection ConnectionSql = new SqlConnection(ConnectionString))
{
StringBuilder SqlQuery = new StringBuilder();

SqlQuery.Append("USE [ExampleDatabase]; ");

// Returns 1 so works for the first insert when the table is empty before any insert:
// Returns 1 again for the second insert, so it FAILS:
SqlQuery.Append("SELECT ISNULL(IDENT_CURRENT('ExampleTable'), 0) ;");

SqlCommand CommandSql = new SqlCommand(SqlQuery.ToString(), ConnectionSql);

ConnectionSql.Open();

SqlDataReader DataReaderSql = CommandSql.ExecuteReader();

while (DataReaderSql.Read())
{
NextRowID = int.Parse(DataReaderSql.GetValue(0).ToString());
}

ConnectionSql.Close();

MessageBox.Show("Add Row.");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
return NextRowID;
}

Thanks


@ck9663

[ck9663]

You might want to reconsider your technique. If this is for a multi-user environment, you'll have a problem when you access your app and assign the next ID to you and someone else access it, the app will assign the same ID to him. So better reconsider everything.

Good luck!!!

--- CK

[nbiswas]

Try with @@Identity or Scope_Identity()