TableAdapter.UpdateAll in Master/Detail

[Nitesh]

Hi Guys,

I have a master/detail datagridview setup. I am using a Dataset to manage select,update, deletes. I have various text columns and combobox columns in the master datagridview. I use this code to save all changes to both tables:

Private Sub ProductsBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ProductsBindingNavigatorSaveItem.Click
        Dim saved As Boolean = False

        Dim TestForChanges As DataTable

        Me.Validate()
        Me.Result_HeaderBindingSource.EndEdit()

        If Me.ResultsDataSet.HasChanges Then

            Try

                saved = True

                TestForChanges = Me.ResultsDataSet.Result_Header.GetChanges(DataRowState.Added)

                If TestForChanges IsNot Nothing Then
                    Dim j As Integer

                    For j = 0 To TestForChanges.Rows.Count - 1
                        Dim curProdId As Integer = 0

                        If Not IsDBNull(TestForChanges.Rows(j).Item(1)) Then
                            Integer.TryParse(TestForChanges.Rows(j).Item(1).ToString, curProdId)
                        Else
                            curProdId = 0
                        End If

                        Dim test2 As DataTable = Me.ProdTestProductTableAdapter.GetData(curProdId)

                        For k = 0 To test2.Rows.Count - 1

                            Dim newchildrow As ResultsDataSet.Result_DetailRow

                            newchildrow = ResultsDataSet.Result_Detail.NewResult_DetailRow
                            newchildrow.ResultID = CInt(TestForChanges.Rows(j).Item(0).ToString)
                            newchildrow.TestID = CInt(test2.Rows(k).Item(1).ToString)
                            newchildrow.SpecLow = test2.Rows(k).Item(2).ToString
                            newchildrow.SpecHi = test2.Rows(k).Item(3).ToString
                            newchildrow.Method = test2.Rows(k).Item(4).ToString
                            newchildrow.TestType = test2.Rows(k).Item(5).ToString
                            newchildrow.Active = CBool(1)

                            Me.ResultsDataSet.Result_Detail.Rows.Add(newchildrow)

                        Next k
                        Me.Result_DetailBindingSource.EndEdit()

                    Next


                End If

                Me.TableAdapterManager.UpdateAll(Me.ResultsDataSet)


            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

        End If

        If (saved) Then
            MsgBox("Your Results where saved", MsgBoxStyle.Information, "Save Successful")
        End If

    End Sub

I am calling TableAdaptermanager.UpdateAll to save the changes in the master table. It worked well with the textbox/combobox columns etc. However the client now needs an autocomplete textbox instead of a combobox. I used this code to populate a textbox and make it autocomplete:

Private Sub Result_HeaderDataGridView_EditingControlShowing(sender As Object, e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles Result_HeaderDataGridView.EditingControlShowing

        If (TypeOf (e.Control) Is TextBox) And (Me.Result_HeaderDataGridView.CurrentCell.ColumnIndex = 13) Then
            Dim source = New AutoCompleteStringCollection()
            Dim stringArray As [String]() = Array.ConvertAll(Of DataRow, [String])(Me.ResultsDataSet.Customers.Select(), Function(row As DataRow) DirectCast(row("CustomerName"), [String]))
            source.AddRange(stringArray)

            Dim txtCustomerName As TextBox = DirectCast(e.Control, TextBox)
            If (Not txtCustomerName Is Nothing) Then
                txtCustomerName.AutoCompleteMode = AutoCompleteMode.SuggestAppend
                txtCustomerName.AutoCompleteCustomSource = source
                txtCustomerName.AutoCompleteSource = AutoCompleteSource.CustomSource
            Else
                txtCustomerName.AutoCompleteCustomSource = Nothing
            End If

        End If

    End Sub

I now need to tap into the tableadapter.updateall or change the way I update so I can use the autocomplete. Also I don't have a key/value pair in the autocomplete like I would in a combobox. Can someone please advise me on a better way to do this task. I'm not sure what is the best way forward

[jmcilhinney]

An auto-complete TextBox seems like a very bad idea because that means that the user can enter any value they want, which may or may not correspond to a value in your selection list. A far better idea would be to keep the ComboBox, change its DropDownStyle to DropDown instead of DropDownList and then use its auto-complete functionality using the drop-down items of the ComboBox. You can then use the Validating and Validated events of the control to ensure that the user has either entered or selected a valid value from the list and prevent their leaving the control if they haven't. That means that you can keep your current bindings in place.

[Nitesh]

Hi JM,

Sorry I forgot to mention. I created a seperate page for the client to maintain a list of customers. They don't want to exit the page they are working on to go add/edit new customers, therefore they want the ability to add customers on the fly. Unfortunately the combobox won't work in this scenario. I need to alter the save method to insert new records into the customer table and then update the customerid in my master table. Not sure how though

[jmcilhinney]

I would change the TableAdapter query to include an extra column from the other table, so you have the ID and name. You can then hide the ID column in the grid. When the value changes in the name column, you use your own code to search the parent table for that name and, if it's present, set the ID. If the name is not present then set the ID field to null. When it's time to save, find all the null IDs and insert records for them into the parent table. Once that's done you can get the new IDs and substitute them into the child tabel for the nulls and then save the child table.