Run-time error '-2147352571(80020005) Type mismatch

Question

I have Run-time error '-2147352571(80020005) Type mismatch in line .AddItem rs(1):

Private Sub UserForm_Activate()
    If dbconn.State = adStateClosed Then dbconn.Open strConn

    Dim rs As New ADODB.Recordset

    ListBox1.Clear
    ListBox1.ColumnCount = 6
    rs.Open "select idClient,client_name,Address,City,state,country from  tblClients ", dbconn

    Do While rs.EOF = False
        i = i + 1
        With UserForm2.ListBox1
            .AddItem rs(1) '---------> getting ERROR HERE PLEASE LET ME KNOW
            .List(.ListCount - 1, 1) = rs(2)
            .List(.ListCount - 1, 2) = rs(3)
            .List(.ListCount - 1, 3) = rs(4)
            .List(.ListCount - 1, 4) = rs(5)
            .List(.ListCount - 1, 5) = rs(0)
        End With
        rs.MoveNext
    Loop
    rs.Close

    With cmbCountry
        .AddItem "United States"
        .AddItem "Canada"
        .AddItem "Germany"
        .AddItem "Australia"
    End With
    cmbCountry.ListIndex = 0
End Sub

I don't know VB , but may be your datatype is int, but your Listbox.Text type is string, and you get error. — Jeyhun, Apr 05 '14 at 09:02
I am almost certain that this is due to `client_name` being `Null`. `ListBox` does that when you try to add a `Null`. — GSerg, Mar 09 '19 at 13:21

score 0 · Answer 1 · answered Mar 09 '19 at 13:25

I think you should start the debugger when the error occurs. Then in the Immediate Window write ? rs(1). If this throws an error, then the rs(1) part is faulty, otherwise the problem is with the .AddItem.

In the first case, watch rs in a Watch Window, and determine what properties it has. Maybe it does not have a default property by which you can access the first of something as you try with rs(1). I suppose you are interested in one of the fields, so I would try ? rs.Fields(1).

In the second case rs(1) returns something that is not convertible to string. You may try ? CStr(rs(1)) to see whether it can be converted or watch it in the Watch window. But how you continue from there depends on what you find.

`rs` is `ADODB.Recordset`. It always has a default property, `Fields`, with the default property `Item` that returns a `Field`, and `Field` has a default property, `Value`, so `rs(1)` is always `rs.Fields.Item(1).Value` [when used without `Set`](https://stackoverflow.com/a/19200523/11683). — GSerg, Mar 09 '19 at 13:31

score -1 · Answer 2 · answered Apr 05 '14 at 09:22

-1

You don't show the database schema, but from looking at the SQL it seems that the first field is numberic (idClient) and you are trying to use this with ListBox.AddItem, which expects a string.

So, you just have to convert as follows:

With UserForm2.ListBox1
    .AddItem Str(rs(1))

    ' etc. ....

End With

answered Apr 05 '14 at 09:22

Roger Rowland

25,885
11
72
113

hi this is Usman i have used this but it still showing an Run time error ' 13' type mismatched – user3391281 Apr 07 '14 at 06:24
1

Implicit conversions between numbers and strings is something VB does a lot. It could have not been the problem. Besides, `rs(1)` is the *second* field, `client_name`. – GSerg Mar 09 '19 at 13:25

Run-time error '-2147352571(80020005) Type mismatch

2 Answers2