quote:

---

Originally posted by X002548

you are going to need to figure out how to hold a row lock until AFTER your row is inserted

Do you have a unique constraint on that column in the destination table?

---

quote:

---

Originally posted by X002548

so what's the primary key of your data table?

ClientID, NextNo?

I sure wonder why you would care about a cash receipt number being sequential..sounds like a bad idea...amount being posted for the wrong client..reports forget the client id...

Still..it all comes down to locking the rows...

And you don't find that this could be a bottle neck?

---

Using myConnection As New SqlConnection(LoginClass.getConnectionString)

    myConnection.Open()

    Dim transaction As SqlTransaction = myConnection.BeginTransaction

    Dim myCommand As SqlCommand = myConnection.CreateCommand

    myCommand.Transaction = transaction

    'Transaction 1 //////////////////////////////////////////////////////////

    sql = "UPDATE NextNumber SET @Next = LastReceiptNo = LastReceiptNo + 1 WHERE SN=@SN"
                        
    myCommand.CommandText = sql

    Dim NextNoParam As SqlParameter = myCommand.Parameters.AddWithValue("Next", 0)

    NextNoParam.Direction = ParameterDirection.Output

    myCommand.Parameters.AddWithValue("SN", sn)

    n = myCommand.ExecuteNonQuery() 'returns number of rows affected

    If n = 1 Then

        'Transaction 2 //////////////////////////////////////////////////////////

        'cleanup
        myCommand.Parameters.Clear()

        sql = "INSERT INTO Receipts ReceiptNo VALUES @ReceiptNo WHERE SN=@SN SET @ID = SCOPE_IDENTITY()"

        Dim IDParam As SqlParameter = myCommand.Parameters.AddWithValue("ID", -1)
        myCommand.Parameters.AddWithValue("SN", sn)
        myCommand.Parameters.AddWithValue("ReceiptNo", NextNoParam.Value)

        myCommand.CommandText = sql

        n = myCommand.ExecuteNonQuery() 'returns number of rows affected

        If n = 1 Then
            ret = IDParam.Value & "|" & NextNoParam.Value
            transaction.Commit()
        Else
            transaction.Rollback()
        End If

        'end of Transaction 2 //////////////////////////////////////////////////////////

    Else
        transaction.Rollback()
    End If

End Using