Getting data from SQL Server to Excel VBA Listview

Question

im trying to get the data in my database and show it in my listview (userform). I ran into an error saying the "Type Mismatch" im quite confuse on what part is the mismatch, already search what this error means but i've declare all my variables in correct data types. But since I have blanks or with null value it displays a error. On this line If not IsNull(Records(j, i)) Then li.ListSubItems.Add , , Records(j, i) Im trying to display null or empty on listview the mismatch error was gone but if column1 is blank and column2 have is not blank, the values on column2 will be pasted on column1.

Private Sub ListView_Data()
    On Error GoTo ERR_Handler
    Dim sql As String
    Dim con As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim Records As Variant
    Dim i As Long, j As Long
    Dim RecCount As Long
    
    
    Set con = New ADODB.Connection
    
    con.Open "Provider=SQLOLEDB;Data Source=10.206.88.119\BIWFO;" & _
                          "Initial Catalog=TESTDB;" & _
                          "Uid=user; Pwd=pass;"
                          
    sql = "select * from [TESTDB].[dbo].[tbl_MN_Omega_Raw]"
    
    rst.Open sql, con, adOpenKeyset, adLockOptimistic
    '~~> Get the records in an array
    Records = rst.GetRows
    '~~> Get the records count
    RecCount = rst.RecordCount
    
    '~~> Populate the headers
    For i = 0 To rst.Fields.count - 1
        ListView1.ColumnHeaders.Add , , rst.Fields(i).Name, 200
    Next i
    
    rst.Close
    con.Close

    With ListView1
        .View = lvwReport
        .CheckBoxes = False
        .FullRowSelect = True
        .Gridlines = True
        .LabelWrap = True
        .LabelEdit = lvwManual
           
        Dim li As ListItem
        
        '~~> Populate the records
        For i = 0 To RecCount - 1
            Set li = .ListItems.Add(, , Records(0, i))
            For j = 1 To UBound(Records)
            
            
                If not IsNull(Records(j, i)) Then li.ListSubItems.Add , , Records(j, i)
                
                'End If
            Next j
        Next i
    End With
    
ERR_Handler:

    Select Case Err.Number
    
        Case 0
        Case Else
        
            MsgBox Err.Description, vbExclamation + vbOKOnly, Err.Number
            
    End Select
End Sub

FunThomas · Accepted Answer · 2022-10-24 14:54:23Z

2

Your type mismatch has nothing to do with null-values. It comes from a statement that looks rather unsuspicious: rst.RecordCount returns a Vartype LongLong, and assigning it to a Long throws a type mismatch.

You have 3 ways to fix this problem:

(1) Declare RecCount as Variant
(2) Convert rst.RecordCount into a Long: RecCount = CLng(rst.RecordCount)
(3) Don't use RecCount at all and instead use UBound(records, 2) (I prefer this method).

To find such errors, disable your error handler (comment the On Error Goto ERR_Handler). This will show the line that throws the error.

There is, however, a small issue when you have fields that contain null: You don't execute a li.ListSubItems.Add for those values, and therefore all values for the remaining fields of that row will be "shifted" to the left. You will need to run ListSubItems.Add also for null-Values, eg with

    Dim fValue As Variant
    fValue = Records(j, i)
    If IsNull(fValue) Then fValue = ""    ' or maybe "(null)"
    li.ListSubItems.Add , , fValue

answered Oct 24, 2022 at 14:54

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mr Nimbus Over a year ago

That was quick and well explained!! Thanks a lot sir!

Collectives™ on Stack Overflow

Getting data from SQL Server to Excel VBA Listview

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related