Member Avatar for sing1006 
   Dim DMC_No As New List(Of Integer)
                cmd.CommandText = "SELECT MC_No FROM tbl_MainComp WHERE EquipTag=  '" & TextBox1.Text & "'"
                Using reader As OleDb.OleDbDataReader = cmd.ExecuteReader()
                    While reader.Read()
                        DMC_No.Add(reader.GetInt32(reader.GetOrdinal("MC_No")))
                    End While
                End Using



                Dim DFM_No As New List(Of Integer)

                For i = 0 To DMC_No.Count - 1
                    MessageBox.Show(DMC_No.Item(i)) 'This line to to test is there any value in array list and it get the correct value
                    cmd.CommandText = "SELECT FM_No FROM tbl_FailureMode WHERE MC_No=  " & DMC_No.Item(i) & ""
                    Using reader2 As OleDb.OleDbDataReader = cmd.ExecuteReader()
                        While reader2.Read()
                            DFM_No.Add(reader2.GetInt32(reader2.GetOrdinal("FM_No")))
                        End While
                    End Using
                    MessageBox.Show(DMC_No.Item(i))'This line to to test is there any value in array list and it get the correct value
                Next i

I think my problem is occuring at this line

cmd.CommandText = "SELECT FM_No FROM tbl_FailureMode WHERE MC_No=  " & DMC_No.Item(i) & ""

When i set the DMC_No.Item(i) value as hard code it able to run smoothly, but when i add the DMC_No.Item(i) and i check the value by using

  Dim sResult As String = String.Join(", ", DFM_No.ToArray())
                Dim str As String
                str = String.Join(",", DFM_No)

                MessageBox.Show(str)

It does not return any value

  • 3 Contributors
  • 2 Replies
  • 416 Views
  • 3 Hours Discussion Span
  • Latest Post Latest Post by Reverend Jim

Dani AI

Generated

A short diagnosis for based on the symptoms (DMC_No shows correct values but DFM_No ends up empty): the inner SELECT is returning zero rows at runtime. Common root causes are a different runtime SQL string than the tested hard‑coded one, a type/format mismatch between MC_No values and the column in tbl_FailureMode, a subtle column-name typo, or a command/reader reuse issue. ’s suggestion to set a breakpoint and inspect both the DMC_No item and the actual SQL text is the single most useful first step; ’s redeclaration warning is also worth checking (shadowed variables can silently ruin a loop).

Practical checks to perform in order: build the inner SQL into a local string and log it (Debug.Print or a temporary MessageBox) right before ExecuteReader; confirm the connection and any prior DataReaders are closed; after ExecuteReader test rdr.HasRows; verify FM_No exists and that NULLs are handled; inspect DMC_No contents before the loop for unexpected values or duplicates. If the hard-coded SQL works but the dynamic form doesn’t, comparing the two SQL strings character-for-character usually reveals the problem.

A safer, more robust pattern (avoids string concat and reduces casting issues) is to use a short parameterized command created per iteration and to convert values defensively. Example:

Dim sqlFM As String = "SELECT FM_No FROM tbl_FailureMode WHERE MC_No = ?"
Using cmdFM As New OleDb.OleDbCommand(sqlFM, connection)
    cmdFM.Parameters.AddWithValue("?", dmcValue)
    Using rdrFM As OleDb.OleDbDataReader = cmdFM.ExecuteReader()
        If rdrFM IsNot Nothing AndAlso rdrFM.HasRows Then
            While rdrFM.Read()
                Dim fm As Integer = If(IsDBNull(rdrFM("FM_No")), -1, Convert.ToInt32(rdrFM("FM_No")))
                dfmList.Add(fm)
            End While
        End If
    End Using
End Using

For performance and clarity consider a single JOIN to retrieve FM_No for an EquipTag instead of looping. Example SQL:

SELECT f.FM_No
FROM tbl_MainComp m
INNER JOIN tbl_FailureMode f ON m.MC_No = f.MC_No
WHERE m.EquipTag = ?

Other final tips: clear parameters when reusing a command, avoid concatenation to prevent SQL injection, and print DFM_No.Count after the loop to confirm whether rows were found.

Recommended Answers

All 2 Replies

Member Avatar for Taywin

Line 11 redeclares line 1 variable???

Member Avatar for Reverend Jim

Put a breakpoint at

cmd.CommandText = "SELECT FM_No FROM tbl_FailureMode WHERE MC_No=  " & DMC_No.Item(i) & ""

and check the value of DMC_No.Item(i) in either the watch or immediate windows. Note that you don't need the "" at the end. You can just use

cmd.CommandText = "SELECT FM_No FROM tbl_FailureMode WHERE MC_No=  " & DMC_No.Item(i)

or even

cmd.CommandText = "SELECT FM_No FROM tbl_FailureMode WHERE MC_No=  " & DMC_No(i)
Edited by Reverend Jim
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.