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
