EDN Admin
Well-known member
Im getting frustrated on how hard it is to find a good DataGridView event, BindingSource event, or DataTable event to use to update my database. I have to use a stored procedure to do my adapters update, because when a user updates a row
I have to check for concurrency to ensure 3 other related rows to the row that has been modified hasnt been updated since the user obtained the data. For example, if my query returns 4 records and the user updates the 4th row, I have to ensure rows
1-4 havent been updated since the user retrieved the records.
My DataSource is a DataTable and I use a BindingSource to bind it to a DataGridView. I want to update my database whenever a user changes a cell value in the DataGridView or when I change a value programmatically to the underlying DataTable.
Anyone recommend an event from any of these objects?
The problem Im having is this. When the user changes a cell in the DataGridView I want to apply a date time stamp in another cell in that same row before I update my database. If the update is successful for that row then I AcceptChanges or
if it fails then I RejectChanges for that row. I cant change any values in the row in the DataTable RowChanged event because it will be an endless loop, plus it does fire when the user changes a cell. I cant use DataGridView CellEndEdit because
when I change a cell value programmatically using a loop the event wont fire to do the update. I cant use DataTable ColumnChanged event because the row hasnt been set Modified so the adapter doesnt know to update the row.
What do the experts recommend?
<br/>
AddHandler Me.ds.Tables("Current").RowChanged, New DataRowChangeEventHandler(AddressOf Me.UpdateDatabase)
<pre class="prettyprint lang-vb Private Sub UpdateDatabase(ByVal sender As Object, ByVal e As DataRowChangeEventArgs) Handles Me.ds.Tables("Current").RowChanged
Try
only run this code if the user changed a cell value
If Not (e.Action = DataRowAction.Change And e.Row.RowState = DataRowState.Modified) Then Exit Sub
If sender.Equals(Me.ds.Tables("Current")) Then
Me.da.UpdateCommand = New SqlCommand("dbo.NBB_UpdateCalculatedAnalytes_Current")
Me.da.UpdateCommand.CommandType = CommandType.StoredProcedure
Me.da.UpdateCommand.Connection = New SqlConnection(clsDBConn.clsDBConnections.prpConnString)
With Me.da.UpdateCommand.Parameters
.Add("@ReportingStatus", SqlDbType.VarChar, 25, "ReportingStatus")
.Add("@FinalResultID", SqlDbType.BigInt, 0, "FinalResultID")
.Add("@ResultID", SqlDbType.BigInt, 0, "ResultID")
.Add("@AnalysisDate", SqlDbType.DateTime, 0, "dtmAnalysisDate")
.Add("@RunNumber", SqlDbType.TinyInt, 0, "intRunNumber")
.Add("@AnalystID", SqlDbType.VarChar, 4, "vchAnalystID")
.Add("@FinalResult", SqlDbType.Float, 0, "fltFinalResult")
.Add("@NHCommentCode", SqlDbType.TinyInt, 0, "intCommentCode")
.Add("@ExportComments", SqlDbType.VarChar, 8000, "vchExportComments")
.Add("@BatchID", SqlDbType.BigInt, 0, "BatchID")
.Add("@Hold", SqlDbType.Bit, 0, "booHold")
.Add("@UserID", SqlDbType.VarChar, 4).Value = prpCurrentUser
.Add("@UpdateDatetime", SqlDbType.DateTime, 0, "dtmUpdateDatetime")
.Add("@ReturnValue", SqlDbType.TinyInt)
.Item("@ReturnValue").Direction = ParameterDirection.ReturnValue
get the FinalResultID and UpdateDatetime for all records related to the patient so the stored procedure
can check for concerrency on all records (species and calculated analyte) related to the patient
Dim PatientID As String = e.Row("vchPatientID")
Dim enm As EnumerableRowCollection = From drs In Me.ds.Tables("Current").AsEnumerable() Where drs!vchPatients = PatientID Select FinalResult = drs!FinalResultID, UpdateDateTime = drs!dtmUpdateDatetime
Dim dt As DataTable = enm.CopyToDateTable()
Dim prm As New SqlParameter
With prm
.ParameterName = "@FinalResultUpdateDatetimes"
.Value = dt
.SqlDbType = SqlDbType.Structured
.TypeName = "dbo.udtt_FinalResultUpdateDatetimes"
End With
.Add(prm)
End With
End If
When the update is executed changes are automatically accepted even though an error number was returned, this is why
i will explicitly AcceptChanges or RejectChanges to the row below.
Me.da.AcceptChangesDuringUpdate = False
update database with row data
Me.da.Update(sender)
Select Case Me.da.UpdateCommand.Parameters("@ReturnValue").Value
Case Is = 0
e.Row.RowError = "Record doesnt exist in the database."
e.Row.RejectChanges()
Case Is = 1 : e.Row.AcceptChanges()
Case Else
e.Row.RowError = "Row update failed. Please see administrator."
e.Row.RejectChanges()
End Select
Catch ex As Exception
Call pubsub_AppErrorHandler(Me.GetType.Name, System.Reflection.MethodBase.GetCurrentMethod().Name, ex)
e.Row.RejectChanges()
End Try
End Sub[/code]
<br/>
<hr class="sig Ryan
View the full article
I have to check for concurrency to ensure 3 other related rows to the row that has been modified hasnt been updated since the user obtained the data. For example, if my query returns 4 records and the user updates the 4th row, I have to ensure rows
1-4 havent been updated since the user retrieved the records.
My DataSource is a DataTable and I use a BindingSource to bind it to a DataGridView. I want to update my database whenever a user changes a cell value in the DataGridView or when I change a value programmatically to the underlying DataTable.
Anyone recommend an event from any of these objects?
The problem Im having is this. When the user changes a cell in the DataGridView I want to apply a date time stamp in another cell in that same row before I update my database. If the update is successful for that row then I AcceptChanges or
if it fails then I RejectChanges for that row. I cant change any values in the row in the DataTable RowChanged event because it will be an endless loop, plus it does fire when the user changes a cell. I cant use DataGridView CellEndEdit because
when I change a cell value programmatically using a loop the event wont fire to do the update. I cant use DataTable ColumnChanged event because the row hasnt been set Modified so the adapter doesnt know to update the row.
What do the experts recommend?
<br/>
AddHandler Me.ds.Tables("Current").RowChanged, New DataRowChangeEventHandler(AddressOf Me.UpdateDatabase)
<pre class="prettyprint lang-vb Private Sub UpdateDatabase(ByVal sender As Object, ByVal e As DataRowChangeEventArgs) Handles Me.ds.Tables("Current").RowChanged
Try
only run this code if the user changed a cell value
If Not (e.Action = DataRowAction.Change And e.Row.RowState = DataRowState.Modified) Then Exit Sub
If sender.Equals(Me.ds.Tables("Current")) Then
Me.da.UpdateCommand = New SqlCommand("dbo.NBB_UpdateCalculatedAnalytes_Current")
Me.da.UpdateCommand.CommandType = CommandType.StoredProcedure
Me.da.UpdateCommand.Connection = New SqlConnection(clsDBConn.clsDBConnections.prpConnString)
With Me.da.UpdateCommand.Parameters
.Add("@ReportingStatus", SqlDbType.VarChar, 25, "ReportingStatus")
.Add("@FinalResultID", SqlDbType.BigInt, 0, "FinalResultID")
.Add("@ResultID", SqlDbType.BigInt, 0, "ResultID")
.Add("@AnalysisDate", SqlDbType.DateTime, 0, "dtmAnalysisDate")
.Add("@RunNumber", SqlDbType.TinyInt, 0, "intRunNumber")
.Add("@AnalystID", SqlDbType.VarChar, 4, "vchAnalystID")
.Add("@FinalResult", SqlDbType.Float, 0, "fltFinalResult")
.Add("@NHCommentCode", SqlDbType.TinyInt, 0, "intCommentCode")
.Add("@ExportComments", SqlDbType.VarChar, 8000, "vchExportComments")
.Add("@BatchID", SqlDbType.BigInt, 0, "BatchID")
.Add("@Hold", SqlDbType.Bit, 0, "booHold")
.Add("@UserID", SqlDbType.VarChar, 4).Value = prpCurrentUser
.Add("@UpdateDatetime", SqlDbType.DateTime, 0, "dtmUpdateDatetime")
.Add("@ReturnValue", SqlDbType.TinyInt)
.Item("@ReturnValue").Direction = ParameterDirection.ReturnValue
get the FinalResultID and UpdateDatetime for all records related to the patient so the stored procedure
can check for concerrency on all records (species and calculated analyte) related to the patient
Dim PatientID As String = e.Row("vchPatientID")
Dim enm As EnumerableRowCollection = From drs In Me.ds.Tables("Current").AsEnumerable() Where drs!vchPatients = PatientID Select FinalResult = drs!FinalResultID, UpdateDateTime = drs!dtmUpdateDatetime
Dim dt As DataTable = enm.CopyToDateTable()
Dim prm As New SqlParameter
With prm
.ParameterName = "@FinalResultUpdateDatetimes"
.Value = dt
.SqlDbType = SqlDbType.Structured
.TypeName = "dbo.udtt_FinalResultUpdateDatetimes"
End With
.Add(prm)
End With
End If
When the update is executed changes are automatically accepted even though an error number was returned, this is why
i will explicitly AcceptChanges or RejectChanges to the row below.
Me.da.AcceptChangesDuringUpdate = False
update database with row data
Me.da.Update(sender)
Select Case Me.da.UpdateCommand.Parameters("@ReturnValue").Value
Case Is = 0
e.Row.RowError = "Record doesnt exist in the database."
e.Row.RejectChanges()
Case Is = 1 : e.Row.AcceptChanges()
Case Else
e.Row.RowError = "Row update failed. Please see administrator."
e.Row.RejectChanges()
End Select
Catch ex As Exception
Call pubsub_AppErrorHandler(Me.GetType.Name, System.Reflection.MethodBase.GetCurrentMethod().Name, ex)
e.Row.RejectChanges()
End Try
End Sub[/code]
<br/>
<hr class="sig Ryan
View the full article