Consulting

Results 1 to 19 of 19

Thread: ADODB not getting all the data from one field

  1. #1
    VBAX Mentor paulked's Avatar
    Joined
    Apr 2006
    Posts
    393
    Location

    ADODB not getting all the data from one field

    Hi all

    After querying a database with

    Sub GetAppData()
        If Not IsInternetConnected Then
            If Data.Cells(2, 2) <> 1 Then frmNoInternet.Show
            Exit Sub
        End If
        Dim cn As New ADODB.Connection, rs As New ADODB.Recordset, i As Long
        On Error GoTo Link
    Conn:
        #If Win64 Then
        cn.Open "Provider=MSDASQL;DRIVER={mysql odbc 5.2 unicode driver}" _
            & ";SERVER=localhost" _
            & ";PORT=3306" _
            & ";DATABASE=fringell_DbgTfg0O" _
            & ";UID=" _
            & ";PWD="
        #Else
        cn.Open "DRIVER={MySQL ODBC 3.51 Driver}" _
            & ";SERVER=localhost" _
            & ";PORT=3306" _
            & ";DATABASE=fringell_DbgTfg0O" _
            & ";UID=" _
            & ";PWD="
        #End If
        On Error GoTo Oops
        rs.CursorLocation = adUseClient
        rs.Open "SELECT * FROM jos_visforms_1", cn, adOpenDynamic
        With AppData
            .Cells.ClearContents
            For i = 0 To rs.Fields.Count - 1
                .Cells(1, i + 1) = rs.Fields(i).Name
            Next
            .Cells(2, 1).CopyFromRecordset rs
        End With
        rs.Close
        cn.Close
        Set rs = Nothing
        Set cn = Nothing
        Exit Sub
    Link:
        Dim yesno As String
        yesno = MsgBox("It looks like you are not connected through the SSH Tunnel.  Do you want to try connecting now?", _
            vbYesNo + vbInformation, "No connection to Database...")
        If yesno = vbYes Then
            PlinkConnect
            GoTo Conn
        End If
    Oops:
        MsgBox Err.Number & "; " & Err.Description
    End Sub
    I get all the data in the table from all fields except one.

    Example


    Record contains I'm getting
    Fri 17, Sat 18, Fri 24, Sat 25 Fri 17
    Sun 19, Mon 20, Tue 21, Sun 26 Sun 19

    There are other fields in the DB that contain commas but I'm getting those ok.

    Any ideas?
    Semper in excretia sumus; solum profundum variat.

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,884
    Location
    Is it a datetime value? If so, you can set the numberformat before or after...

  3. #3
    VBAX Mentor paulked's Avatar
    Joined
    Apr 2006
    Posts
    393
    Location
    Thanks for response but no, it is text utf8mb4_unicode_ci
    Semper in excretia sumus; solum profundum variat.

  4. #4
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,597
    Location
    Are you sure there are no weird things like null characters or line feeds in that field?
    Be as you wish to seem

  5. #5
    VBAX Mentor paulked's Avatar
    Joined
    Apr 2006
    Posts
    393
    Location
    Sure. If I query from the website I get the correct results:

    VBAX23.png

    The same query from Excel:

    VBAX24.png

    not even a comma!
    Semper in excretia sumus; solum profundum variat.

  6. #6
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,597
    Location
    That doesn't necessarily answer my question. It's quite possible that the web script copes with such characters whereas Excel doesn't. Have you checked the data in the recordset itself before you return it to the sheet?
    Be as you wish to seem

  7. #7
    VBAX Mentor paulked's Avatar
    Joined
    Apr 2006
    Posts
    393
    Location
    Yes, they're just normal spaces. Do you think it possible there could be a 'hidden' character before the comma?
    eg Fr1 17(something here?), Sat 18(something here?), ....
    Semper in excretia sumus; solum profundum variat.

  8. #8
    VBAX Mentor paulked's Avatar
    Joined
    Apr 2006
    Posts
    393
    Location
    I copied the string from the web and then got the ASCII codes back as follows:

    F 70
    r 114
    i 105
    32
    1 49
    7 55
    , 44
    32
    S 83
    a 97
    t 116
    32
    1 49
    8 56
    , 44
    32
    F 70
    r 114
    i 105
    32
    2 50
    4 52
    , 44
    32
    S 83
    a 97
    t 116
    32
    2 50
    5 53

    So I don't see any special characters.
    Semper in excretia sumus; solum profundum variat.

  9. #9
    VBAX Mentor paulked's Avatar
    Joined
    Apr 2006
    Posts
    393
    Location
    I've tried changing the text format from utf8mb4_unicode_ci to ascii_general_ci but that doesn't make a difference.
    Semper in excretia sumus; solum profundum variat.

  10. #10
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,884
    Location
    Have you tried getting just the Value using Fields()? Maybe CopyFromRecordSet is translating where Fields() does not for that field?

  11. #11
    VBAX Mentor paulked's Avatar
    Joined
    Apr 2006
    Posts
    393
    Location
    I'll give it a try, thanks
    Semper in excretia sumus; solum profundum variat.

  12. #12
    VBAX Mentor paulked's Avatar
    Joined
    Apr 2006
    Posts
    393
    Location
    That was interesting!

    I stepped through the sub and as I 'watched' the .Fields(28) :

    VBAX25.jpg

    So I need to filter those out somehow!

    Aflatoon, I apologise as these didn't show up when I copied the field data from the web table.
    Semper in excretia sumus; solum profundum variat.

  13. #13
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,576
    If you use wrap text you will see all data in the cell.

  14. #14
    VBAX Mentor paulked's Avatar
    Joined
    Apr 2006
    Posts
    393
    Location
    Sorry, that didn't work. The AppData.Cells(i + 2, 29), when hovered over, only showed the string up to the first comma and that's all that gets copied from the record set.
    Semper in excretia sumus; solum profundum variat.

  15. #15
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,884
    Location
    Using Fields() method, did you try WorksheetFunction.Clean()? It is limited in what it can do though.

    https://docs.microsoft.com/en-us/off...6)%26rd%3Dtrue

  16. #16
    VBAX Mentor paulked's Avatar
    Joined
    Apr 2006
    Posts
    393
    Location
    Again, thanks for the info. I tried it like:

            Do While Not (.EOF)
                AppData.Cells(i + 2, 29) = WorksheetFunction.Clean(.Fields(28))
                i = i + 1
                .MoveNext
            Loop
    But it gave the same result.

    I've asked the developer of the Joomla! add-in to see if we can change the way this obnoxious string is written to the database!

    I'll also look further into this:

    In the Unicode character set, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157).
    which Clean doesn't deal with.

    Thanks again
    Semper in excretia sumus; solum profundum variat.

  17. #17
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,884
    Location
    I just created this so I don't know if it will help any.
    Function eClean(aStr, Optional aA, Optional tfTrim As Boolean = True, Optional iCompare = 0)
        Dim iv, a
        a = WorksheetFunction.Clean(aStr)
        '160, https://en.wikipedia.org/wiki/Non-breaking_space
        For Each iv In Array(127, 129, 141, 143, 144, 157, 160)
            a = Replace(a, Chr(iv), "", Compare:=iCompare)
        Next iv
        If IsArray(aA) Then
            For Each iv In aA
            a = Replace(a, Chr(iv), "", Compare:=iCompare)
        Next iv
        End If
        If tfTrim Then
            eClean = Trim(a)
            Else
            eClean = a
        End If
    End Function

  18. #18
    VBAX Mentor paulked's Avatar
    Joined
    Apr 2006
    Posts
    393
    Location
    Got it as you posted that!!

    I ran
                For j = 1 To Len(.Fields(28))
                    Debug.Print Asc(Mid(.Fields(28), j, 1))
                Next
    and found that it was ASCII 0 upsetting things. So executing:

        cn.Execute "UPDATE jos_visforms_1 SET F44 = REPLACE(F44, CHAR(0), '')"
        rs.Open "SELECT * FROM jos_visforms_1", cn, adOpenDynamic
    cleaned them out before the select query. Any others in new records added will be swept away!

    Thanks for all your help, perseverance prevailed
    Semper in excretia sumus; solum profundum variat.

  19. #19
    VBAX Mentor paulked's Avatar
    Joined
    Apr 2006
    Posts
    393
    Location
    Ken, I've popped that function into my toolbox, it may prove very handy one day.

    Thanks
    Semper in excretia sumus; solum profundum variat.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •