CODEWITHSUNDEEP
.netsqlsql-servervb.netsql-server-2008
Maysam
Maysam

Reputation: 7367

IDENT_CURRENT returns NULL

Here in my code DR.HasRows() returns True, but DR("LastID") returns DBNUll while SELECT IDENT_CURRENT ('SiteSection') AS 'LastID' works in SQL Management Studio.

__SQLString= "SELECT IDENT_CURRENT ('SiteSection') AS 'LastID'"
Dim DR As SqlDataReader
ddConnect() 'It's my custom class that opens a connection to db, it works
DR = ddExecuteReader()
DR.Read()
If DR.HasRows() Then
    GetLastID = DR("LastID")
End If

Upvotes: 1

Views: 5779

Answers (4)

Baloo0ch
Baloo0ch

Reputation: 43

In case it return null because no row inserted before use following code, it assume seed value is one :

     SELECT COALESCE((SELECT top(1) IDENT_CURRENT('TableName')),0) +1 as NextId

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280500

I suspect that the account your .NET application is using does not have the same permissions as the account you are using to connect via Management Studio.

That said, I find your approach questionable at best. What are you planning to do with the information that comes from IDENT_CURRENT()? If you think that you can take that and make assumptions about what IDENTITY value is going to be generated for an INSERT that you have not performed yet, please think again. Several factors can invalidate that assumption - someone can reseed the identity in the meantime, or change the increment, or insert a bunch of rows.

So I suggest you reconsider what exactly you need to do and why you think IDENT_CURRENT is the way to do it. Based on your comment, you should be sending:

INSERT ... ; SELECT LastID = SCOPE_IDENTITY();

Now you can be sure that this is the ID you've inserted.

Upvotes: 3

Muhammad Hani
Muhammad Hani

Reputation: 8664

Check this .. Ident_Current returning NULL on SQL 2005

to use ident_current or any of the other functions to get the last identity, the user needs to have permissions to read meta data, so it needs db_onwer on that database.

Upvotes: 2

Victor Zakharov
Victor Zakharov

Reputation: 26454

From MSDN:

In SQL Server, a user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as IDENT_CURRENT may return NULL if the user does not have any permission on the object.

You may find this interesting:

When the IDENT_CURRENT value is NULL (because the table has never contained rows or has been truncated), the IDENT_CURRENT function returns the seed value.

Upvotes: 2

Related Questions