ADODB not getting all the data from one field

Hi all

After querying a database with

Code:

---

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?

Is it a datetime value? If so, you can set the numberformat before or after...

Thanks for response but no, it is text utf8mb4_unicode_ci

Are you sure there are no weird things like null characters or line feeds in that field?

Sure. If I query from the website I get the correct results:

Attachment 25142

The same query from Excel:

Attachment 25143

not even a comma!

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?

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?), ....

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.

I've tried changing the text format from utf8mb4_unicode_ci to ascii_general_ci but that doesn't make a difference.

Have you tried getting just the Value using Fields()? Maybe CopyFromRecordSet is translating where Fields() does not for that field?

I'll give it a try, thanks

That was interesting!

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

Attachment 25144

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.

If you use wrap text you will see all data in the cell.

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.

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

Again, thanks for the info. I tried it like:

Code:

---

        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:

Quote:

---

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

I just created this so I don't know if it will help any.

Code:

---

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

---

Got it as you posted that!!

I ran

Code:

---

            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:

Code:

---

    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 :clap:

Ken, I've popped that function into my toolbox, it may prove very handy one day.

Thanks :thumb