CODEWITHSUNDEEP
asp.netsql-servervb.netprimary-keyguid
Bashabi
Bashabi

Reputation: 706

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

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.

enter image description here

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.

Upvotes: 4

Views: 1175

Answers (2)

Aijaz Chauhan
Aijaz Chauhan

Reputation: 1649

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

Upvotes: 1

M.Ali
M.Ali

Reputation: 69564

SCOPE_IDENTITY() will only return the newly generated Identity value if there is any, for GUID values you would need a table variable with OUTPUT clause in your insert statement something like this.....

DECLARE @NewIDs TABLE (ID UNIQUEIDENTIFIER)

insert into survey(id,title, detail,employerid,userid) 
OUTPUT inserted.id INTO @NewIDs(ID)
values(@id,@title, @detail, @eid, @uid);

SELECT * FROM @NewIDs

Upvotes: 1

Related Questions