By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,843 Members | 2,311 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,843 IT Pros & Developers. It's quick & easy.

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

P: 24
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
Nov 5 '09 #1
Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
Identity auto-increment itself. Why do you need to add one? Anyway, you might want to do a
Expand|Select|Wrap|Line Numbers
  1. isnull(IDENT_CURRENT('ExampleTable'),0)
  2.  
Good luck!!!

--- CK
Nov 5 '09 #2

P: 24
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
Nov 6 '09 #3

ck9663
Expert 2.5K+
P: 2,878
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
Nov 6 '09 #4

nbiswas
100+
P: 149
Try with @@Identity or Scope_Identity()
Nov 8 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.