How to store the value of scope_identity() into a variable in VB.Net

Question

User-270722712 posted

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

an example of the table structures and relationship are as follows.

Table 1 : Survey

SurveyID	Title
1	S1
2	S2
3	S3
4	S4

Table 2 : Survey Question Category

SurveyQuestionID	Title	SurveyID
1	S1	1
2	S2	1
3	S3	2
4	S4	2

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

Now I want to use scope_identity()  to retrieve of just inserted SurveyID  value and store it into a variable strSurveyID

 Dim strSurveyID As String 
    Dim strSurveyQuestionCategoryTitle As String = "Race Ethnicity"
    Dim strSurveyQuestionCategoryDetail 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))

But I don't know how to use scope_identity()  to retrieve the id and store it into the variable. Please suggest with code.

Answer 1

User753101303 posted

Hi,

Try : http://stackoverflow.com/questions/4413178/executescalar-with-scope-identity-generating-system-invalidcastexception (C# but applies as well to VB).

The basic idea is to follow the SQL insert statement with a SELECT that returns the identity value (or even other server side generated column) that you can then read as any other SELECT.

BTW Entity Framework handles this for you: https://msdn.microsoft.com/en-us/data/ef.aspx , it allows to expose data as .NET objects to your application.

Answer 2

User-270722712 posted

Scope_identity will not work for me as in my case the ID is GUID

Also I want to store the value in a variable to use it for multiple entry.