1. dataDbStore is a class that populates the DataSet
2. frmPprEdit is a form which uses the populated DataSet from dataDbStore to display the recordsPublic Class dataDbStore
3. frmAddSru is a dialog form popped up from frmPprEdit which performs the adds. The dataset from frmPprEdit is passed as a property
4. After frmAddSru closes, frmPprEdit checks the dialog result to determine whether it needs to update the database. If it does then it calls a method in dbDataStore to perform the updates.
I've attempted to eliminate the extraneous code to focus on the issue of he adds.
Public Class dataDbStore
Private Sub FillAideDataTablesByPprNbr()
Try
dataAideDataSet.Clear()
' Load this data in sequence
' ''dataAidePprLruAdapter.Fill(dataAideDataSet.tblPprLru)
'
'dataAideDataSet.EnforceConstraints = False
If _PprNbr.Length > 0 Then
dataAidePprAdapter.FillByPprNbr(dataAideDataSet.tblPPR, _PprNbr)
dataAideMilestonesAdapter.FillByPprNbr(dataAideDataSet.tblPpr_Milestones, _PprNbr)
dataAideMybAdapter.FillByPprNbr(dataAideDataSet.tblPpr_MYB, _PprNbr)
dataAideSolutionsAdapter.FillByPprNbr(dataAideDataSet.tblPpr_Solutions, _PprNbr)
dataAideUsageAdapter.FillByPprNbr(dataAideDataSet.tblPprUsage, _PprNbr)
dataAideResolutionAdapter.Fill(dataAideDataSet.tblResolution)
dataAideSruAdapter.FillByPprNbr(dataAideDataSet.tblPprSru, _PprNbr)
'Dim sruTable As New DataTable
'sruTable = dataAideSruAdapter.GetSruByPprLruID(_PprLruID)
'Debug.Print("Reading the tblPprSru ...")
'For Each dr As DataRow In sruTable.Rows
' Debug.Print("Col0=" & dr(0).ToString() & " Col1=" & dr(1))
'Next
dataAideSruLinksAdapter.FillByPprNbr(dataAideDataSet.tblPpr_PprSru_Links, _PprNbr)
dataAideCompAdapter.FillByPprNbr(dataAideDataSet.tblPprComponents, _PprNbr)
dataAideCompLinksAdapter.FillByPprNbr(dataAideDataSet.tblPpr_PprComp_Links, _PprNbr)
End If
Catch ex As Exception
Stop
End Try
End Sub
Public Function GetAideDataSet() As devAideDS
If dataAideDataSet.tblPPR.Count <= 0 Then
' Determine whether this is being run by LruID or PprNbr
If _PprLruID > 0 Then
FillAideDataTables()
ElseIf _PprNbr.Length > 0 Then
FillAideDataTablesByPprNbr()
End If
End If
Return dataAideDataSet
End Function
Public Sub New(ByVal PprNbr As String)
Try
_PprNbr = PprNbr
If _CurrentServer <> My.Settings.CurrentServer.ToString Then
_CurrentServer = My.Settings.CurrentServer.ToString
End If
Select Case _CurrentServer
Case "COSLOGDATA"
dataAideDataSet.Clear()
FillAideDataTablesByPprNbr()
Case "COSLOGSVR"
' This value is invalid for this Class
' It only gets data from COSLOGDATA
Message.AppMessageBox("Invalid call to services class dataDbStore.", MessageBoxCaptions.ErrorCaption)
End Select
Catch ex As Exception
Message.ShowExceptionMsg(ex)
End Try
End Sub
Public Sub UpdateAideData()
Try
Debug.Print("SRU: " & sruAddsReturn)
Debug.Print("SRU Links: " & srulinksAddsReturn)
' Process the added and updated rows first
' followed by the deleted rows
If dataAideDataSet.HasChanges Then
' Process the deletes next
' removing all link tables first followed by tables with foreign keys
' LINKS
'Dim pprlinkDeletes() As DataRow = Me.dataAideDataSet.tblPpr_LruPpr_Links.Select("", "", DataViewRowState.Deleted)
If complinkDeletes IsNot Nothing Then
complinkDeletesReturn = dataAideCompAdapter.Update(complinkDeletes)
End If
If srulinkDeletes IsNot Nothing Then
srulinkDeletesReturn = dataAideSruLinksAdapter.Update(srulinkDeletes)
End If
If solDeletes IsNot Nothing Then
solDeletesReturn = dataAideSolutionsAdapter.Update(solDeletes)
End If
If milestoneDeletes IsNot Nothing Then
mileDeletesReturn = dataAideMilestonesAdapter.Update(milestoneDeletes)
End If
If mybDeletes IsNot Nothing Then
mybDeletesReturn = dataAideMybAdapter.Update(mybDeletes)
End If
If usageDeletes IsNot Nothing Then
usagelinkDeletesReturn = dataAideUsageAdapter.Update(usageDeletes)
End If
If sruDeletes IsNot Nothing Then
sruDeletesReturn = dataAideSruAdapter.Update(sruDeletes)
End If
If compDeletes IsNot Nothing Then
compDeletesReturn = (dataAideCompAdapter.Update(compDeletes))
End If
If pprDeletes IsNot Nothing Then
pprDeletesReturn = dataAidePprAdapter.Update(pprDeletes)
End If
'-----------
' Updates won't be applicable to link tables
If pprAdds IsNot Nothing Then
pprAddsReturn = (dataAidePprAdapter.Update(pprAdds))
End If
If sruAdds IsNot Nothing Then
sruAddsReturn = dataAideSruAdapter.Update(sruAdds)
End If
If compAdds IsNot Nothing Then
compAddsReturn = dataAideCompAdapter.Update(compAdds)
End If
If solAdds IsNot Nothing Then
solAddsReturn = dataAideSolutionsAdapter.Update(solAdds)
End If
If milestoneAdds IsNot Nothing Then
milestoneAddsReturn = dataAideMilestonesAdapter.Update(milestoneAdds)
End If
If mybAdds IsNot Nothing Then
mybAddsReturn = dataAideMybAdapter.Update(mybAdds)
End If
If usageAdds IsNot Nothing Then
usageAddsReturn = dataAideUsageAdapter.Update(usageAdds)
End If
If srulinksAdds IsNot Nothing Then
srulinksAddsReturn = Me.dataAideSruLinksAdapter.Update(srulinksAdds)
End If
If complinksAdds IsNot Nothing Then
complinksAddsReturn = Me.dataAideCompAdapter.Update(complinksAdds)
End If
'---------
' Updates won't be applicable to link tables
If pprUpdates IsNot Nothing Then
pprReturn = dataAidePprAdapter.Update(pprUpdates)
End If
If sruUpdates IsNot Nothing Then
sruReturn = dataAideSruAdapter.Update(sruUpdates)
End If
If compUpdates IsNot Nothing Then
compReturn = dataAideCompAdapter.Update(compUpdates)
End If
If solUpdates IsNot Nothing Then
solReturn = dataAideSolutionsAdapter.Update(solUpdates)
End If
If milestoneUpdates IsNot Nothing Then
milestoneReturn = dataAideMilestonesAdapter.Update(milestoneUpdates)
End If
If mybUpdates IsNot Nothing Then
mybReturn = dataAideMybAdapter.Update(mybUpdates)
End If
If usageUpdates IsNot Nothing Then
usageReturn = dataAideUsageAdapter.Update(usageUpdates)
End If
If srulinksUpdates IsNot Nothing Then
srulinksReturn = Me.dataAideSruLinksAdapter.Update(srulinksUpdates)
End If
If complinksUpdates IsNot Nothing Then
complinksReturn = Me.dataAideCompAdapter.Update(complinksUpdates)
End If
End If
Catch ex As Exception
Message.AppMessageBox("Error updating the AIDE dataset in dataDbStore." & ControlChars.CrLf & ControlChars.CrLf & ex.Message.ToString, MessageBoxCaptions.ErrorCaption)
End Try
End Sub
Public Sub RefreshSruData()
dataAideDataSet.tblPpr_PprSru_Links.Clear()
dataAideDataSet.tblPprSru.Clear()
dataAideDataSet.EnforceConstraints = False
Try
If _PprNbr.Length > 0 Then
dataAideSruAdapter.FillByPprNbr(dataAideDataSet.tblPprSru, _PprNbr)
dataAideSruLinksAdapter.FillByPprNbr(dataAideDataSet.tblPpr_PprSru_Links, _PprNbr)
Else
dataAideSruAdapter.FillSruByPprLruID(dataAideDataSet.tblPprSru, _PprLruID)
dataAideSruLinksAdapter.FillPprSruLinksByPprLruID(dataAideDataSet.tblPpr_PprSru_Links, _PprLruID)
End If
dataAideDataSet.EnforceConstraints = True
Catch ex As Exception
Message.AppMessageBox(ex.Message, MessageBoxCaptions.ErrorCaption, "dataDbStore_RefreshSruData")
End Try
End Sub
End Class
Public Class frmSruAdd
Private Sub btnAddSRU_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddSRU.Click
' Execute the SRU add stored procedure
If Me.txtSruID.Text.Length > 0 AndAlso CInt(Me.txtSruID.Text) > 0 Then
LinkSruItem()
Else
AddSruItem()
End If
End Sub
Private Sub AddSruItem()
Dim cnnString As String = String.Empty
Dim sruID As Integer = 0
Try
Me.cboPN.AutoCompleteMode = AutoCompleteMode.Append
Dim newSruRow As DataRow
Dim newLinkRow As DataRow
newSruRow = aideDS.tblPprSru.NewRow
newLinkRow = aideDS.tblPpr_PprSru_Links.NewRow
newSruRow("PN") = dbText(Me.cboPN.Text)
newSruRow("Cage") = dbText(Me.cboCage.Text)
newSruRow("Name") = dbText(Me.txtDescriptionSru.Text)
newSruRow("NSN") = dbText(Me.cboNSN.Text)
newSruRow("QPA") = dbInteger(Me.txtQpaSru.Text)
newSruRow("SOS") = dbText(Me.cboSOS.Text)
newSruRow("FailRate") = dbDouble(Me.txtFailRateSru.Text)
newSruRow("ChgDate") = Date.Now
aideDS.tblPprSru.Rows.Add(newSruRow)
sruID = newSruRow("ID")
Me.txtSruID.Text = dbInteger(sruID)
newLinkRow("PprID") = dbInteger(Me.txtPprID.Text)
newLinkRow("SruID") = dbInteger(Me.txtSruID.Text)
aideDS.tblPpr_PprSru_Links.Rows.Add(newLinkRow)
_sruID = dbInteger(Me.txtSruID.Text)
If _sruID > 0 Then
' Notify the user that the links was successful
' and ask whether they was to insert another
'If they want to insert another then clear
' the controls except for the PprID and leave
' the form displayed with the cursor in the PN
_sruItemsAdded = +1
If Message.AskYesNoQuestion("Insert was successful.", _
"Load another?", "SRU Insert") = vbYes Then
ClearSruControls()
Else
' If they opt to add no more links close the form
btnClose_Click(Me, New System.EventArgs)
End If
Else
'Notify them that the insert attempt was unsuccessful
If Message.AskYesNoQuestion("Insert was unsuccessful.", _
"Try again?", "SRU Insert") = vbYes Then
' Take no action to save the current data on the form
' for the user to retry this load.
Else
' If they opt to not try again the close the form
btnClose_Click(Me, New System.EventArgs)
End If
End If
Catch ex As Exception
Message.AppMessageBox(ex.Message, MessageBoxCaptions.ErrorCaption, "FillCageCombo")
Stop
End Try
End Sub
End Class
-------------------
Public Class frmPprEdit
Private Sub frmPprEditer_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
LoadAideDataSet()
' On open first time you need to bind the dataset
BindData()
' and set up the navigation
CompNavigationHandler(Me, New System.EventArgs)
SruNavigationHandler(Me, New System.EventArgs)
End Sub
Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
Try
If aideDS.HasChanges Then
If Message.AskYesNoQuestion("Save this data?", "Changes to the data have not been saved.") = vbYes Then
'_dataStore.UpdateAideData()
'SaveToolStripButton_Click(sender, e)
SaveData()
Try
If aideDS.HasChanges Then
Try
SaveData()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End If
Catch ex As Exception
Message.ShowExceptionMsg(ex, sender.ToString)
End Try
Else
aideDS.RejectChanges()
End If
End If
Catch ex As Exception
Message.ShowExceptionMsg(ex, sender.ToString)
End Try
Me.Close()
End Sub
Private Sub BindData()
Dim startDate As New Date
Dim closedDate As New Date
Try
_loadingData = True
With bsPpr
.DataSource = aideDS
.DataMember = "tblPPR"
End With
With bsMYB
.DataSource = bsPpr
.DataMember = "FK_tblPpr_MYB_tblPPR"
'.DataSource = aideDS
'.DataMember = "tblPpr_MYB"
'.Filter = "PprID=" & _PprID
End With
With bsMilestones
.DataSource = bsPpr
.DataMember = "FK_tblPpr_Milestones_tblPPR"
End With
With bsSolutions
.DataSource = bsPpr
.DataMember = "FK_tblPpr_Solutions_tblPPR"
End With
With bsUsage
.DataSource = bsPpr
.DataMember = "FK_tblPprUsage_tblPPR"
End With
With bsResolution
.DataSource = aideDS
.DataMember = "tblResolution"
End With
With bsMilestonesLookup
.DataSource = aideDS
.DataMember = "vtblMilestones"
End With
' These two binding sources contain the data
' pointed to by the links above.
With bsComp
.DataSource = aideDS
.DataMember = "tblPprComponents"
'FilterCompRecords()
End With
With bsSru
.DataSource = aideDS
.DataMember = "tblPprSru"
'FilterSruRecords()
End With
' Set these two link tables up with the foreign key
' as the DataMember
' Ref: CoDe Mag - Article: Table Complex Data Binding with Windows Forms 2.0
With bsPprCompLinks
.DataSource = bsPpr
.DataMember = "FK_tblPpr_PprComp_Links_tblPPR" '"FK_tblPprComponents_tblPpr_PprComp_Links"
End With
With bsPprSruLinks
.DataSource = bsPpr
.DataMember = "FK_tblPpr_PprSru_Links_tblPPR" '"FK_tblPprSru_tblPpr_PprSru_Links"
End With
With bsCompLinks
.DataSource = bsComp
.DataMember = "FK_tblPprComponents_tblPpr_PprComp_Links" '"FK_tblPpr_PprComp_Links_tblPprComponents"
End With
With bsSruLinks
.DataSource = bsSru
.DataMember = "FK_tblPprSru_tblPpr_PprSru_Links" '"FK_tblPpr_PprSru_Links_tblPprSru"
End With
' BINDING CODE DELETED
FilterCompRecords()
FilterSruRecords()
Catch ex As Exception
Message.ShowExceptionMsg(ex, "BindData")
End Try
End Sub
Private Sub btnAddSRU_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddSRU.Click, Button8.Click
Try
ofrmSruAdd = New frmSruAdd
ofrmSruAdd.PprID = CInt(Me.txtPprID.Text)
ofrmSruAdd.frmParent = Me
' Give the form a copy of the current aideDS
ofrmSruAdd.aideDS = aideDS
ofrmSruAdd._dataStore = _dataStore
ofrmSruAdd.ShowDialog()
If ofrmSruAdd.DialogResult = Windows.Forms.DialogResult.Yes Then
'bsSru.EndEdit()
'bsSruLinks.EndEdit()
RefreshSruData()
Me.SruNavigationHandler(Me.btnLastSru, e)
End If
Catch ex As Exception
Message.ShowExceptionMsg(ex, sender.ToString)
End Try
End Sub
Private Sub SruNavigationHandler(ByVal sender As Object, ByVal e As System.EventArgs) Handles _
btnFirstSru.Click, _
btnPrevSru.Click, _
btnNextSru.Click, _
btnLastSru.Click, _
btnAddSRU.Click, _
btnDeleteSRU.Click
Try
' If the BindingNavigator originated this then
' the code will fall through and not reposition the recordset
' the recordset but only update the controls
' Determine the number of records
Dim bsCount As Integer = bsSru.Count
' Reposition bsSru depending on which button pressed
If sender Is btnFirstSru Then
Me.BindingContext(bsSru).Position = 0
ElseIf sender Is btnPrevSru Then
Me.BindingContext(bsSru).Position = bsSru.Position - 1
ElseIf sender Is btnNextSru Then
Me.BindingContext(bsSru).Position += 1
ElseIf sender Is btnLastSru Then
bsSru.DataSource = aideDS
bsSru.DataMember = "tblPprSru"
bsSru.Position = bsCount - 1
ElseIf sender Is btnAddSRU Then
EnableSruControls(True)
bsSru.Position = bsCount - 1
ElseIf sender Is btnDeleteSRU Then
bsSru.Position = bsSru.Position - 1
Else
bsSru.Position = 0
End If
' Then update the positional information
Me.txtPositionSru.Text = bsSru.Position + 1 & " of " & bsCount
' Then enable/disable the correct buttons on the form
If bsSru.Position = 0 Then
Me.btnFirstSru.Enabled = False
Me.btnPrevSru.Enabled = False
' If this record is positioned on zero
' but has records then they can be deleted
ElseIf bsSru.Position > 0 Then
Me.btnFirstSru.Enabled = True
Me.btnPrevSru.Enabled = True
Me.btnDeleteSRU.Enabled = True
Else
Me.btnFirstSru.Enabled = False
Me.btnPrevSru.Enabled = False
Me.btnDeleteSRU.Enabled = False
Me.btnNextSru.Enabled = False
Me.btnLastSru.Enabled = False
Exit Sub
End If
If bsSru.Position + 1 >= bsCount Then
Me.btnLastSru.Enabled = False
Me.btnNextSru.Enabled = False
Else
Me.btnLastSru.Enabled = True
Me.btnNextSru.Enabled = True
End If
'Dim row As
'row = CType(CType(Me.CustomersBindingSource.Current, DataRowView).Row, NorthwindDataSet.CustomersRow)
Catch ex As Exception
Message.ShowExceptionMsg(ex, sender.ToString)
End Try
End Sub
Private Sub FilterSruRecords()
Try
Dim filterString As New System.Text.StringBuilder("ID=0")
'Dim row As New ToMgmtSQLDataSet.tblPpr_LruSru_LinksRow 'in bsPprCompLinks.list
'bsPprSruLinks.RemoveFilter()
bsSru.RemoveFilter()
For Each Row As DataRowView In bsPprSruLinks.List
If Row("PprID").ToString = Me.txtPprID.Text Then
If filterString.Length > 0 Then
filterString.Append(" OR ")
End If
filterString.Append("ID=" & Row("SruID"))
End If
Next
bsSru.Filter = filterString.ToString
' Then update the positional information
Me.txtPositionSru.Text = bsSru.Position + 1 & " of " & bsSru.Count
Catch ex As Exception
Message.ShowExceptionMsg(ex, "FilterSruRecords")
End Try
End Sub
Private Sub FilterCompRecords()
Try
Dim filterString As New System.Text.StringBuilder("ID=0")
bsComp.RemoveFilter()
For Each Row As DataRowView In bsPprCompLinks.List
If Row("PprID").ToString = Me.txtPprID.Text Then
If filterString.Length > 0 Then
filterString.Append(" OR ")
End If
filterString.Append("ID=" & Row("ID"))
End If
Next
bsComp.Filter = filterString.ToString
' Then update the positional information
Me.txtPositionComp.Text = bsComp.Position + 1 & " of " & bsComp.Count
Catch ex As Exception
Message.ShowExceptionMsg(ex, "FilterCompRecords")
End Try
End Sub
Private Sub bsPpr_PositionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles bsPpr.PositionChanged
FilterSruRecords()
FilterCompRecords()
End Sub
Public Sub RefreshSruData()
' The aideDS has the same Row.Count before and after refresh
' the real problem is that the BindingSources do not refresh
SaveData()
aideDS.tblPpr_PprSru_Links.Clear()
aideDS.tblPprSru
aideDS.EnforceConstraints = False
Dim dataAideSruAdapter As New devAideDSTableAdapters.tblPprSruTableAdapter
Dim dataAideSruLinksAdapter As New devAideDSTableAdapters.tblPpr_PprSru_LinksTableAdapter
Try
If _PprNbr.Length > 0 Then
dataAideSruAdapter.FillByPprNbr(aideDS.tblPprSru, _PprNbr)
dataAideSruLinksAdapter.FillByPprNbr(aideDStblPpr_PprSru_Links), _PprNbr)
Else
dataAideSruAdapter.FillSruByPprLruID(aideDS.tblPprSru), _PprLruID)
dataAideSruLinksAdapter.FillPprSruLinksByPprLruID(aideDS.tblPpr_PprSru_Links, _PprLruID)
End If
aideDS.EnforceConstraints = True
Catch ex As Exception
Message.AppMessageBox(ex.Message, MessageBoxCaptions.ErrorCaption, "frmPprEdit_RefreshSruData")
End Try
End Sub
Private Sub SaveData()
Me.Validate()
Try
If aideDS.HasChanges Then
Me.bsPpr.EndEdit()
Me.bsSru.EndEdit()
Me.bsComp.EndEdit()
Me.bsMilestones.EndEdit()
Me.bsMYB.EndEdit()
Me.bsUsage.EndEdit()
Me.bsSolutions.EndEdit()
Me.bsPprSruLinks.EndEdit()
Me.bsPprCompLinks.EndEdit()
Me.bsCompLinks.EndEdit()
Me.bsSruLinks.EndEdit()
Select Case My.Settings.CurrentServer
Case "COSLOGDATA"
_dataStore.UpdateAideData()
Case "COSLOGSVR"
_svrStore.UpdateAideData()
End Select
End If
bsPpr.ResetBindings(True)
bsSru.ResetBindings(True)
bsComp.ResetBindings(True)
bsPprSruLinks.ResetBindings(True)
bsPprCompLinks.ResetBindings(True)
Catch ex As Exception
Message.ShowExceptionMsg(ex, "frmPprEdit_SaveData")
End Try
End Sub
End Class