Returning guid from inserted row in SQL server 2005

[Steve]

Hi All

Can someone help get the rowGUID value after an insert

I am using VB.net 2005 and SQL server Express 2005

The column GUID is an uniqueidentifier column with default value
(newsequentialid())

The following is my current function which is clumsy to say the least

I am sure there is a more elegant solution

Regards

Steve

----------------------------------------------------------------------------------------------------------
Public Function SaveParamDataGetGUID(ByVal mycmd As SqlCommand, ByVal
mytable As String) As Guid

Dim con As New SqlConnection, guid As Guid, sql As String = ""

Dim myint As Int16 = 0

Try

con.ConnectionString = connectionstring

con.Open()

mycmd.Connection = con

mycmd.ExecuteNonQuery()

sql = "select max(mykey) as maxkey from " & mytable

mycmd.CommandText = sql

myint = mycmd.ExecuteScalar

sql = "select guid from " & mytable & " where mykey = " & myint

mycmd.CommandText = sql

guid = mycmd.ExecuteScalar

Return guid



Catch ex As SqlException

mymsgbox(ex.Message)

Return Nothing

Finally

If Not IsNothing(mycmd) Then

mycmd.Dispose()

mycmd = Nothing

End If

If Not IsNothing(con) Then

con.Close()

con.Dispose()

con = Nothing

End If

End Try

End Function

---------------------------------------------------------------------------------------------------------------------------------------------------

 

[Robert]

Can someone help get the rowGUID value after an insert

I am using VB.net 2005 and SQL server Express 2005

The column GUID is an uniqueidentifier column with default value
(newsequentialid())

The following is my current function which is clumsy to say the least

I am sure there is a more elegant solution

I prefer creating the GUID on the client.. This way you can insert a parent
and children in one transaction. No round trip perf issues are a good
thing.

 

[Guest]

sql = "select max(mykey) as maxkey from " & mytable

mycmd.CommandText = sql

myint = mycmd.ExecuteScalar

sql = "select guid from " & mytable & " where mykey = " & myint

mycmd.CommandText = sql

guid = mycmd.ExecuteScalar

Return guid

You should never use select (max) to obtain the last key... because if
concurrent transactions are going on, maxkey may not return the proper
result.

I believe you can fetch the identity by using:

SELECT SCOPE_IDENTITY()

 

[Guest]

I prefer creating the GUID on the client.. This way you can insert a
parent and children in one transaction. No round trip perf issues are
a good thing.

It depends on how you write your SQL statements - you could combine
everything into one statement to avoid the round trip.

Otherwise, using a DAL framework (LLBLGen Pro, NHibernate, Wilson OR/M,
etc) reduces your need to worry about such intracacies.

 

[Steve]

Hi Robert

How do you generate a Sequential GUID on the Client?

VB.net only has GUID class

Regards
Steve

 

[Guest]

I am no expert, but I think the standard way to do this is:

Select @@Identity

as the last statement in your insert sql.

 

[Guest]

Sorry,
That should be:

SELECT MyKey = @@IDENTITY