954,560 Members — Technology Publication meets Social Media
Username:
Password:
Lost login information?
Have something to say? Contribute New Article Reply to this Article

Website plagued by Identity Theft in SQL

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

J'Tok
Light Poster
39 posts since Jan 2008
Reputation Points: 22
Solved Threads: 0
 

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

Oxiegen
Master Poster
715 posts since Jun 2006
Reputation Points: 87
Solved Threads: 141
 

This article has been dead for over three months

Post: Markdown Syntax: Formatting Help
You
View similar articles that have also been tagged: