Problem to save data from ComboBox in VB6

Question

I've a ComboBox but the problem is that when I try to save the value selected, 'cause I load the ComboBox with values of SQLServer and the applications shows me the next error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'celular'.

I load the ComboBox with values of SQL:

Private Sub Form_Load()
   Set rs = New Recordset
   rs.CursorLocation = adUseServer

   Call IniciarConexion

   CargaIDTipoNumero
End Sub

Private Sub CargaIDTipoNumero()
    cmbAddExample(indice).Clear
    rs.Open "SELECT tipo FROM tipo_Numero", cnn, adOpenDynamic, adLockOptimistic
    Do While rs.EOF = False
        cmbAddExample(indice).AddItem rs!tipo
        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

The table where saves the values of ComboBox:

CREATE TABLE ejTres(
    combo int
)

The table with I load the ComboBox:

CREATE TABLE tipo_Numero(
    idTipo INT IDENTITY (1,1) NOT NULL, 
    tipo VARCHAR (10) NOT NULL, 
    CONSTRAINT pk_tipo PRIMARY KEY(idTipo)
)

INSERT INTO tipo_Numero(tipo)
VALUES('celular'), ('fijo')

And the Button where I save the values:

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

Set CM = New ADODB.Command
Set CM.ActiveConnection = cnn
    CM.CommandType = adCmdText
    CM.CommandText = "INSERT INTO ejTres (combo) VALUES (?)"
    CM.Parameters.Append CM.CreateParameter("@combo", adInteger, , , cmbAddExample(i))
    CM.Execute , , adExecuteNoRecords
End Sub

You *really* need to parametrise your code before anything. This is open to Injection as it is. — Thom A, Jan 08 '20 at 13:20
Print out `sql` before executing it. Then please see https://stackoverflow.com/q/332365/11683. — GSerg, Jan 08 '20 at 13:21
A parameterized query will not only avoid the security issue, it will prevent the invalid column name error. — Dan Guzman, Jan 08 '20 at 13:26
It appears you are loading the combo with descriptive strings and then want to save the related identity value to the other table. Is this an accurate statement? — Brian M Stafford, Jan 08 '20 at 13:35
As you are loading the combo, you could put the identity value into `ItemData`. Then during save, reference `ItemData` as you are creating the parameter. — Brian M Stafford, Jan 08 '20 at 13:53
@FranqoBalsamo, [the documentation](https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/parameters-collection-ado) includes an example. — Dan Guzman, Jan 08 '20 at 14:02
I read the documentation but i don't understand how to save the identity value selected in the combobox. This is my problem @DanGuzman — Franqo Balsamo, Jan 08 '20 at 14:19

Brian M Stafford · Accepted Answer · 2020-01-08T21:21:21.320

1

Focusing only on the issue of how to get the Identity value into your table, try something like the following:

''as you load the combo, also save the identity value

Do While rs.EOF = False
   cmbAddExample(indice).AddItem rs!tipo
   cmbAddExample(indice).ItemData(cmbAddExample(indice).NewIndex) = rs!idTipo
   rs.MoveNext
Loop


''now retrieve the identity value when creating the parameter

id = cmbAddExample(i).ItemData(cmbAddExample(i).ListIndex)

Set CM = New ADODB.Command
Set CM.ActiveConnection = cnn
CM.CommandType = adCmdText
CM.CommandText = "INSERT INTO ejTres (combo) VALUES (?)"
CM.Parameters.Append CM.CreateParameter("@combo", adInteger, , , id)
CM.Execute , , adExecuteNoRecords

edited Jan 08 '20 at 21:21

answered Jan 08 '20 at 14:25

Brian M Stafford

8,483
2
16
25

id is a variable? – Franqo Balsamo Jan 08 '20 at 14:32
1

Yes. `id` would be either Integer or Long. I just broke it out for clarity in what I was trying to show. – Brian M Stafford Jan 08 '20 at 14:34

Problem to save data from ComboBox in VB6

1 Answers1