0

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
2
Contributors
1
Reply
2
Views
5 Years
Discussion Span
Last Post by adam_k
0

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.