Nested for loop error

[dakaboguy]

Im having trouble with nested for loops.

I have have a SQL database with a few tables i need data pulled from.
So in my first for loop i search for all materials in a product, i insert the data into a datatable then i do the for each dr in dt.rows() loop.
then the next for loop I search for all reports that include the material.
and so on... I have about 4 for loops.
My problem is that when it runs through all the loops and I output data it is not what I expect( I need each for statement to run through once for each material, which is not what is happening).
any help would be appreciated.

 Private Sub RunCytoReport1()
        Dim cmd As New SqlCommand
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        Dim dt As New DataTable
        Dim cmd1 As New SqlCommand
        Dim da1 As New SqlDataAdapter
        Dim ds1 As New DataSet
        Dim dt1 As New DataTable
        Dim cmd2 As New SqlCommand
        Dim da2 As New SqlDataAdapter
        Dim ds2 As New DataSet
        Dim dt2 As New DataTable
        Dim cmd3 As New SqlCommand
        Dim da3 As New SqlDataAdapter
        Dim ds3 As New DataSet
        Dim dt3 As New DataTable
        Dim TestType As String
        Dim TestResult As String
        'Dim sdr As SqlDataReader
        If Trim(cbModel.Text) = "" Or Trim(cbProc1.Text) = "" Then
        Else

            Try
                conn = GetConnect()
                conn.Open()
                cmd = conn.CreateCommand
                cmd.CommandText = "SELECT * FROM Product_Materials  WHERE Model_Number = '" & Trim(cbModel.Text) & "'  ORDER BY Material ASC"
                da.SelectCommand = cmd
                da.Fill(ds, "Product_Materials")
                dt = ds.Tables("Product_Materials")

                If (dt.Rows.Count > 0) Then
                    Dim i As Integer = 1
                    Dim dr As DataRow
                    For Each dr In dt.Rows()
                        'First For loop through all materials linked to a product
                        Dim Material As String = dr("Material")
                        ListView1.Items.Add(dr("Material"))

                        cmd1 = conn.CreateCommand
                        cmd1.CommandText = "SELECT * FROM Report_Materials WHERE Test_Type = 'Cytotoxicity' and Material = '" & Trim(Material) & "'"
                        da1.SelectCommand = cmd1
                        da1.Fill(ds1, "Report_Materials")
                        dt1 = ds1.Tables("Report_Materials")

                        If (dt1.Rows.Count > 0) Then
                            Dim dr1 As DataRow
                            'Second for loop goes through all reports that match Test_Type and Material
                            For Each dr1 In dt1.Rows()
                                Dim ReportNumber As String = dr1("Report_Number")
                                ListView3.Items.Add(dr1("Report_Number"))

                                cmd2 = conn.CreateCommand
                                cmd2.CommandText = "SELECT * FROM Test_Log WHERE Report_Number = '" & Trim(ReportNumber) & "'"
                                da2.SelectCommand = cmd2
                                da2.Fill(ds2, "Test_Log")
                                dt2 = ds2.Tables("Test_Log")

                                If (dt2.Rows.Count > 0) Then
                                    Dim dr2 As DataRow
                                    'Third for loop goes through the report to locate pass/fail results
                                    For Each dr2 In dt2.Rows()
                                        TestType = dr2("Test_Type")
                                        TestResult = dr2("Result")


                                        cmd3 = conn.CreateCommand
                                        cmd3.CommandText = "SELECT * FROM Report_Reprocessing WHERE Reprocessing_Method = '" & Trim(cbProc1.Text) & "' and Report_Number = '" & Trim(ReportNumber) & "'"
                                        da3.SelectCommand = cmd3
                                        da3.Fill(ds3, "Report_Reprocessing")
                                        dt3 = ds3.Tables("Report_Reprocessing")

                                        If (dt3.Rows.Count > 0) Then

                                            txtOne.Text = Material
                                            txtTwo.Text = TestResult
                                            savematerialresult()
                                            CheckAllMaterialsPass()
                                            'Inserting the same values into a datagridview
                                            DataGridView1.Rows.Add(New String() {Material, TestResult, ReportNumber})

                                        Else

                                            'MsgBox("Reprocessing " & Trim(cbProc1.Text) & " could not be found for test type:" & Trim(Label2.Text) & "")
                                            MsgBox("third check failed")
                                            txtCyto1.BackColor = Color.Orange
                                            txtCyto1.Text = "No Data"
                                        End If
                                    Next dr2
                                Else
                                    'MsgBox("Error: could not locate report in test log")
                                    MsgBox("second check failed")
                                    txtCyto1.BackColor = Color.Orange
                                    txtCyto1.Text = "No Data"
                                End If
                                ReportNumber = ""

                            Next dr1

                        Else
                            'MsgBox("Material " & Trim(cbInputMaterial.Text) & " could not be found for test type:" & Trim(Label2.Text) & "")
                            MsgBox("first check failed")
                            txtCyto1.BackColor = Color.Orange
                            txtCyto1.Text = "No Data"
                        End If
                        Material = ""
                    Next dr

                Else

                End If
            Catch ex As Exception
            End Try
            conn.Close()
        End If

    End Sub

[Reverend Jim]

Why don't you start by telling us the structure of your tables, and the nature of the information you want to extract from them. If you can do that in English (instead of code), perhaps we can build a query that will get that data directly from the tables without all the messy bits of code.

[M.Waqas Aslam]

and i also want to add something in jim's comment that in your code you are using 4 cmd objects , 4 dataadapter and 4 datatable , it not good . just use this

cmd as new sqlcommand()
da as new sqldataadapter()
etc

this will reduce your code.

Regards

[dakaboguy]

Ok below are my tables and the structure:
table name / columns

Product_Materials: Model_Number, Material
Report_Materials: Material, Test_Type
Test_Log: Report_Number, Date, Result, Test_Type
Report_Reprocessing: Report_Number, Reprocessing_Method, Test_Type

basicly I want to enter a "model" then search that each "material" associated with that model passes/fails the testing.

Here is how i am trying to achieve that.

OK so the User enters a "Model_Number", and runs the summary.
Im trying to search "Product_Materials" and select all Materials for that model.
then for each "Material" I want to find all reports that match "Material" and "Test_Type" from Table:Test_Log and match "Reprocessing_Method" from table:Report_Reprocessing.
then for each "Report_Number" i want to know if it passes/fails. If each "Material" passes i want to output "Pass", If any "Material" fails i want to output "Fail".

[Reverend Jim]

basicly I want to enter a "model" then search that each "material" associated with that model passes/fails the testing.

The following query will return all the Material and test result from Test_Log for all materials used in the given Model_Number. Assuming Result is a boolean you can do:

SELECT Material,
       Result  = CASE WHEN Result THEN 'PASS' ELSE 'FAIL' END
  FROM Test_Log 
 WHERE Material IN (
       SELECT Material FROM Product_Materials
        WHERE Model_Number = '655321')

It will convert a true value to the string 'PASS' and a false to 'FAIL'.