ADODB not getting all the data from one field

[paulked]

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?

[Kenneth Hobs]

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

[paulked]

Thanks for response but no, it is text utf8mb4_unicode_ci

[Aflatoon]

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

[paulked]

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

25142

The same query from Excel:

25143

not even a comma!

[Aflatoon]

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?

[paulked]

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

[paulked]

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.

[paulked]

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

[Kenneth Hobs]

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

[paulked]

I'll give it a try, thanks

[paulked]

That was interesting!

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

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.

[snb]

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

[paulked]

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.

[Kenneth Hobs]

Using Fields() method, did you try WorksheetFunction.Clean()? It is limited in what it can do though.

https://docs.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.clean?f1url=https%3A%2F%2Fmsdn.microsoft.com%2Fquer y%2Fdev11.query%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(vbaxl10.chm137136)%3Bk(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue

[paulked]

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

[Kenneth Hobs]

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

[paulked]

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

[paulked]

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

Thanks :thumb