I am working in VS2008. I use vb.net and asp.net. MS SQL2005 as database server.

Occasionally when two sets of data are inserted at nearly the same time, I find that one has "stomped" on the other, over-writing it's data. The inserts are done via a stored proc using SCOPE_IDENTITY() to retrieve the last identity value when necessary.

Below is an example of how I connect to SQL server and insert the data.
NOTE - Conn is imported from a class and looks something like this (in psudocode):
Conn As New SqlConnection With {.ConnectionString = ConfigurationManager.ConnectionStrings("DB").ConnectionString}

Using myConnection As New System.Data.SqlClient.SqlConnection(Conn.ConnectionString)
            Using myCommand As New System.Data.SqlClient.SqlCommand(InsertString, myConnection)
                Dim retvalue As Integer
                retvalue = myCommand.ExecuteNonQuery()
            End Using
        End Using

The only thing I can think of is that the connections are being shared, instead of being created for each insert. Does this sound right? I'm running out of ideas.
Do I just need to make sure each connection is truly unique (since SCOPE_IDENTITY does not leave a given connection), or is it something else?

Any ideas, comments, suggestions would be greatly appreciated.

Thanks in advance,

7 Years
Discussion Span
Last Post by Oxiegen

Have you tried creating a Webservice as an intermediate between your site and the dB-server?
Perhaps that will eliminate the problem.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.