If I use scope_identity() ; is it possible that it can return the value of a wrong row if several users are using the application at the same time

Question

I am novice in programming world, so please forgive my ignorance.

I want to retrieve the primary key value of a row I just inserted. The primary key values are automatically generated. I want to insert that primary key value into another table as a foreign key.

This is the code to insert data in 1st table

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

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

In the above code the id which is the primary key is automatically generated. I want to retrieve that value to insert it as surveyid into 2nd table.

Core.DB.DoQuery("insert into surveyquestioncategory(title, detail, surveyid) values(@title, @detail, @sid)", 
                Core.DB.SIP("title", categoryTitle.Text), 
                Core.DB.SIP("detail", categoryDetail.Text), 
                Core.DB.SIP("sid", Survey.ID))

And by retrieving the id of 2nd table wan to insert the value on 3rd table.

To retrieve the id value of recently inserted row we can use

Core.DB.DoQuery("SELECT SCOPE_IDENTITY() AS MostRecentID")           

As this is an web application and several users are using the application at the same time is it possible that SCOPE_IDENTITY() can return value of wrong row. If so how can I avoid that?

Please forgive my ignorance about programming.

Appreciate your help.

Thanks Bashabi

Answer 1

Using OUTPUT statement you can do it in one query or create procedure for this

INSERT INTO survey (id, title, detail, employerid, userid) 
OUTPUT @title, @detail, inserted.ID INTO surveyquestioncategory (title, detail, surveyid)
VALUES (@id, @title, @detail, @eid, @uid);

From MSDN OUTPUT Clause (Transact-SQL)