Issues on loop for retrieve the dynamic sql parameter values from dynamic textboxes

Question

all. I am a new VB.NET beginner. I am facing the issue of how to pass the dynamic SQL parameter values from the dynamic textboxes to search the data. I had added control of dynamic textboxes and labels and would like to search the data on the database table based on the dynamic textboxes value inputted from the user. Currently, I can only able to search the data from 1 dynamic textbox value only.

I want all the values from the dynamic textboxes that the user had been entered can be retrieved, and search the data based on the SQL parameter variable name and value entered by the user.

Can someone give me some solutions on how to solve this problem? I had stuck on this problem for a few days. Thank you for all the help!

What I had tried:

Private Sub FilterData()

    Dim count As Integer = 0

    'filterdata for radiobutton
    Try
        For Each TextBox As TextBox In grp2.Controls.OfType(Of TextBox)()
            For Each Label As Label In grp2.Controls.OfType(Of Label)()

                Using connection As New SqlConnection("connectionString")

                    'user key in the SQL statement
                    sql = TextBox1.Text
                    Dim sql2 As String
                    sql2 = sql
                    Dim sp1 As String() = sql.Split(New String() {"where"}, StringSplitOptions.None)
                    sql = sp1(0) & " where " & Label.Text & " = @parameter or " & Label.Text & " =@parameter"

                    If (TextBox.Text <> "") Then
                        count += 1
                        For j As Integer = 0 To count - 1
                            Using cmd As New SqlCommand(sql, connection)
                                cmd.Parameters.AddWithValue("@parameter", TextBox.Text)
                                'cmd.Parameters.Add("@parameter", SqlDbType.NVarChar, 20).Value = TextBox.Text
                                connection.Open()
                                Dim dt As New DataTable()
                                Dim reader As SqlDataReader
                                reader = cmd.ExecuteReader()
                                dt.Load(reader)
                                DataGridView1.DataSource = dt
                            End Using
                        Next
                    Else
                        GetData()
                    End If
                    'cmd.Dispose()
                    connection.Close()
                End Using
            Next
        Next

    Catch ex As Exception
        'MsgBox(ex.Message)
    End Try

End Sub

Answer 1

Firstly, if you have a 1:1 correspondence between Labels and TextBoxes then you should not be using two nested For Each loops, because that is going to pair up each and every Label with each and every TextBox. What you should be doing is creating arrays and then using a single For loop to access the pairs of controls:

Dim labels = grp2.Controls.OfType(Of Label)().ToArray()
Dim textBoxes = grp2.Controls.OfType(Of TextBox)().ToArray()

For i = 0 To labels.getUpperBound(0)
    Dim label = labels(i)
    Dim textBox = textBoxes(i)

    '...
Next

As for build the SQL and adding the parameters, I would tend to do it something like this:

Dim labels = grp2.Controls.OfType(Of Label)().ToArray()
Dim textBoxes = grp2.Controls.OfType(Of TextBox)().ToArray()
Dim criteria As New List(Of String)
Dim command As New SqlCommand

For i = 0 To labels.getUpperBound(0)
    Dim label = labels(i)
    Dim textBox = textBoxes(i)
    Dim parameterName = "@" & label.Text.Replace(" ", "_")

    criteria.Add($"[{label.Text}] = {parameterName}")
    command.Parameters.AddWithValue(parameterName, textBox.Text)
Next

Dim sql = "SELECT * FROM MyTable"

If criteria.Any() Then
    sql &= " WHERE " & String.Join(" OR ", criteria)
End If

command.CommandText = sql