Locking A Record in SQL Server until you are done with it

[matt23lucier]

This is what I am trying to do:
I have about 10 shipping stations that will be referencing the same SQL table (called shipper). This table only has one field (called shipNumber). This field is an integer value. These shipping stations will need to query this table to retrieve the current value of shipNumber and then increase the number by one. The problem I have is that I need to lock this record for the duration of selecting and increasing the shipNumber. Does anyone know how I can handle this?
Thanks
Matt

 

[MacLeod72]

Is this value to be used as a primary key in another table? You might consider looking into using a column with the identity Property set to Yes. This will automatically increment for you.

--Rob

 

[matt23lucier]

No this column is not going to be part of a primary key in any other table. The sole purpose of this field is to be a unique number that a shipping station can retrieve and then increment. I am just curious on how to lock the table until a particular shipping station is done selecting and increasing the number.
Thanks
Matt

 

[chrissie1]

wrap the two in a transaction no need for locking.

You can either do this wit a stored procedure with transaction

or use the vb transaction.

Christiaan Baes
Belgium

"My new site" - Me

 

[Kliot]

Matt,

As Christiaan says wrap the query in a transaction something like this.

               Dim myTransaction As SqlClient.SqlTransaction, myQuery As String

        Try

            myConnection.Open()
            myTransaction = myConnection.BeginTransaction

            objCommand.Transaction = myTransaction

            ' Get the Current Value
            myQuery = "SELECT QUERY STRING"
            objCommand.CommandText = myQuery
            newNumber = CInt(objCommand.ExecuteScalar())

           ' Update the Value by 1
            myQuery = "UPDATE NewNumbers SET KeyValue = KeyValue + 1 etc..."
            objCommand.CommandText = myQuery
            objCommand.ExecuteNonQuery()

            myTransaction.Commit()
        Catch
            MsgBox("Cannot Obtain a New Numberr, Please try again.")
        End Try

Perrin