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('Exa mpleTable');
--does not work when the table is empty!

USE [master]
GO

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

CREATE DATABASE [ExampleDatabase];
GO

USE [ExampleDatabase];
GO

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

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

SELECT * FROM dbo.ExampleTabl e;
GO

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

INSERT INTO ExampleTable
VALUES
(
'Bill'
);
GO

SELECT * FROM dbo.ExampleTabl e;
GO

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

INSERT INTO ExampleTable
VALUES
(
'Steve'
);
GO

SELECT * FROM dbo.ExampleTabl e;
GO

--WORKS IN THIS CASE:
SELECT IDENT_CURRENT(' ExampleTable') + IDENT_INCR('Exa mpleTable');
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]

It doesn't help, because I want to query from C# code

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(C onnectionString ))
{
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_CU RRENT('ExampleT able'), 0) ;");

SqlCommand CommandSql = new SqlCommand(SqlQ uery.ToString() , ConnectionSql);

ConnectionSql.O pen();

SqlDataReader DataReaderSql = CommandSql.Exec uteReader();

while (DataReaderSql. Read())
{
NextRowID = int.Parse(DataR eaderSql.GetVal ue(0).ToString( ));
}

ConnectionSql.C lose();

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

Thanks

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

[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( )