Retrieve @@IDENTITY 1

[Keyth]

Hi Guys.

I know a lot of you who read this will say "That thread belongs in 'SQL Server Programming'" but if I post this thread in there, they may not know what I am on about because its VB code thats calling the command...

I have a problem where I am trying to call the following SQL command on an SQL Server 2005 db but for some reason its returning DBNull??!! I have used this block of code hundreds of times but now finally have a mental block and feel like throwing the whole lot of the nearest pier.

        Dim Sql As String = "SELECT @@IDENTITY"
        Dim cmdIdentity As New SqlCommand(Sql, sqlCn)
        Dim returnval As Integer 'Var to hold new ID ref
        Dim previousConnectionState As ConnectionState = sqlCn.State

        If sqlCn.State = ConnectionState.Closed Then
            sqlCn.Open()
        End If
        Try
            returnval = cmdIdentity.ExecuteScalar()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            cmdIdentity.Dispose()
        End Try

        If previousConnectionState = ConnectionState.Closed Then
            sqlCn.Close()
        End If

I call this directly after a successfull new row insert on the database but for some reason I still get DBNull back as ID. I've checked structure of DB table and it does have Key/Index so what am I missing?

All help offered is very much appreciated.

Regards,
Keyth

Come on you reds.

 

[gmmastros]

First... Since you are using SQL Server, do [!]NOT[/!] use @@Identity. It can return unexpected results if you have a trigger on the table that insert rows in to another table that also has an identity column. Instead, use Scope_Identity(), like this...

Dim Sql As String = "SELECT Scope_Identity()"

>> I've checked structure of DB table and it does have Key/Index so what am I missing?

Having a Key/Index is not good enough. The table must have an identity column. You can only have 1 identity column per table, but you can have multiple keys and indexes.

To see the tables/columns in your database that that have an identity column....

[COLOR=blue]Select[/color] Table_Name, 
       Column_Name, 
       ColumnProperty([COLOR=#FF00FF]Object_ID[/color](Table_Name), Column_Name, [COLOR=red]'IsIdentity'[/color])
[COLOR=blue]From[/color]   Information_Schema.Columns
[COLOR=blue]Where[/color]  ColumnProperty([COLOR=#FF00FF]Object_ID[/color](Table_Name), Column_Name, [COLOR=red]'IsIdentity'[/color]) = 1
[COLOR=blue]Order[/color] [COLOR=blue]By[/color] Table_Name, Column_Name

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Keyth]

Thanks George

The table does have one ID column which is Primary Key

I'll try what you suggest.

thanks

Come on you reds.

 

[gmmastros]

Primary Keys are not necessarily identity columns. @@Identity and Scope_Identity() only work if there is an identity column. Also... Identity columns do not have to be primary keys either.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Keyth]

Hi gmmastros.

I've tried that but it still returns DBNull, any more ideas?

Thanks

Come on you reds.

 

[gmmastros]

Try running the sql query I posted. Does it show your table in the list?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Keyth]

Yeah its in the list..

COMPLAINTS COMPLAINT_ID 1

Come on you reds.

 

[AlexCuse]

Are you positive your connection remains open throughout the process? SCOPE_IDENTITY() gets the last identity inserted by commands run on the currently open connection. So if you're not doing an insert first, you won't be seeing any result. I'd also not be surprised if this got somehow messed up by ADO.net.

My advice - put you insert into a stored proc, and have it return SCOPE_IDENTITY(). That way its just one command, easier to maintain, etc...

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[gmmastros]

Well... the simple solution is to not allow people to complain. If there are no complaints, then you don't need a table. No table, no identity column, no problem.

Seriously though, you're right. It's a VB code problem.

Personally, I usually do my inserts using a stored procedure. In the stored procedure, I insert in to the table and also return the identity value. So, it would look something like this....

Create Procedure InsertComplaint
  @ReportedBy Int,
  @Complaint VarChar(8000)
AS
SET NOCOUNT ON

Insert Into Complaints(ReportedBy, Complaint, ComplaintDateTime)
Values (@ReportedBy, @Complaint, GetDate())

Select Scope_Identity() As Complaint_ID

Then, from the VB side, you call the stored procedure in the same way that you would call a stored procedure that returns a result set. In this case, you will get 1 row with 1 column named Complaint_ID.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Keyth]

AlexCuse: The connection does remain open thanks.

LOL George! The update is performed successfully via a dataadaptor because my "complaints" form has a lot of bound controls. Its saved me a lot of time binding rather than creating the updates etc. Puzzling thing is that other tables return ID without problem but the complaints table does not which is why I thought it may be a table related problem. Maybe its something to do with my adaptor... I'll keep looking %-)


Come on you reds.

 

[RonRepp]

Keyth:

George is right (as usual).

I ran into the same problem a few months back and he straightened me out.

However, using a data adapter (which I stay away from like bad cliches) is just another control. Using an actual Stored Procedure will give you not only the access through VB, but if you ever need to test it, you can EXEC it through Query Analyzer.

George, a star for you.


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net