Last post Aug 14, 2015 06:48 AM by bashabi
Aug 13, 2015 08:26 AM|bashabi|LINK
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
Table 2 : Survey Question Category
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))
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.
Aug 13, 2015 08:59 AM|PatriceSc|LINK
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.
Aug 14, 2015 06:48 AM|bashabi|LINK
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.