Type Mismatch error 13 in vb6, during ADODB Recordset loop

Question

Private Sub cmdShow_Click()
    'lblTotPur/lblTotPayRet/lblBalance
    Dim strShow, mSlNo
    Dim rsShow As New ADODB.Recordset
    Dim NewItem As Variant
    If Trim(txtCustomer.Text) = "" Then
        MsgBox "Please select vendor to proceed...", vbCritical, POPUP_COMP
        Exit Sub
    End If

    Dim recCnt
    pgrPartyLedger.Min = 0
    recCnt = 0
    'VOUCHMST_P//VNO,DATED,VTYPE,REMARKS,byUser CASH PURCHASE RETURN
    strShow = "select Count(*) as mCnt from VOUCHMST A,VOUCHDAT B, mPurchase C "
    strShow = strShow & " where A.VNO=B.VNO and (A.REMARKS='CASH PURCHASE RETURN' OR A.REMARKS='CREDIT PURCHASE RETURN' OR A.REMARKS='CREDIT PURCHASE' OR A.REMARKS='CASH PURCHASE' OR A.REMARKS='PAYMENT' OR A.REMARKS='CREDIT NOTE') "
    strShow = strShow & " and A.DATED between #" & Format(dtFrom.Value, "MM/dd/yyyy") & "# "
    strShow = strShow & " and #" & Format(dtTo.Value, "MM/dd/yyyy") & "# "
    strShow = strShow & " and B.IDNO = '" & Trim(txtCustomerId.Text) & "' "
    strShow = strShow & " and A.VREFNO = C.PurBillNo  "
    'strShow = strShow & " order by A.ID,A.DATED,A.VNO"
    rsShow.Open strShow, cn
    recCnt = rsShow("mCnt")
    rsShow.Close
    pgrPartyLedger.Max = recCnt + 1

    'VOUCHMST_P//VNO,DATED,VTYPE,REMARKS,byUser
    strShow = "select A.Id,A.cmnt,A.VNO,A.DATED,B.IDNO,B.IDNAME,B.AMOUNT,B.DR_CR,B.VNARRATION,A.REMARKS,C.DealInvNo, B.CQ_TYPE, B.BANKNAME, B.BANKBRANCH, B.CQ_NO from VOUCHMST A,VOUCHDAT B,mPurchase C "
    strShow = strShow & " where A.VNO=B.VNO and (A.REMARKS='CASH PURCHASE RETURN' OR A.REMARKS='CREDIT PURCHASE RETURN' OR A.REMARKS='CREDIT PURCHASE' OR A.REMARKS='CASH PURCHASE' OR A.REMARKS='PAYMENT' OR A.REMARKS='CREDIT NOTE') "
    strShow = strShow & " and A.DATED between #" & Format(dtFrom.Value, "MM/dd/yyyy") & "# "
    strShow = strShow & " and #" & Format(dtTo.Value, "MM/dd/yyyy") & "# "
    strShow = strShow & " and B.IDNO = '" & Trim(txtCustomerId.Text) & "' "
     strShow = strShow & " and A.VREFNO = C.PurBillNo  "
    strShow = strShow & " order by A.ID,A.DATED,A.VNO"
    rsShow.Open strShow, cn

    Dim mPur, mPayRet, mAnyAdv, mTempVNO
    mPur = 0
    mPayRet = 0
    mAnyAdv = 0

    mSlNo = 1
    ShowPaymentHeader
    Do While Not rsShow.EOF
        mTempVNO = rsShow("VNO")

        Set NewItem = listViewPayment.ListItems.Add(, "C" & mSlNo, Format(rsShow("DATED"), "dd/MM/yyyy"))
        NewItem.SubItems(1) = rsShow("VNO")
        NewItem.SubItems(13) = rsShow("DealInvNo")

        NewItem.SubItems(2) = IIf(IsNull(rsShow("IDNAME")), "", CommaFilterText(rsShow("IDNAME"), 1))
        If Trim(rsShow("REMARKS")) = "CASH PURCHASE" Then
            NewItem.SubItems(3) = FormatTakaPaisa(rsShow("AMOUNT"))
            NewItem.SubItems(4) = FormatTakaPaisa(rsShow("AMOUNT"))
        ElseIf Trim(rsShow("REMARKS")) = "CREDIT PURCHASE" Then
            mAnyAdv = ShowPartialAdvance(rsShow("VNO"))
            NewItem.SubItems(3) = FormatTakaPaisa(rsShow("AMOUNT") + Val(mAnyAdv))
            If Val(mAnyAdv) > 0 Then
                NewItem.SubItems(4) = FormatTakaPaisa(mAnyAdv)
            Else
                NewItem.SubItems(4) = ""
            End If
        Else
            NewItem.SubItems(3) = ""
            NewItem.SubItems(4) = FormatTakaPaisa(rsShow("AMOUNT"))
            NewItem.SubItems(7) = rsShow("CQ_TYPE")
            NewItem.SubItems(8) = rsShow("BANKNAME")
            NewItem.SubItems(9) = rsShow("BANKBRANCH")

            **    
            > NewItem.SubItems(9) = rsShow("cmnt")

            **

        End If
        NewItem.SubItems(6) = rsShow("IDNO")

        mPur = mPur + Val(NewItem.SubItems(3))
        mPayRet = mPayRet + Val(NewItem.SubItems(4))

        NewItem.SubItems(5) = rsShow("REMARKS")
        NewItem.SubItems(12) = GetVoucherRefNo(mTempVNO)

        pgrPartyLedger.Value = mSlNo

        mSlNo = mSlNo + 1
        rsShow.MoveNext
    Loop
    rsShow.Close

    lblTotPur.Caption = FormatTakaPaisa(mPur)
    lblTotPayRet.Caption = FormatTakaPaisa(mPayRet)
    lblBalance.Caption = FormatTakaPaisa(mPur - mPayRet)
    pgrPartyLedger.Value = 0        
End Sub

I am getting type mismatch runtime error 13 for that line, Please help me

Answer 1

Depending from your database design and from your query, you may have fields with null values or fields with zero-length strings. You should always check for this especially when you do outer joins, or multiple joins like in your query.

If you check the content of just only of a table and you see the fields are all populated, this doesn't mean the resulting set will return only valid values - but this goes beyond the scope of this answer.

There are multiple common practices to check Field values and trap errors when dealing with a Recordset. Just to name a few:

1. On Error Resume ... blocks: avoid this, you haven't an overview of what's happen

2. Check for Null values, DBNull values, Empty values or check the Field Type. Just one example:

   If IsNull(rsShow("cmnt").value) = True Then NewItem.SubItems(9) = "" Else NewItem.SubItems(9) = rsShow("cmnt") End If

   Note: you should explicit the value property of the Field.

   If you do Field Type check, learn to use the Object Browser integrated in the VB IDE, this will your best friend.

3. Check the ActualSize property of the Field: see MSDN: The Field Object

4. Another common shortcut is to prepend or append a string with zero-length to the return value of the Field, this is only valid if you don't have to distinguish between Null values and Blank values and you just only need to show the result in a label or in a list, as your case is: NewItem.SubItems(9) = "" & rsShow("cmnt")

About Error 13: In the VB IDE, open the Immediate Window, type in: ? 0 = "" and then press Enter. What is the result?

Last but not least, always double-check the names of the fields, each database developer has been faced with errors due to typos, notably when dealing with financial applications with dozens of tables and hundreds of fields.