Loop Through records in a dataset table

[bckelley]

For this particular sub I type the customer id in a text box and then click the button, then I'm trying to get the relative information pertaining to that id.
Not sure what I am doing wrong.

Comes back with an error message of

system.data.sqlclient.sqlexception
Recursive common table expression '[customers]' does not contain a top-level UNION ALL operator.

Private Sub FillByCustomerIDToolStripButton_Click(ByVal sender As Object, ByVal e As EventArgs) Handles FillByCustomerIDToolStripButton.Click

        Try

            Dim customerID As Integer = CInt(CustomerIDToolStripTxtBox.Text)

            ' LOAD THE CUSTOMERS TABLE WITH THE SELECTED CUSTOMER.
            Dim id = Me.CustomersTableAdapter.FillByCustomerID(Me.DatabaseDataSet.customers, customerID)
            If Me.DatabaseDataSet.customers.Count > 0 Then

                For Each row As DataRow In DatabaseDataSet.customers.Rows
                    Dim i = row(id)

                    CustomerIDTextBox.Text = Me.CustomersTableAdapter.FillByCustomerID(Me.DatabaseDataSet.customers, customerID)
                    NameTextBox.Text = Me.CustomersTableAdapter.GetDataBy(customerID).Rows(i)(1).ToString

                Next

                ' LOAD THE INVOICES TABLE WITH THE CUSTOMER'S INVOICES.
                Me.InvoicesTableAdapter.FillByCustomerID(Me.DatabaseDataSet.invoices, customerID)

            Else

                MessageBox.Show("No Customer was found with that ID.", "No Customer Found.", MessageBoxButtons.OK, MessageBoxIcon.Error)

            End If

        Catch ex As Exception

            MessageBox.Show(ex.Message, ex.GetType.ToString)

        End Try

    End Sub

[rproffitt]

There appears to be missing code or steps in your post. Read https://docs.microsoft.com/en-us/sql/ssms/visual-db-tools/create-union-queries-visual-database-tools?view=sql-server-2017 for an example of the UNION but you may need to redo your query (I don't see that here) to use the UNION ALL as the error calls out.

[bckelley]

The union between the two tables seems to be fixed, but now I have this error: InvalidCastException Conversion from string '[customerName]' to type 'Integer' is not valid.