How do I reference X controls into a FOR loop?

Question

I have those three text boxes by the name StandardPath_TextBoxA1, StandardPath_TextBoxA2, StandardPath_TextBoxA3 and through a FOR loop I am trying to save their text values into an ms access database file. I am trying something like this below, but my syntax is wrong... Any idea?

For i = 1 To 3
Dim str(i) As String
str(i) = "INSERT INTO StandardPaths ([TagNum], [Title], [Path]) values (?,?,?)"

Dim cmd As OleDbCommand = New OleDbCommand(str(i), MyConnection)
cmd.Parameters.Add(New OleDbParameter("TagNum", CType("A" & i, String)))
cmd.Parameters.Add(New OleDbParameter("Title", CType(StandardPath_LabelA(i).Text), String)))
cmd.Parameters.Add(New OleDbParameter("Path", CType(StandardPath_TextBoxA(i).Text), String)))

Try
    cmd.ExecuteNonQuery()
    cmd.Dispose()
    MyConnection.Close()
Catch ex As Exception
    MsgBox(ex.Message)
End Try
Next

EDIT:

The syntax error located here:

cmd.Parameters.Add(New OleDbParameter("Title", CType(StandardPath_LabelA(i).Text), String)))
cmd.Parameters.Add(New OleDbParameter("Path", CType(StandardPath_TextBoxA(i).Text), String)))

Answer 1

Try this. It fixes some type-mismatches in the original, as well as removing unnecessary variables and redundancy.

Dim sql As String = "INSERT INTO StandardPaths ([TagNum], [Title], [Path]) values (?,?,?)"
'DON'T RE-USE YOUR DATABASE CONNECTION, EXCEPT FOR SHORT BURSTS IN TIGHT LOOPS LIKE THIS
Using connection As New OleDbConnection("connection string here"), _
      cmd As New OleDbCommand(sql, connection)

    'Guessing at column types and lengths. Use actual types from your database here
    cmd.Parameters.Add("TagNum", OleDbType.VarChar, 2) 
    cmd.Parameters.Add("Title", OleDbType.VarWChar, 100)
    cmd.Parameters.Add("Path", OleDbType.VarWChar, 512)

    'Do this just once, outside the loop, to avoid needing to repeatedly re-negotiate with the DB. Let the USING black take care of closing the connection
    connection.Open()

    For i = 1 To 3

        Dim TitleLabel As Control = Me.Controls.Find("StandardPath_LabelA" & i.ToString(), True).First()
        Dim PathBox As Control = Me.Controls.Find("StandardPath_TextBoxA" & i.ToString(), True).First()

        cmd.Parameters(0).Value = "A" & i.ToString()
        cmd.Parameters(1).Value = TitleLabel.Text
        cmd.Parameters(2).Value = PathBox.Text

        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    Next
End Using

Even better if you have already grouped these controls in related panel or similar control, or added them to an array. That could allow better options than Controls.Find(), but I saw no indication of this in the question.

One other option, since the number of items is small, is to do it like this:

Dim sql As String = _ 
    "INSERT INTO StandardPaths ([TagNum], [Title], [Path]) values (?,?,?);" & VbCrLf & _
    "INSERT INTO StandardPaths ([TagNum], [Title], [Path]) values (?,?,?);" & VbCrLf & _
    "INSERT INTO StandardPaths ([TagNum], [Title], [Path]) values (?,?,?);"

Using connection As New OleDbConnection("connection string here"), _
      cmd As New OleDbCommand(sql, connection)

    'Guessing at column types and lengths. Use actual types from your database here
    cmd.Parameters.Add("TagNum1", OleDbType.VarChar, 2).Value = "A1"
    cmd.Parameters.Add("Title1", OleDbType.VarWChar, 100).Value = StandardPath_LabelA1.Text
    cmd.Parameters.Add("Path1", OleDbType.VarWChar, 512).Value = StandardPath_TextBoxA1.Text
    cmd.Parameters.Add("TagNum2", OleDbType.VarChar, 2).Value = "A2"
    cmd.Parameters.Add("Title2", OleDbType.VarWChar, 100).Value = StandardPath_LabelA2.Text
    cmd.Parameters.Add("Path2", OleDbType.VarWChar, 512).Value = StandardPath_TextBoxA2.Text
    cmd.Parameters.Add("TagNum3", OleDbType.VarChar, 2).Value = "A3" 
    cmd.Parameters.Add("Title3", OleDbType.VarWChar, 100).Value = StandardPath_LabelA3.Text
    cmd.Parameters.Add("Path3", OleDbType.VarWChar, 512).Value = StandardPath_TextBoxA3.Text

    connection.Open()

    Try
        cmd.ExecuteNonQuery()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Using

I know the repeated code is often seen as bad, but for three items sometimes a loop is just overkill. In this case, skipping the loop also gives an advantage of reducing you to a single atomic trip to the database.