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

  • 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.

    Thursday, August 13, 2015 8:26 AM

All replies