'For each' loop based in SQL select statement

Question

I am trying to figure out how to perform a 'For Each' loop for each distinct value returned from an SQL query.

Here is my pseudo code.

connection.ConnectionString = "server=***01\SQLEXPRESS; database=Billing; integrated security=yes"
command.Connection = connection
command.CommandType = CommandType.Text
command.CommandText = "SELECT DISTINCT [Customer] FROM [Billing]

For Each... Distinct value returned above

    command.CommandType = CommandType2.Text
    command.CommandText2 = "Select * FROM [Billing] WHERE [Customer] = [DISTINCT VALUE FROM ABOVE]
    dataAdapter.SelectCommand = command

        'Fill data to datatable
        connection.Open()
        dataAdapter.Fill(datatableMain)
        connection.Close()

Then Export (I am ok with the Export code)

In essence I need to be able to loop until I have a datatable per customer exported.

Hope that makes sense, any help greatly appreciated.

Is that real code? Why do you want to loop every distinct cutomer just to be able to select all Billings for these customers? Wouldn't it be much more efficient to select all billings in the first place? Select * FROM [Billing] ORDER BY Customer — Tim Schmelter
– Tim Schmelter, Commented Aug 25, 2014 at 11:27
You can just use a self join on Billing table and Group By Customer. — Pradeep Kumar
– Pradeep Kumar, Commented Aug 25, 2014 at 11:32
@PradeepKumar: presumes that he doesn't want all rows of each customer. The inner select speaks against. — Tim Schmelter
– Tim Schmelter, Commented Aug 25, 2014 at 11:34
I need a separate output for each customer. So for customer A, I want to export a file with all of Customer A's billing details and for customer B, all of customers B's billing details and so on... — user3580480
– user3580480, Commented Aug 25, 2014 at 11:36
ok.. In that case, you can just fill the datatable once (SELECT * FROM Billing ORDER BY Customer). Then you can filter on each customer and get rows related to that customer. That would be much more efficient than looping and filling data for each customer separately. — Pradeep Kumar
– Pradeep Kumar, Commented Aug 25, 2014 at 11:40

LarsTech · Accepted Answer · 2014-08-25 13:46:57Z

2

Here is some untested code. But it will give you a fair idea about what I was talking about (in the comments of question).

Sub Whatever()
    connection.ConnectionString = "server=***01\SQLEXPRESS; database=Billing; integrated security=yes"
    connection.Open()
    Using da As New SqlDataAdapter("Select * FROM [Billing] ORDER BY Customer", connection)
        da.Fill(datatableMain)
    End Using
    connection.Close()

    ' get distinct customers
    Dim dv As New DataView(datatableMain)
    Dim distinctCustomers As DataTable = dv.ToTable(True, "Customer")

    For Each customer As DataRow In distinctCustomers.Rows
        ' this messagebox is only to give you an idea which customer you are printing
        ' not required in actual code.
        MessageBox.Show("Exporting Customer... " & customer("Customer").ToString)

        Dim customerRows() As DataRow = datatableMain.Select("Customer=" & customer("Customer").ToString)  '<-- put single quotes around value if "Customer" field is of string type. e.g. "Customer='value'"
        For Each customerRow As DataRow In customerRows
            ' all the rows related to this customer are here
            ' do whatever you do to export

        Next
    Next
End Sub

edited Aug 25, 2014 at 13:46

LarsTech

81.9k14 gold badges161 silver badges237 bronze badges

answered Aug 25, 2014 at 13:30

Pradeep Kumar

7,0294 gold badges24 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3580480 Over a year ago

Thanks @Pradeep, looks good. However it currently errors. Additional information: Cannot find column [ALL001]. ALL001 my first customer value, not a column name? Thanks

user3580480 Over a year ago

my field is called 'CustomerLookup'. Here is the line that throws the error: Dim customerRows() As DataRow = datatableMain.Select("CustomerLookup=" & customer("CustomerLookup").ToString)

Pradeep Kumar Over a year ago

What happens if you put single quotes around the values. Dim customerRows() As DataRow = datatableMain.Select("CustomerLookup = '" & customer("CustomerLookup").ToString & "'")

user3580480 Over a year ago

Thanks @Pradeep that seems to have done the trick. I will mark this post as answered. Unfortunately my code is now hanging. I have opened a new thread and posted my latest code here. stackoverflow.com/questions/25488938/… Thanks

Collectives™ on Stack Overflow

'For each' loop based in SQL select statement

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related