Getting data from SQL Server to Excel VBA Listview-CodePudding

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

CodePudding user response：

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