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
myConnection.Open()
retvalue = myCommand.ExecuteNonQuery()
Console.WriteLine(retvalue)
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,
JTok