Member Avatar for 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
  • 2 Contributors
  • 2 Replies
  • 3K Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by bckelley
Member Avatar for 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.

commented: since this is the only place I can comment! [customerName] is a string from the database i.e. George, Fred, Alice whatever, that was a placeholder +0
Member Avatar for 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.

commented: I see no customername in the code. Again, you may be leaving out code that the forum needs to see. +15
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.