Search code examples
sql-servercomboboxtextboxvb6

Load and save values in VB6


So, this is my interface:

enter image description here

This is my UserControl:

enter image description here

TextBox = Text1 / ComboBox = cmbAddExample

This is the code from UserControl:

Option Explicit
Dim cnn As Connection
Dim rs As Recordset
Dim sql As Command

Private Sub UserControl_Initialize()
   Set rs = New Recordset
   rs.CursorLocation = adUseServer
   Call IniciarConexion
   CargaIDTipoNumero
End Sub

Public Property Get AddType() As String
   AddType = cmbAddExample.Text
End Property

Public Property Let AddType(ByVal Value As String)
   cmbAddExample.Text = Value
End Property

Public Property Get AddNumber() As String
   AddNumber = Text1.Text
End Property

Public Property Let AddNumber(ByVal Value As String)
   Text1.Text = Value
End Property

Private Sub CargaIDTipoNumero()
cmbAddExample.Clear
rs.Open "SELECT idTipo, tipo FROM tipo_Numero", cnn, adOpenDynamic, adLockOptimistic
Do While rs.EOF = False
    cmbAddExample.AddItem rs!tipo
    cmbAddExample.ItemData(cmbAddExample.NewIndex) = rs!idTipo
    rs.MoveNext
Loop
rs.Close
End Sub

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

In my form, I have these methods to connect and load data from SQLServer:

Private Sub Form_Load()
   Set rs = New Recordset
   rs.CursorLocation = adUseServer
   Picture1.Visible = False
   Call IniciarConexion
   CargaIDTipoNumero
End Sub

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

Private Sub CargaIDTipoNumero()
cmbAddExample(indice).Clear
rs.Open "SELECT idTipo, tipo FROM tipo_Numero", cnn, adOpenDynamic, adLockOptimistic
Do While rs.EOF = False
    cmbAddExample(indice).AddItem rs!tipo
    cmbAddExample(indice).ItemData(cmbAddExample(indice).NewIndex) = rs!idTipo
    rs.MoveNext
Loop
rs.Close
End Sub

So, the problem is as follows:

enter image description here

compilation error sub or function not defined

in line: cmbAddExample(indice).Clear

Finally, I have a "Guardar" button that saves the data in SQLServer:

Private Sub btnGuardar_Click()
Dim i As Integer
Dim id As Integer

Dim CM As ADODB.Command

For i = 0 To indice
id = cmbAddExample(i).ItemData(cmbAddExample(i).ListIndex)

Set CM = New ADODB.Command
Set CM.ActiveConnection = cnn
    CM.CommandType = adCmdText
    CM.CommandText = "INSERT INTO ejemplo (combo,nombre) VALUES (?,?)"
    CM.Parameters.Append CM.CreateParameter("@txt", adInteger, , , id)
    CM.Parameters.Append CM.CreateParameter("@comboDos", adInteger, , , Text1(i).Text)
    CM.Execute , , adExecuteNoRecords
Next
End Sub

How do I load the ComboBox with the SQLServer data? And how do I save them?


Solution

  • To load a combo inside a UserControl and later save the selection, I would add additional methods and properties to the UserControl:

    Public Sub LoadAddType(ByVal Data As ADODB.Recordset)
       cmbAddType.Clear
       Data.MoveFirst
    
       Do While Not Data.EOF
           cmbAddType.AddItem Data!tipo
           cmbAddType.ItemData(cmbAddType.NewIndex) = Data!idTipo
           Data.MoveNext
       Loop
    End Sub
    
    Public Sub LoadAddType2()
       ***or this could be in UserControl_Initialize()
       cmbAddType.Clear
       cmbAddType.AddItem "celular"
       cmbAddType.AddItem "option2"
    End Sub
    
    Public Property Get AddTypeID() As Integer
       AddTypeID = cmbAddType.ItemData(cmbAddType.ListIndex)
    End Property
    

    Keeping all the database connection and querying on the main form, you could then initialize your UserControls like this:

    Private Sub Form_Load()
        uc1(0).LoadAddType rs
    End Sub
    

    And every time you add a UserControl:

    Private Sub btnAdd_Click()
        uc1(indice).LoadAddType rs
    End Sub
    

    To save user selections, the general idea would be:

    Private Sub btnGuardar_Click()
       Dim i As Integer
       Dim id As Integer
       Dim sel As String
    
       Dim CM As ADODB.Command
    
       For i = 0 To indice
          id = uc1(i).AddTypeID
          sel = uc1(i).AddType
    
          ***with the retrieved data, build your CM object
       Next
    End Sub