Inserting multiple records from vb.net misses out inserts

• ian.worcester

  Valued Member

  Points: 61

  More actions

  April 23, 2010 at 8:40 am

  #386775

  Hi All,

  I don't know if it's just me (Highly likely) but i have an app in vb.net that reads a SQL table and appends to SQL 2008 by firing single insert statements for each append to the underlying database.

  The initial issue i had was that i would append parent records followed by a few child records but it was occassionally breaking, saying the parent didn't exist so it couldn't append the child but when i stepped through all parents and children appended correctly therefore leading me to believe that it was somehow tripping over itsself.

  To alleviate the problem i put a 20 ms delay after each parent insert and this has sorted it (Although not really a satisfactory solutuion in my eyes)

  i have now come across children that are sometimes missed out due to the same issue in that it seems to just get ahead of itself and miss them out.

  I am not sure if this is a SQL server issue or a VB.net issue i am using SQL 2008 and VB 2008. both are located on my development machine running windows 7.

  In the example below the code exists in a dll and iterates through the child objects of the parent object. the connection, transaction and parent object are passed through as variables

  an example of the code i am using is:

  Sub StampTransactionItems(ByRef conn As SqlConnection, _

  ByRef trn As SqlTransaction, _

  ByRef Trans As TransHeader)

  Dim cmd As New SqlCommand

  Dim strSQL As String = ""

  Dim ti As TransItem

  For Each ti In Trans.TransItems

  strSQL = "INSERT INTO ... Insert Statement that appends a single child to the passed parent object that has already been appended"

  cmd.Connection = conn

  cmd.CommandType = CommandType.Text

  cmd.CommandText = strSQL

  cmd.Transaction = trn

  cmd.ExecuteScalar()

  Next

  cmd = Nothing

  ' Put in place as the system was falling over because the child record was not recognising the

  ' fact that the parent record had been appended. Having a small delay seems to rectify this

  ' although it Is Not ideal

  Delay(20)

  End Sub

  Any help would be appreciated.

  Thanks,

  Ian