How to loop SQL command through set of textboxes

Question

I have a set of three panels in a single flowlayoutpanel. Panel1 contains textbox controls named title1, city1, state1, and so on. Respectively, panel2 controls will be named title2, city2, state2 and the same goes for panel3.

My goal is to collect all .text properties from all textboxes in each panel and store them in the database using the INSERT command.

My code looks like this:

Dim i As Integer = 1

Do
    connection.Open()

    cmd = New SqlCommand(Insert, connection)
    cmd.Parameters.AddWithValue("@user_id", My.Settings.CurrentUserID)
    cmd.Parameters.AddWithValue("@title", ("title" + i.ToString))
    cmd.Parameters.AddWithValue("@job_status", " ")
    cmd.Parameters.AddWithValue("@jobnumber", i)
    cmd.Parameters.AddWithValue("@employer", ("employer" + i.ToString))
    cmd.Parameters.AddWithValue("@city", ("city" + i.ToString))
    cmd.Parameters.AddWithValue("@state", ("state" + i.ToString))
    cmd.Parameters.AddWithValue("@startdate", ("startdate" + i.ToString))
    cmd.Parameters.AddWithValue("@enddate", ("enddate" + i.ToString))
    cmd.Parameters.AddWithValue("@jobinfo", ("jobinfo" + i.ToString))
    cmd.ExecuteNonQuery()

    i = i + 1
    connection.Close()
Loop Until i = 4

This loop works fine, inserting 3 rows of values in the table but it actually inserts String (title1, title2), not .Text property of the string named textbox

I've tried finding ways to convert string to a control name but couldn't find anything that would work for me

Alternatively, I tried to do this:

cmd.Parameters.AddWithValue("@title", ("title" + i.ToString).text)

But it gave me this error:

'Text' is not a member 'String'

There is a hard way of achieving my goal by doing a bunch of If statements and manually writing code for each textbox name but for now, I'm trying to do it as effectively as I could.

Looking forward to your suggestions!

Edit: Following @JoelCoehoorn solution, I was able to solve my problem

cmd = New SqlCommand(Insert, connection)

cmd.Parameters.Add("@user_id", SqlDbType.Int).Value = My.Settings.CurrentUserID
cmd.Parameters.Add("@title", SqlDbType.VarChar, 100)
cmd.Parameters.Add("@job_status", SqlDbType.VarChar, 20).Value = " "
cmd.Parameters.Add("@jobnumber", SqlDbType.Int)
cmd.Parameters.Add("@employer", SqlDbType.VarChar, 100)
cmd.Parameters.Add("@city", SqlDbType.VarChar, 100)
cmd.Parameters.Add("@state", SqlDbType.Char, 100)
cmd.Parameters.Add("@startdate", SqlDbType.VarChar, 20)
cmd.Parameters.Add("@enddate", SqlDbType.VarChar, 20)
cmd.Parameters.Add("@jobinfo", SqlDbType.VarChar, 1000)

connection.Open()

For i As Integer = 1 To 3
    Dim pnl As System.Windows.Forms.Panel = CType(FlowLayoutPanel2.Controls($"WorkPanel{i}"), Control)
    Dim targetBox As TextBox
    Dim datepicker As DateTimePicker

    targetBox = CType(pnl.Controls($"title{i}"), Control)
    cmd.Parameters("@title").Value = targetBox.Text

    cmd.Parameters("@jobnumber").Value = i

    targetBox = CType(pnl.Controls($"employer{i}"), Control)
    cmd.Parameters("@employer").Value = targetBox.Text

    targetBox = CType(pnl.Controls($"city{i}"), Control)
    cmd.Parameters("@city").Value = targetBox.Text

    targetBox = CType(pnl.Controls($"state{i}"), Control)
    cmd.Parameters("@state").Value = targetBox.Text

    datepicker = CType(pnl.Controls($"startdate{i}"), Control)
    cmd.Parameters("@startdate").Value = datepicker.Value

    datepicker = CType(pnl.Controls($"enddate{i}"), Control)
    cmd.Parameters("@enddate").Value = datepicker.Value

    targetBox = CType(pnl.Controls($"jobinfo{i}"), Control)
    cmd.Parameters("@jobinfo").Value = targetBox.Text

    cmd.ExecuteNonQuery()
Next

Answer 1

There's a lot we can do here. See the comments for explanations.

'Thanks to connection pooling, you should think about 
' creating a new connection object for each operation. Really. 
Using connection As New SqlConnection("connection string here"), _
      cmd As New SqlCommand(Insert, connection)

    'Avoid AddWithValue. It has to guess at types, and when (not if) it guesses
    ' wrong it causes index mismatches, which can create severe performance issues.
    'Instead, tell the computer what database types you expect.
    'I have to guess for this answer, but you can replace with exact types from the DB
    cmd.Parameters.Add("@user_id", SqlDbType.Int).Value = My.Settings.CurrentUserID
    cmd.Parameters.Add("@title", SqlDbType.NVarChar, 40)
    cmd.Parameters.Add("@job_status", SqlDbType.VarChar, 10).Value =  " "
    cmd.Parameters.Add("@jobnumber", SqlDbType.Int)
    cmd.Parameters.Add("@employer", SqlDbtype.NVarChar, 80)
    cmd.Parameters.Add("@city", SqlDbtype.NVarChar, 80)
    cmd.Parameters.Add("@state", SqlDbType.Char, 6)
    cmd.Parameters.Add("@startdate", SqlDbType.DateTime)
    cmd.Parameters.Add("@enddate", SqlDbType.DateTime)
    cmd.Parameters.Add("@jobinfo", SqDbType.NVarChar, 120)

    'A tight loop like this is the rare exception to creating new connections.
    ' In this situation, open the connection once before the loop, close it after
    connection.Open()
    For i As Integer = 1 To 3
        'Inside the loop, we only need to update values for parameters that change

        'Use the "Find()" function in Winforms to search for a control by name
        'String interpolation can help build the names
        Dim pnl As Panel = FlowLayoutPanel1.Controls.Find($"Panel{i}")
        Dim target As Control

        target = pnl.Controls.Find($"title{i}", True)
        cmd.Parameters("@title").Value = target.Text
        cmd.Parameters("@jobnumber").Value = i
        target = pnl.Controls.Find($"employer{i}", True)
        cmd.Parameters("@employer").Value = target.Text
        target = pnlControls.Find($"city{i}", True)
        cmd.Parameters("@city").Value = target.Text
        target = pnl.Controls.Find($"state{i}", True)
        cmd.Parameters("@state").Value = target.Text
        target = pnl.Controls.Find($"startdate{i}", True)
        cmd.Parameters("@startdate").Value = DateTime.Parse(target.Text)
        target = pnl.Controls.Find($"enddate{i}", True)
        cmd.Parameters("@enddate").Value = DateTime.Parse(target.Text)
        target = pnl.Controls.Find($"jobinfo{i}", True)
        cmd.Parameters("@jobinfo").Value = target.Text

        cmd.ExecuteNonQuery()
    Next

'The Using block is a MUCH safer way to call connection.Close()
' It ensures the connection still closes if an exception is thrown
End Using

Going even a little deeper, for this situation I would create a new control, either custom control or user control, based on a Panel with the textboxes. Then put this new control type into the FlowLayoutPanel. The control would also have properties for each textbox field. In this way, you only need to do the Find() step once.