How to store the value of last inserted id(PK) into a variable where the primary key is a GUID in VB.Net

Question

My question is related to the question asked in here How to get last inserted id?

But the scope_identity() will not work for me as in my case the primary key value for the table is a GUID value.

Also I have seen the question in here SQL Server - Return value after INSERT

but the link does not explain how can i store the value in a variable. I need to store the value in a variable which I can use for multiple entry.

I am inserting hard coded value into multiple SQL Server tables. All the primary key columns are GUID.

The table structure are as follows.

This is the code I use to insert data into survey table.

 Protected Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click

    Dim survey = Guid.NewGuid().ToString()

    Dim SurveyTitle As String = "Diversity Monitoring Survey"
    Dim SurveyDetail As String = ""

    Core.DB.DoQuery("insert into survey(id,title, detail,employerid,userid) values(@id,@title, @detail, @eid, @uid);", Core.DB.SIP("title", SurveyTitle), Core.DB.SIP("detail", SurveyDetail), Core.DB.SIP("eid", LocalHelper.UserEmployerID()), Core.DB.SIP("uid", LocalHelper.UserID()), Core.DB.SIP("id", survey))

 End Sub

Where DoQuery is

Shared Sub DoQuery(ByVal commandText As String, ByVal ParamArray params As SqlParameter())
    Dim conn As SqlConnection = Nothing

    Try
        conn = GetOpenSqlConnection()
        DoQuery(conn, commandText, params)
    Finally
        If conn IsNot Nothing Then conn.Dispose()
    End Try
End Sub

Now I want to retrieve of just inserted SurveyId value and store it into a variable strSurveyId

    Dim strSurveyID As String 

So that I can insert that value in the table SurveyQuestionCategory:

    Core.DB.DoQuery("insert into surveyquestioncategory(title, detail, surveyid) values(@title, @detail, @sid)", Core.DB.SIP("title", strSurveyQuestionCategoryTitle), Core.DB.SIP("detail", strSurveyQuestionCategoryDetail), Core.DB.SIP("sid", strSurveyID))

scope_identity() will not work in my case as the the is GUID.

I have tried this

        SELECT * from [MPBlLiteDev].[dbo].[Survey] where id=SCOPE_IDENTITY()

But it gives me a error Operand type clash: uniqueidentifier is incompatible with numeric

Please suggest with code.

Answer 1

Please go through the below stored procedure

Create proc SPInsertSurvey
(
   @Title varchar(MAX),
   @OutSurveyID UNIQUEIDENTIFIER output
)
as
DECLARE @Table TABLE (SurveyID UNIQUEIDENTIFIER)
begin

   insert into survey(Title) 
   Output inserted.SurveyID into @Table values (@Title)

   set @OutSurveyID =(select SurveyID from @Table)

end

You can execute it by using below Syntax

DECLARE @return_value int,
        @OutSurveyID uniqueidentifier

EXEC    @return_value = [dbo].[SPInsertSurvey]
        @Title = N'''S1''',
        @OutSurveyID = @OutSurveyID OUTPUT

SELECT  @OutSurveyID as N'@OutSurveyID'

SELECT  'Return Value' = @return_value

Hope this will help