Hi Guys,

I wrote a function that returns the number in a particular field, my issue at the moment is how to update that field before i return the previous value i collected from the field. i am seeking any guidance available. Enclosed is the function i wrote.

Function GetTransactionNumber() As Int64
        Try
            GConn.ConnectionString = GConnstr
            GConn.Open()
            sqlcmd = New SqlCommand("Select Last_Transaction_Num from System_Table", GConn)
            sqlreader = sqlcmd.ExecuteReader()
            While sqlreader.Read
                Return sqlreader.GetInt64(0)
            End While
            sqlreader.Close()
            GConn.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        'Return int64_TranNum
    End Function

I'd use the OUTPUT clause with an update. You can find more information here: http://msdn.microsoft.com/en-us/library/ms177564.aspx

I'd suggest you read the whole page, as there are a few drawbacks, ie:

An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back. The result should not be used if any error occurs when you run the statement.

Depending on the number of updates and the isolation level you might want to stick the whole thing in a stored procedure with a transaction.