Save VB6 values in SQL

I have this interface:

When the add button is pressed, a UserControl with the same fields is added:

So, the question is: How do I save the main values and those that have been added? That is, if the user pressed the add button, how do I also save those values?

Currently, this is the entire interface code:

Option Explicit
Private oldPos As Integer

Dim indice As Integer
Dim indicee As Integer

Dim cnn As Connection
Dim rs As Recordset
Dim sCon As String
Dim sql As Command

Private Sub btnAñadir_Click()
   indice = indice + 1
   indicee = indicee + 0

   Picture1.Visible = True

   Load uc1(indice)
   Set uc1(indice).Container = Picture1 
   uc1(indice).Visible = True
   uc1(indice).Top = IIf(indice = 1, 0, uc1(indice - 1).Top + uc1(indice - 1).Height + 20)


   Set lblTipo(indice).Container = uc1(indice)
   lblTipo(indice).Visible = True
   lblTipo(indice).Top = lblTipo(indice - 1).Top

   Load cmbAddTipo(indice)
   Set cmbAddTipo(indice).Container = uc1(indice)
   cmbAddTipo(indice).Visible = True
   cmbAddTipo(indice).Top = cmbAddTipo(indice - 1).Top
   CargarTablaEnCombo "SELECT [tipo] FROM dbo.[tipo_Numero]", Me.cmbAddTipo(indice), "tipo"


   Load lblAddPrefijo(indice)
   Set lblAddPrefijo(indice).Container = uc1(indice)
   lblAddPrefijo(indice).Visible = True
   lblAddPrefijo(indice).Top = lblAddPrefijo(indice - 1).Top

   Load txtAddPrefijo(indice)
   Set txtAddPrefijo(indice).Container = uc1(indice)
   txtAddPrefijo(indice).Visible = True
   txtAddPrefijo(indice).Top = txtAddPrefijo(indice - 1).Top


   Load lblAddNum(indice)
   Set lblAddNum(indice).Container = uc1(indice)
   lblAddNum(indice).Visible = True
   lblAddNum(indice).Top = lblAddNum(indice - 1).Top

   Load txtAddNumero(indice)
   Set txtAddNumero(indice).Container = uc1(indice)
   txtAddNumero(indice).Visible = True
   txtAddNumero(indice).Top = txtAddNumero(indice - 1).Top


   Load chkAddPrincipal(indice)
   Set chkAddPrincipal(indice).Container = uc1(indice)
   chkAddPrincipal(indice).Visible = True
   chkAddPrincipal(indice).Top = chkAddPrincipal(indice - 1).Top


   Load chkAddActual(indice)
   Set chkAddActual(indice).Container = uc1(indice)
   chkAddActual(indice).Visible = True
   chkAddActual(indice).Top = chkAddActual(indice - 1).Top


   Load lblAddVin(indice)
   Set lblAddVin(indice).Container = uc1(indice)
   lblAddVin(indice).Visible = True
   lblAddVin(indice).Top = lblAddVin(indice - 1).Top

   Load cmbAddVinculo(indice)
   Set cmbAddVinculo(indice).Container = uc1(indice)
   cmbAddVinculo(indice).Visible = True
   cmbAddVinculo(indice).Top = cmbAddVinculo(indice - 1).Top
   CargarTablaEnCombo "SELECT [tipoVinculo] FROM dbo.[tipo_Vinculo]", Me.cmbAddVinculo(1), "tipoVinculo"

   If indice = 3 Then
   indice = 0
   Me.btnAñadir.Enabled = False
   End If

End Sub

Private Sub btnGuardar_Click()
    Dim sql As String
    sql = "INSERT INTO ejemplo(nombre) VALUES("
    sql = sql & "'" & cmbAddTipo.Count & "',"
    sql = sql & "'" & txtAddPrefijo.Text & "',"
    sql = sql & "'" & txtAddNumero.Text & "',"
    sql = sql & "'" & chkAddPrincipal.Value & "',"
    sql = sql & "'" & chkAddActual.Value & "',"
    sql = sql & "'" & cmbAddVinculo.Count & "',"
    sql = sql & "'" & txtTimer.Text & "')"
    cnn.Execute sql
End Sub

Private Sub Form_Load()

   Dim theDate As Date
   theDate = Format(Now(), "short date")
   TextBox1.Text = theDate

   scrollAdd.Min = 0
   scrollAdd.Max = 3000
   scrollAdd.SmallChange = Screen.TwipsPerPixelX * 10
   scrollAdd.LargeChange = scrollAdd.SmallChange

   Call IniciarConexion
   CargarTablaEnCombo "SELECT [tipo] FROM dbo.[tipo_Numero]", Me.cmbAddTipo(0), "tipo"
   CargarTablaEnCombo "SELECT [tipoVinculo] FROM dbo.[tipo_Vinculo]", Me.cmbAddVinculo(0), "tipoVinculo"

End Sub

Sub CargarTablaEnCombo(ByVal sql As String, comboBox As Object, ByVal Campo As String)
    Set rs = New ADODB.Recordset
    rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
    While Not rs.EOF
        If Not IsNull(rs.Fields(Campo)) Then
            comboBox.AddItem rs.Fields(Campo)
        End If
    rs.MoveNext
    Wend
End Sub

Function cerrarRecordset(R As Recordset)
    On Error Resume Next
    If Not R Is Nothing Then
        If R.State = adStateOpen Then
            R.Close
            Set R = Nothing
        Else
            Set R = Nothing
        End If
    End If
    Err.Clear
    On Error GoTo 0
End Function

Private Sub IniciarConexion()
    Set cnn = New ADODB.Connection
    With cnn
        .CursorLocation = adUseClient
        .Open "PROVIDER=MSDASQL;driver={SQL Server};server=server;uid=uid;pwd=password;database=database;"
    End With
End Sub

Private Sub Form_Unload(Cancel As Integer)
On Local Error Resume Next
cnn.Close
Set cnn = Nothing
End Sub

Private Sub scrollAdd_Change()
   ScrollControls
End Sub

Private Sub scrollAdd_Scroll()
   ScrollControls
End Sub

Private Sub ScrollControls()
   Dim c As Control

   For Each c In Me.Controls
      If c.Container.Name = "Picture1" And Not TypeOf c Is VScrollBar Then
         c.Top = c.Top + (oldPos - scrollAdd.Value)
      End If
   Next

   oldPos = scrollAdd.Value
End Sub

Answer

Your app allows the creation of any number of UserControls. At some point, you will need to save all this data. One approach is to to loop through those UserControl’s and build an Insert statement. As mentioned in the comments, it is wise to sanitize the input and to use parameters. Something like this:

Private Sub btnGuardar_Click()
   Dim i As Integer
   Dim CM As ADODB.Command

   For i = 0 To NumberOfUserControls - 1
      Set CM = New ADODB.Command
      Set CM.ActiveConnection = cnn
      CM.CommandType = adCmdText
      CM.CommandText = "INSERT INTO ejemplo (nombre) VALUES (?)"
      CM.Parameters.Append CM.CreateParameter("@nombre", adInteger, , , uc1(i).Nombre)
      CM.Execute , , adExecuteNoRecords
   Next
End Sub

For simplicity, this code assumes you know the number of UserControl’s. It also builds upon the ideas I presented in this answer. You will need to sanitize the data and add additional Parameters as needed.

Advertisement

Answer