EDN Admin
Well-known member
I am trying to check for concurrency when updating a record in the database. Here is my current VB.NET code and Stored Procedure code to do the update.
When a user changes a Status column value the DataGridView CellValidating event is fired and in that event it trys to update the record in the database using the stored procedure. The update works, but my SqlDataAdapter doesnt update dtmUpdateDatetime
in dtResults. My UpdateDatabase function uses dtmUpdateDatetime to check for concurrency. Im probably not doing this right so I open to suggestions and advice. Thanks in advance!
<div style="color:Black;background-color:White; <pre>
<span style="color:Blue; Public <span style="color:Blue; Class frmCalculatedResults
<span style="color:Blue; Private dtCriteria <span style="color:Blue; As <span style="color:Blue; New DataTable
<span style="color:Blue; Private bs <span style="color:Blue; As <span style="color:Blue; New BindingSource
<span style="color:Blue; Private clsDGV <span style="color:Blue; As <span style="color:Blue; New clsDataGridView
<span style="color:Blue; Private da <span style="color:Blue; As SqlDataAdapter
<span style="color:Blue; Private dtResults <span style="color:Blue; As DataTable
<span style="color:Blue; Private <span style="color:Blue; Function prvfnc_CalculateRBF() <span style="color:Blue; As <span style="color:Blue; String
<span style="color:Blue; Using cnn <span style="color:Blue; As <span style="color:Blue; New SqlClient.SqlConnection(clsDBConn.clsDBConnections.prpConnString)
cnn.Open()
<span style="color:Blue; Using cmd <span style="color:Blue; As <span style="color:Blue; New SqlClient.SqlCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = <span style="color:#A31515; "[dbo].[ssp_CalculateRBCF]"
<span style="color:Blue; Me.da = <span style="color:Blue; New SqlDataAdapter(cmd)
<span style="color:Blue; Me.da.Fill(<span style="color:Blue; Me.dtResults)
<span style="color:Blue; End <span style="color:Blue; Using
<span style="color:Blue; End <span style="color:Blue; Using
<span style="color:Blue; Return <span style="color:#A31515; "Success"
<span style="color:Blue; End <span style="color:Blue; Function
<span style="color:Blue; Private <span style="color:Blue; Sub dgvCalculatedResults_CellValidating(<span style="color:Blue; ByVal sender <span style="color:Blue; As <span style="color:Blue; Object, <span style="color:Blue; ByVal e <span style="color:Blue; As System.Windows.Forms.DataGridViewCellValidatingEventArgs) <span style="color:Blue; Handles dgvCalculatedResults.CellValidating
<span style="color:Blue; If <span style="color:Blue; Me.dgvCalculatedResults.IsCurrentCellDirty <span style="color:Blue; Then
<span style="color:Blue; Dim rwIndex <span style="color:Blue; As <span style="color:Blue; Integer = <span style="color:Blue; Me.dtResults.Rows.IndexOf(<span style="color:Blue; DirectCast(<span style="color:Blue; Me.dgvCalculatedResults.CurrentRow.DataBoundItem, DataRowView).Row)
<span style="color:Blue; Dim clIndex <span style="color:Blue; As <span style="color:Blue; Integer = <span style="color:Blue; Me.dtResults.Columns(<span style="color:Blue; Me.dgvCalculatedResults.Columns(e.ColumnIndex).Name).Ordinal
<span style="color:Blue; With <span style="color:Blue; Me.dtResults.Rows(rwIndex)
.BeginEdit()
<span style="color:Green; ensure DataRowState is set to modified, if user hits the Enter key it sets row modified, if the user mouse clicks another cell the row is not set modified
<span style="color:Blue; If .RowState.<span style="color:Blue; Equals(DataRowState.Unchanged) <span style="color:Blue; Then .SetModified()
.Item(clIndex) = e.FormattedValue
<span style="color:Blue; If <span style="color:Blue; Me.UpdateDatabase() = <span style="color:#A31515; "Failed" <span style="color:Blue; Then
.RejectChanges()
<span style="color:Blue; Else
.AcceptChanges()
<span style="color:Blue; End <span style="color:Blue; If
<span style="color:Blue; End <span style="color:Blue; With
<span style="color:Blue; End <span style="color:Blue; If
<span style="color:Blue; End <span style="color:Blue; Sub
<span style="color:Blue; Private <span style="color:Blue; Function UpdateDatabase() <span style="color:Blue; As <span style="color:Blue; String
<span style="color:Blue; Try
<span style="color:Blue; Me.Cursor = Cursors.WaitCursor
<span style="color:Blue; If <span style="color:Blue; Me.da.UpdateCommand <span style="color:Blue; Is <span style="color:Blue; Nothing <span style="color:Blue; Then
<span style="color:Blue; Me.da.UpdateCommand = <span style="color:Blue; New SqlCommand(<span style="color:#A31515; "dbo.usp_CalculatedAnalytes")
<span style="color:Blue; Me.da.UpdateCommand.CommandType = CommandType.StoredProcedure
<span style="color:Blue; Me.da.UpdateCommand.Connection = <span style="color:Blue; New SqlConnection(clsDBConn.clsDBConnections.prpConnString)
<span style="color:Blue; With <span style="color:Blue; Me.da.UpdateCommand.Parameters
.Add(<span style="color:#A31515; "@Status", SqlDbType.VarChar, 25, <span style="color:#A31515; "Status")
.Add(<span style="color:#A31515; "@FinalResultID", SqlDbType.BigInt, 0, <span style="color:#A31515; "FinalResultID")
.Add(<span style="color:#A31515; "@ResultID", SqlDbType.BigInt, 0, <span style="color:#A31515; "ResultID")
.Add(<span style="color:#A31515; "@AnalysisDate", SqlDbType.DateTime, 0, <span style="color:#A31515; "dtmAnalysisDate")
.Add(<span style="color:#A31515; "@RunNumber", SqlDbType.TinyInt, 0, <span style="color:#A31515; "intRunNumber")
.Add(<span style="color:#A31515; "@AnalystID", SqlDbType.VarChar, 4, <span style="color:#A31515; "vchAnalystID")
.Add(<span style="color:#A31515; "@FinalResult", SqlDbType.Float, 0, <span style="color:#A31515; "fltFinalResult")
.Add(<span style="color:#A31515; "@NHCommentCode", SqlDbType.TinyInt, 0, <span style="color:#A31515; "intCommentCode")
.Add(<span style="color:#A31515; "@ExportComments", SqlDbType.VarChar, 8000, <span style="color:#A31515; "vchExportComments")
.Add(<span style="color:#A31515; "@BatchID", SqlDbType.BigInt, 0, <span style="color:#A31515; "BatchID")
.Add(<span style="color:#A31515; "@UserID", SqlDbType.VarChar, 4).Value = prpCurrentUser
.Add(<span style="color:#A31515; "@Original_UpdateDatetime", SqlDbType.DateTime, 0, <span style="color:#A31515; "dtmUpdateDatetime")
.Item(<span style="color:#A31515; "@Original_UpdateDatetime").SourceVersion = DataRowVersion.Original
.Add(<span style="color:#A31515; "@ReturnValue", SqlDbType.TinyInt)
.Item(<span style="color:#A31515; "@ReturnValue").Direction = ParameterDirection.ReturnValue
<span style="color:Blue; End <span style="color:Blue; With
<span style="color:Blue; End <span style="color:Blue; If
<span style="color:Green; i will explicitly AcceptChanges if the update is successful in CellValidating event
<span style="color:Blue; Me.da.AcceptChangesDuringUpdate = <span style="color:Blue; False
<span style="color:Blue; Me.da.Update(<span style="color:Blue; Me.dtResults.GetChanges(DataRowState.Modified))
<span style="color:Green; TEST IF ROWS ARE ACCEPTED AFTER UPDATE, YOU MAY BE ABLE TO REMOVE THE AcceptChangesDuringUpdate
<span style="color:Green; TEST dtmUpdateDatetime is update after da.Update, if not you will get a concurrency issue if the user updates the cell more than once
<span style="color:Blue; Select <span style="color:Blue; Case <span style="color:Blue; Me.da.UpdateCommand.Parameters(<span style="color:#A31515; "@ReturnValue").Value
<span style="color:Blue; Case <span style="color:Blue; Is = 0 : <span style="color:Blue; Return <span style="color:#A31515; "Success"
<span style="color:Blue; Case <span style="color:Blue; Is = 1
MsgBox(<span style="color:#A31515; "FinalResultID " & <span style="color:Blue; Me.da.UpdateCommand.Parameters(<span style="color:#A31515; "@FinalResultID").Value & _
<span style="color:#A31515; ", cant be updated because another user has modified this record since you obtained it. Please refresh the dataset and try again.", MsgBoxStyle.Exclamation)
<span style="color:Blue; Return <span style="color:#A31515; "Failed"
<span style="color:Blue; Case <span style="color:Blue; Is = 2
MsgBox(<span style="color:#A31515; "FinalResultID " & <span style="color:Blue; Me.da.UpdateCommand.Parameters(<span style="color:#A31515; "@FinalResultID").Value & _
<span style="color:#A31515; ", " & <span style="color:Blue; Me.cbxAnalyte.Text & <span style="color:#A31515; " cant have a Ready To Report Status because the whole blood folate result has a No Reportable Result Status.", MsgBoxStyle.Exclamation)
<span style="color:Blue; Return <span style="color:#A31515; "Failed"
<span style="color:Blue; Case <span style="color:Blue; Is = 3
MsgBox(<span style="color:#A31515; "FinalResultID " & <span style="color:Blue; Me.da.UpdateCommand.Parameters(<span style="color:#A31515; "@FinalResultID").Value & _
<span style="color:#A31515; ", was not updated successfully. Please see administrator.", MsgBoxStyle.Exclamation)
<span style="color:Blue; Return <span style="color:#A31515; "Failed"
<span style="color:Blue; Case <span style="color:Blue; Else
MsgBox(<span style="color:#A31515; "FinalResultID " & <span style="color:Blue; Me.da.UpdateCommand.Parameters(<span style="color:#A31515; "@FinalResultID").Value & _
<span style="color:#A31515; ", update failed. Please see administrator.", MsgBoxStyle.Exclamation)
<span style="color:Blue; Return <span style="color:#A31515; "Failed"
<span style="color:Blue; End <span style="color:Blue; Select
<span style="color:Blue; Catch ex <span style="color:Blue; As Exception
<span style="color:Blue; Call pubsub_AppErrorHandler(<span style="color:Blue; Me.<span style="color:Blue; GetType.Name, System.Reflection.MethodBase.GetCurrentMethod().Name, ex)
<span style="color:Blue; Return <span style="color:#A31515; "Failed"
<span style="color:Blue; Finally
<span style="color:Blue; Me.Cursor = Cursors.<span style="color:Blue; Default
<span style="color:Blue; End <span style="color:Blue; Try
<span style="color:Blue; End <span style="color:Blue; Function
<span style="color:Blue; End <span style="color:Blue; Class
[/code]
<div style="color:Black;background-color:White; <pre>
<span style="color:Blue; ALTER <span style="color:Blue; PROCEDURE [dbo].[usp_CalculatedAnalytes]
(
@Status <span style="color:Blue; varchar(25),
@FinalResultID <span style="color:Blue; bigint,
@ResultID <span style="color:Blue; bigint,
@AnalysisDate <span style="color:Blue; datetime,
@RunNumber <span style="color:Blue; tinyint,
@AnalystID <span style="color:Blue; varchar(4),
@FinalResult <span style="color:Blue; float,
@NHCommentCode <span style="color:Blue; tinyint,
@ExportComments <span style="color:Blue; varchar(<span style="color:Magenta; max),
@BatchID <span style="color:Blue; bigint,
@UserID <span style="color:Blue; varchar(4),
@Original_UpdateDatetime <span style="color:Blue; datetime
)
<span style="color:Blue; AS
<span style="color:Blue; BEGIN
<span style="color:Green; -- Return 1, if RBF3 cant be updated because of a concurrency issue. Always check this first before continuing.
<span style="color:Green; -- Return 2, if RBF3 cant be set to Ready To Report because WBF1 is No Reportable Result.
<span style="color:Green; -- Return 3, if no row was affected by the update
<span style="color:Green; -- Return 0, if update was successful.
<span style="color:Green; -- check for concurrency, ensure the record has not been modified since the user obtained their dataset
<span style="color:Blue; if <span style="color:Blue; not <span style="color:Blue; exists(<span style="color:Blue; select * <span style="color:Blue; from tbl_data_Expected
<span style="color:Blue; where FinalResultID = @FinalResultID
<span style="color:Blue; and dtmUpdateDatetime = @Original_UpdateDatetime)
<span style="color:Blue; Return 1
<span style="color:Green; -- see if WBF has been set No Reportable Result, if so RBF can not be set Ready To Report
<span style="color:Blue; if <span style="color:Blue; exists(<span style="color:Blue; select * <span style="color:Blue; from tbl_data_Expected
<span style="color:Blue; where intAnalysis_PK = (<span style="color:Blue; select intAnalysis_PK <span style="color:Blue; from tbl_data_Expected <span style="color:Blue; where FinalResultID = @FinalResultID)
<span style="color:Blue; and bintAnalyteCodeID = dbo.usf_GetAnalyteCodeID(<span style="color:#A31515; WBF1)
<span style="color:Blue; and booNoReportableResult = 1
<span style="color:Blue; and @Status = <span style="color:#A31515; Ready To Report)
<span style="color:Blue; Return 2
<span style="color:Green; -- now it is ok to update
<span style="color:Blue; update tbl_data_Expected
<span style="color:Blue; set ResultID = <span style="color:Blue; case <span style="color:Blue; when @Status <span style="color:Blue; in (<span style="color:#A31515; Ready To Report, <span style="color:#A31515; No Reportable Result) <span style="color:Blue; then @ResultID <span style="color:Blue; else <span style="color:Blue; NULL <span style="color:Blue; end,
dtmAnalysisDate = <span style="color:Blue; case <span style="color:Blue; when @Status <span style="color:Blue; in (<span style="color:#A31515; Ready To Report, <span style="color:#A31515; No Reportable Result) <span style="color:Blue; then @AnalysisDate <span style="color:Blue; else <span style="color:Blue; NULL <span style="color:Blue; end,
intRunNumber = <span style="color:Blue; case <span style="color:Blue; when @Status <span style="color:Blue; in (<span style="color:#A31515; Ready To Report, <span style="color:#A31515; No Reportable Result) <span style="color:Blue; then @RunNumber <span style="color:Blue; else <span style="color:Blue; NULL <span style="color:Blue; end,
vchAnalystID = <span style="color:Blue; case <span style="color:Blue; when @Status <span style="color:Blue; in (<span style="color:#A31515; Ready To Report, <span style="color:#A31515; No Reportable Result) <span style="color:Blue; then @AnalystID <span style="color:Blue; else <span style="color:Blue; NULL <span style="color:Blue; end,
fltFinalResult = <span style="color:Blue; case <span style="color:Blue; when @Status <span style="color:Blue; in (<span style="color:#A31515; Ready To Report, <span style="color:#A31515; No Reportable Result) <span style="color:Blue; then @FinalResult <span style="color:Blue; else <span style="color:Blue; NULL <span style="color:Blue; end,
intCommentCode = <span style="color:Blue; case <span style="color:Blue; when @Status <span style="color:Blue; in (<span style="color:#A31515; Ready To Report, <span style="color:#A31515; No Reportable Result) <span style="color:Blue; then @NHCommentCode <span style="color:Blue; else <span style="color:Blue; NULL <span style="color:Blue; end,
booNoReportableResult = <span style="color:Blue; case <span style="color:Blue; when @Status = <span style="color:#A31515; Ready To Report <span style="color:Blue; then 0
<span style="color:Blue; when @Status = <span style="color:#A31515; No Reportable Result <span style="color:Blue; then 1
<span style="color:Blue; else <span style="color:Blue; NULL <span style="color:Blue; end,
vchExportComments = @ExportComments,
BatchID = <span style="color:Blue; case <span style="color:Blue; when @Status <span style="color:Blue; in (<span style="color:#A31515; Ready To Report, <span style="color:#A31515; No Reportable Result) <span style="color:Blue; then @BatchID <span style="color:Blue; else <span style="color:Blue; NULL <span style="color:Blue; end,
dtmSetReadyDatetime = <span style="color:Blue; case <span style="color:Blue; when @Status <span style="color:Blue; in (<span style="color:#A31515; Ready To Report, <span style="color:#A31515; No Reportable Result) <span style="color:Blue; then <span style="color:Magenta; GETDATE() <span style="color:Blue; else <span style="color:Blue; NULL <span style="color:Blue; end,
chrUpdateUserID = @UserID,
dtmUpdateDatetime = <span style="color:Magenta; GETDATE()
<span style="color:Blue; where FinalResultID = @FinalResultID
<span style="color:Blue; if <span style="color:Magenta; @@ROWCOUNT = 1
<span style="color:Blue; Return 0
<span style="color:Blue; else
<span style="color:Blue; Return 3
<span style="color:Blue; END
[/code]
<hr class="sig Ryan
View the full article
When a user changes a Status column value the DataGridView CellValidating event is fired and in that event it trys to update the record in the database using the stored procedure. The update works, but my SqlDataAdapter doesnt update dtmUpdateDatetime
in dtResults. My UpdateDatabase function uses dtmUpdateDatetime to check for concurrency. Im probably not doing this right so I open to suggestions and advice. Thanks in advance!
<div style="color:Black;background-color:White; <pre>
<span style="color:Blue; Public <span style="color:Blue; Class frmCalculatedResults
<span style="color:Blue; Private dtCriteria <span style="color:Blue; As <span style="color:Blue; New DataTable
<span style="color:Blue; Private bs <span style="color:Blue; As <span style="color:Blue; New BindingSource
<span style="color:Blue; Private clsDGV <span style="color:Blue; As <span style="color:Blue; New clsDataGridView
<span style="color:Blue; Private da <span style="color:Blue; As SqlDataAdapter
<span style="color:Blue; Private dtResults <span style="color:Blue; As DataTable
<span style="color:Blue; Private <span style="color:Blue; Function prvfnc_CalculateRBF() <span style="color:Blue; As <span style="color:Blue; String
<span style="color:Blue; Using cnn <span style="color:Blue; As <span style="color:Blue; New SqlClient.SqlConnection(clsDBConn.clsDBConnections.prpConnString)
cnn.Open()
<span style="color:Blue; Using cmd <span style="color:Blue; As <span style="color:Blue; New SqlClient.SqlCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = <span style="color:#A31515; "[dbo].[ssp_CalculateRBCF]"
<span style="color:Blue; Me.da = <span style="color:Blue; New SqlDataAdapter(cmd)
<span style="color:Blue; Me.da.Fill(<span style="color:Blue; Me.dtResults)
<span style="color:Blue; End <span style="color:Blue; Using
<span style="color:Blue; End <span style="color:Blue; Using
<span style="color:Blue; Return <span style="color:#A31515; "Success"
<span style="color:Blue; End <span style="color:Blue; Function
<span style="color:Blue; Private <span style="color:Blue; Sub dgvCalculatedResults_CellValidating(<span style="color:Blue; ByVal sender <span style="color:Blue; As <span style="color:Blue; Object, <span style="color:Blue; ByVal e <span style="color:Blue; As System.Windows.Forms.DataGridViewCellValidatingEventArgs) <span style="color:Blue; Handles dgvCalculatedResults.CellValidating
<span style="color:Blue; If <span style="color:Blue; Me.dgvCalculatedResults.IsCurrentCellDirty <span style="color:Blue; Then
<span style="color:Blue; Dim rwIndex <span style="color:Blue; As <span style="color:Blue; Integer = <span style="color:Blue; Me.dtResults.Rows.IndexOf(<span style="color:Blue; DirectCast(<span style="color:Blue; Me.dgvCalculatedResults.CurrentRow.DataBoundItem, DataRowView).Row)
<span style="color:Blue; Dim clIndex <span style="color:Blue; As <span style="color:Blue; Integer = <span style="color:Blue; Me.dtResults.Columns(<span style="color:Blue; Me.dgvCalculatedResults.Columns(e.ColumnIndex).Name).Ordinal
<span style="color:Blue; With <span style="color:Blue; Me.dtResults.Rows(rwIndex)
.BeginEdit()
<span style="color:Green; ensure DataRowState is set to modified, if user hits the Enter key it sets row modified, if the user mouse clicks another cell the row is not set modified
<span style="color:Blue; If .RowState.<span style="color:Blue; Equals(DataRowState.Unchanged) <span style="color:Blue; Then .SetModified()
.Item(clIndex) = e.FormattedValue
<span style="color:Blue; If <span style="color:Blue; Me.UpdateDatabase() = <span style="color:#A31515; "Failed" <span style="color:Blue; Then
.RejectChanges()
<span style="color:Blue; Else
.AcceptChanges()
<span style="color:Blue; End <span style="color:Blue; If
<span style="color:Blue; End <span style="color:Blue; With
<span style="color:Blue; End <span style="color:Blue; If
<span style="color:Blue; End <span style="color:Blue; Sub
<span style="color:Blue; Private <span style="color:Blue; Function UpdateDatabase() <span style="color:Blue; As <span style="color:Blue; String
<span style="color:Blue; Try
<span style="color:Blue; Me.Cursor = Cursors.WaitCursor
<span style="color:Blue; If <span style="color:Blue; Me.da.UpdateCommand <span style="color:Blue; Is <span style="color:Blue; Nothing <span style="color:Blue; Then
<span style="color:Blue; Me.da.UpdateCommand = <span style="color:Blue; New SqlCommand(<span style="color:#A31515; "dbo.usp_CalculatedAnalytes")
<span style="color:Blue; Me.da.UpdateCommand.CommandType = CommandType.StoredProcedure
<span style="color:Blue; Me.da.UpdateCommand.Connection = <span style="color:Blue; New SqlConnection(clsDBConn.clsDBConnections.prpConnString)
<span style="color:Blue; With <span style="color:Blue; Me.da.UpdateCommand.Parameters
.Add(<span style="color:#A31515; "@Status", SqlDbType.VarChar, 25, <span style="color:#A31515; "Status")
.Add(<span style="color:#A31515; "@FinalResultID", SqlDbType.BigInt, 0, <span style="color:#A31515; "FinalResultID")
.Add(<span style="color:#A31515; "@ResultID", SqlDbType.BigInt, 0, <span style="color:#A31515; "ResultID")
.Add(<span style="color:#A31515; "@AnalysisDate", SqlDbType.DateTime, 0, <span style="color:#A31515; "dtmAnalysisDate")
.Add(<span style="color:#A31515; "@RunNumber", SqlDbType.TinyInt, 0, <span style="color:#A31515; "intRunNumber")
.Add(<span style="color:#A31515; "@AnalystID", SqlDbType.VarChar, 4, <span style="color:#A31515; "vchAnalystID")
.Add(<span style="color:#A31515; "@FinalResult", SqlDbType.Float, 0, <span style="color:#A31515; "fltFinalResult")
.Add(<span style="color:#A31515; "@NHCommentCode", SqlDbType.TinyInt, 0, <span style="color:#A31515; "intCommentCode")
.Add(<span style="color:#A31515; "@ExportComments", SqlDbType.VarChar, 8000, <span style="color:#A31515; "vchExportComments")
.Add(<span style="color:#A31515; "@BatchID", SqlDbType.BigInt, 0, <span style="color:#A31515; "BatchID")
.Add(<span style="color:#A31515; "@UserID", SqlDbType.VarChar, 4).Value = prpCurrentUser
.Add(<span style="color:#A31515; "@Original_UpdateDatetime", SqlDbType.DateTime, 0, <span style="color:#A31515; "dtmUpdateDatetime")
.Item(<span style="color:#A31515; "@Original_UpdateDatetime").SourceVersion = DataRowVersion.Original
.Add(<span style="color:#A31515; "@ReturnValue", SqlDbType.TinyInt)
.Item(<span style="color:#A31515; "@ReturnValue").Direction = ParameterDirection.ReturnValue
<span style="color:Blue; End <span style="color:Blue; With
<span style="color:Blue; End <span style="color:Blue; If
<span style="color:Green; i will explicitly AcceptChanges if the update is successful in CellValidating event
<span style="color:Blue; Me.da.AcceptChangesDuringUpdate = <span style="color:Blue; False
<span style="color:Blue; Me.da.Update(<span style="color:Blue; Me.dtResults.GetChanges(DataRowState.Modified))
<span style="color:Green; TEST IF ROWS ARE ACCEPTED AFTER UPDATE, YOU MAY BE ABLE TO REMOVE THE AcceptChangesDuringUpdate
<span style="color:Green; TEST dtmUpdateDatetime is update after da.Update, if not you will get a concurrency issue if the user updates the cell more than once
<span style="color:Blue; Select <span style="color:Blue; Case <span style="color:Blue; Me.da.UpdateCommand.Parameters(<span style="color:#A31515; "@ReturnValue").Value
<span style="color:Blue; Case <span style="color:Blue; Is = 0 : <span style="color:Blue; Return <span style="color:#A31515; "Success"
<span style="color:Blue; Case <span style="color:Blue; Is = 1
MsgBox(<span style="color:#A31515; "FinalResultID " & <span style="color:Blue; Me.da.UpdateCommand.Parameters(<span style="color:#A31515; "@FinalResultID").Value & _
<span style="color:#A31515; ", cant be updated because another user has modified this record since you obtained it. Please refresh the dataset and try again.", MsgBoxStyle.Exclamation)
<span style="color:Blue; Return <span style="color:#A31515; "Failed"
<span style="color:Blue; Case <span style="color:Blue; Is = 2
MsgBox(<span style="color:#A31515; "FinalResultID " & <span style="color:Blue; Me.da.UpdateCommand.Parameters(<span style="color:#A31515; "@FinalResultID").Value & _
<span style="color:#A31515; ", " & <span style="color:Blue; Me.cbxAnalyte.Text & <span style="color:#A31515; " cant have a Ready To Report Status because the whole blood folate result has a No Reportable Result Status.", MsgBoxStyle.Exclamation)
<span style="color:Blue; Return <span style="color:#A31515; "Failed"
<span style="color:Blue; Case <span style="color:Blue; Is = 3
MsgBox(<span style="color:#A31515; "FinalResultID " & <span style="color:Blue; Me.da.UpdateCommand.Parameters(<span style="color:#A31515; "@FinalResultID").Value & _
<span style="color:#A31515; ", was not updated successfully. Please see administrator.", MsgBoxStyle.Exclamation)
<span style="color:Blue; Return <span style="color:#A31515; "Failed"
<span style="color:Blue; Case <span style="color:Blue; Else
MsgBox(<span style="color:#A31515; "FinalResultID " & <span style="color:Blue; Me.da.UpdateCommand.Parameters(<span style="color:#A31515; "@FinalResultID").Value & _
<span style="color:#A31515; ", update failed. Please see administrator.", MsgBoxStyle.Exclamation)
<span style="color:Blue; Return <span style="color:#A31515; "Failed"
<span style="color:Blue; End <span style="color:Blue; Select
<span style="color:Blue; Catch ex <span style="color:Blue; As Exception
<span style="color:Blue; Call pubsub_AppErrorHandler(<span style="color:Blue; Me.<span style="color:Blue; GetType.Name, System.Reflection.MethodBase.GetCurrentMethod().Name, ex)
<span style="color:Blue; Return <span style="color:#A31515; "Failed"
<span style="color:Blue; Finally
<span style="color:Blue; Me.Cursor = Cursors.<span style="color:Blue; Default
<span style="color:Blue; End <span style="color:Blue; Try
<span style="color:Blue; End <span style="color:Blue; Function
<span style="color:Blue; End <span style="color:Blue; Class
[/code]
<div style="color:Black;background-color:White; <pre>
<span style="color:Blue; ALTER <span style="color:Blue; PROCEDURE [dbo].[usp_CalculatedAnalytes]
(
@Status <span style="color:Blue; varchar(25),
@FinalResultID <span style="color:Blue; bigint,
@ResultID <span style="color:Blue; bigint,
@AnalysisDate <span style="color:Blue; datetime,
@RunNumber <span style="color:Blue; tinyint,
@AnalystID <span style="color:Blue; varchar(4),
@FinalResult <span style="color:Blue; float,
@NHCommentCode <span style="color:Blue; tinyint,
@ExportComments <span style="color:Blue; varchar(<span style="color:Magenta; max),
@BatchID <span style="color:Blue; bigint,
@UserID <span style="color:Blue; varchar(4),
@Original_UpdateDatetime <span style="color:Blue; datetime
)
<span style="color:Blue; AS
<span style="color:Blue; BEGIN
<span style="color:Green; -- Return 1, if RBF3 cant be updated because of a concurrency issue. Always check this first before continuing.
<span style="color:Green; -- Return 2, if RBF3 cant be set to Ready To Report because WBF1 is No Reportable Result.
<span style="color:Green; -- Return 3, if no row was affected by the update
<span style="color:Green; -- Return 0, if update was successful.
<span style="color:Green; -- check for concurrency, ensure the record has not been modified since the user obtained their dataset
<span style="color:Blue; if <span style="color:Blue; not <span style="color:Blue; exists(<span style="color:Blue; select * <span style="color:Blue; from tbl_data_Expected
<span style="color:Blue; where FinalResultID = @FinalResultID
<span style="color:Blue; and dtmUpdateDatetime = @Original_UpdateDatetime)
<span style="color:Blue; Return 1
<span style="color:Green; -- see if WBF has been set No Reportable Result, if so RBF can not be set Ready To Report
<span style="color:Blue; if <span style="color:Blue; exists(<span style="color:Blue; select * <span style="color:Blue; from tbl_data_Expected
<span style="color:Blue; where intAnalysis_PK = (<span style="color:Blue; select intAnalysis_PK <span style="color:Blue; from tbl_data_Expected <span style="color:Blue; where FinalResultID = @FinalResultID)
<span style="color:Blue; and bintAnalyteCodeID = dbo.usf_GetAnalyteCodeID(<span style="color:#A31515; WBF1)
<span style="color:Blue; and booNoReportableResult = 1
<span style="color:Blue; and @Status = <span style="color:#A31515; Ready To Report)
<span style="color:Blue; Return 2
<span style="color:Green; -- now it is ok to update
<span style="color:Blue; update tbl_data_Expected
<span style="color:Blue; set ResultID = <span style="color:Blue; case <span style="color:Blue; when @Status <span style="color:Blue; in (<span style="color:#A31515; Ready To Report, <span style="color:#A31515; No Reportable Result) <span style="color:Blue; then @ResultID <span style="color:Blue; else <span style="color:Blue; NULL <span style="color:Blue; end,
dtmAnalysisDate = <span style="color:Blue; case <span style="color:Blue; when @Status <span style="color:Blue; in (<span style="color:#A31515; Ready To Report, <span style="color:#A31515; No Reportable Result) <span style="color:Blue; then @AnalysisDate <span style="color:Blue; else <span style="color:Blue; NULL <span style="color:Blue; end,
intRunNumber = <span style="color:Blue; case <span style="color:Blue; when @Status <span style="color:Blue; in (<span style="color:#A31515; Ready To Report, <span style="color:#A31515; No Reportable Result) <span style="color:Blue; then @RunNumber <span style="color:Blue; else <span style="color:Blue; NULL <span style="color:Blue; end,
vchAnalystID = <span style="color:Blue; case <span style="color:Blue; when @Status <span style="color:Blue; in (<span style="color:#A31515; Ready To Report, <span style="color:#A31515; No Reportable Result) <span style="color:Blue; then @AnalystID <span style="color:Blue; else <span style="color:Blue; NULL <span style="color:Blue; end,
fltFinalResult = <span style="color:Blue; case <span style="color:Blue; when @Status <span style="color:Blue; in (<span style="color:#A31515; Ready To Report, <span style="color:#A31515; No Reportable Result) <span style="color:Blue; then @FinalResult <span style="color:Blue; else <span style="color:Blue; NULL <span style="color:Blue; end,
intCommentCode = <span style="color:Blue; case <span style="color:Blue; when @Status <span style="color:Blue; in (<span style="color:#A31515; Ready To Report, <span style="color:#A31515; No Reportable Result) <span style="color:Blue; then @NHCommentCode <span style="color:Blue; else <span style="color:Blue; NULL <span style="color:Blue; end,
booNoReportableResult = <span style="color:Blue; case <span style="color:Blue; when @Status = <span style="color:#A31515; Ready To Report <span style="color:Blue; then 0
<span style="color:Blue; when @Status = <span style="color:#A31515; No Reportable Result <span style="color:Blue; then 1
<span style="color:Blue; else <span style="color:Blue; NULL <span style="color:Blue; end,
vchExportComments = @ExportComments,
BatchID = <span style="color:Blue; case <span style="color:Blue; when @Status <span style="color:Blue; in (<span style="color:#A31515; Ready To Report, <span style="color:#A31515; No Reportable Result) <span style="color:Blue; then @BatchID <span style="color:Blue; else <span style="color:Blue; NULL <span style="color:Blue; end,
dtmSetReadyDatetime = <span style="color:Blue; case <span style="color:Blue; when @Status <span style="color:Blue; in (<span style="color:#A31515; Ready To Report, <span style="color:#A31515; No Reportable Result) <span style="color:Blue; then <span style="color:Magenta; GETDATE() <span style="color:Blue; else <span style="color:Blue; NULL <span style="color:Blue; end,
chrUpdateUserID = @UserID,
dtmUpdateDatetime = <span style="color:Magenta; GETDATE()
<span style="color:Blue; where FinalResultID = @FinalResultID
<span style="color:Blue; if <span style="color:Magenta; @@ROWCOUNT = 1
<span style="color:Blue; Return 0
<span style="color:Blue; else
<span style="color:Blue; Return 3
<span style="color:Blue; END
[/code]
<hr class="sig Ryan
View the full article