asked on

Query result gives strange (wrong) characters when executed.

Hi everybody,

i have this query here, when executed in my GUI Navicat, it works perfect.

Having it fit in to a VBA script to show the result in a excel VBA listbox, it show chinese characters with a ascii code.

varSQLcmd = "SELECT CASE WHEN ISNULL(k.Sailing) = 1 THEN (h.Wanted-l.Allstock) ELSE (h.Wanted-(l.Allstock+k.Sailing)) END AS Short, "
varSQLcmd = varSQLcmd & "k.Sailing,  "
varSQLcmd = varSQLcmd & "l.AllStock,  "
varSQLcmd = varSQLcmd & "g.CombArtNr,  "
varSQLcmd = varSQLcmd & "l.Assigned,  "
varSQLcmd = varSQLcmd & "l.RestStock,  "
varSQLcmd = varSQLcmd & "h.Wanted,  "
varSQLcmd = varSQLcmd & "g.CombOmschr  "
varSQLcmd = varSQLcmd & " "
varSQLcmd = varSQLcmd & "FROM(SELECT  "
varSQLcmd = varSQLcmd & "CONCAT(productlist.artcode,'_',productlist.kleurcode) AS CombArtNr,  "
varSQLcmd = varSQLcmd & "CONCAT(productlist.artomschr,' - ',productlist.artomschr2,' - ',productlist.kleuromschr) AS CombOmschr  "
varSQLcmd = varSQLcmd & "FROM productlist  "
varSQLcmd = varSQLcmd & vrWhere   '"WHERE productlist.artcode Like 'K%' "
varSQLcmd = varSQLcmd & "GROUP BY CombArtNr  "
varSQLcmd = varSQLcmd & ")g  "
varSQLcmd = varSQLcmd & " "
varSQLcmd = varSQLcmd & "LEFT JOIN (SELECT  "
varSQLcmd = varSQLcmd & "    teststock.CombArtNr,  "
varSQLcmd = varSQLcmd & "    SUM(CASE teststock.KlantNr WHEN '0' THEN 0 ELSE 1 END) AS Assigned,  "
varSQLcmd = varSQLcmd & "    SUM(CASE teststock.KlantNr WHEN '0' THEN 1 ELSE 0 END) AS RestStock,  "
varSQLcmd = varSQLcmd & "    COUNT(teststock.CombArtNr) AS AllStock  "
varSQLcmd = varSQLcmd & "    FROM teststock  "
varSQLcmd = varSQLcmd & "    GROUP BY teststock.CombArtNr  "
varSQLcmd = varSQLcmd & ")l  "
varSQLcmd = varSQLcmd & "ON g.CombArtNr = l.CombArtNr "
varSQLcmd = varSQLcmd & " "
varSQLcmd = varSQLcmd & "LEFT JOIN (SELECT  "
varSQLcmd = varSQLcmd & "CONCAT(prdbesarch.artcode,'_',prdbesarch.klrcode) AS CombArtNr,  "
varSQLcmd = varSQLcmd & "SUM(prdbesarch.qty) AS Wanted  "
varSQLcmd = varSQLcmd & "FROM prdbesarch WHERE faktuurnr = '0' AND leveringsnr = '0' "
varSQLcmd = varSQLcmd & "GROUP BY CombArtNr  "
varSQLcmd = varSQLcmd & ")h  "
varSQLcmd = varSQLcmd & "ON g.CombArtNr = h.CombArtNr  "
varSQLcmd = varSQLcmd & " "
varSQLcmd = varSQLcmd & "LEFT JOIN (SELECT  "
varSQLcmd = varSQLcmd & "DISTINCT(LocCombArtCd),  "
varSQLcmd = varSQLcmd & "Aantal-Received AS Sailing "
varSQLcmd = varSQLcmd & "FROM partspending  "
varSQLcmd = varSQLcmd & "WHERE Aantal - Received <> 0   "
varSQLcmd = varSQLcmd & "GROUP BY LocCombArtCd "
varSQLcmd = varSQLcmd & ")k  "
varSQLcmd = varSQLcmd & "ON g.CombArtNr = k.LocCombArtCd "
varSQLcmd = varSQLcmd & " "
varSQLcmd = varSQLcmd & "ORDER BY G.CombArtNr  ASC "
varSQLcmd = varSQLcmd & vrLIMIT   '"LIMIT 0 , 500"
 
    'UFPause2.TextBox1.Text = varSQLcmd
    'UFPause2.Show
    
    'Exit Sub
 
    SQLajudyCONNECT
 
    If rs.RecordCount > 0 Then
        ReDim aStockArray(rs.RecordCount, 8) As String
        For i = 1 To rs.RecordCount
                aStockArray(i - 1, 0) = rs.Fields("Short").Value & ""
                aStockArray(i - 1, 1) = rs.Fields("Sailing").Value & ""
                aStockArray(i - 1, 2) = rs.Fields("AllStock").Value & ""
                aStockArray(i - 1, 3) = rs.Fields("CombArtNr").Value & ""
                aStockArray(i - 1, 4) = rs.Fields("Assigned").Value & ""
                aStockArray(i - 1, 5) = rs.Fields("RestStock").Value & ""
                aStockArray(i - 1, 6) = rs.Fields("Wanted").Value & ""
                aStockArray(i - 1, 7) = rs.Fields("CombOmschr").Value & ""
                rs.MoveNext
        Next i
        ListBox2.List() = aStockArray
    ElseIf rs.RecordCount = 0 Then
        ListBox2.Clear
    End If

Open in new window

Kevin Cross

varSQLcmd = varSQLcmd & vrLIMIT '"LIMIT 0 , 500"

This line doesn't look correct to me. Try this:

varSQLcmd = varSQLcmd & "LIMIT 0 , 500"

24Carat

ASKER

If there is a variable in the code i've place the code behind it with what it could be just as reference.

It is behind a ' so it's conciderd a comment.

24Carat

ASKER

Maybe it 's of any use.

I've placed my connection function code here.

Public db, srv, varSQLcmd As String, cn As ADODB.Connection, rs As ADODB.Recordset
Public intAffectedRows As Integer
 
Public Function SQLajudyCONNECT()
    On Error GoTo err
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    cn.Provider = "MSDASQL"
    cn.Properties("Persist Security Info") = True
    cn.Properties("Prompt") = adPromptNever
 
    If VBA.Environ("COMPUTERNAME") = "JudyServ1" Then
    srv = "localhost"          ''''''''''' ophalen van server naam (localhost)
    Else
    srv = "JudyServ1"
    End If
    db = "ajudy"           
    cn.Properties("User ID") = "user"      
    cn.Properties("Password") = "passw"    
 
    cn.Properties("Extended Properties") = "Driver=MySQL ODBC 5.1 Driver;db=" & db & ";server=" & srv & ";Option=1"
    cn.Open
    rs.ActiveConnection = cn
    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenForwardOnly
    rs.LockType = adLockReadOnly
 
    rs.Source = varSQLcmd
    rs.Open
    Exit Function
err:
    MsgBox "Error found in function SQLajudyCONNECT at line " & Erl & vbCrLf & "The error Description is " & err.Description & vbCrLf & " and the Error number is " & err.Number
End Function

Open in new window

24Carat

ASKER

I took some screenshots so you could see what i mean.

Top is the code executed from my navicat GUI

Bottom is from by VBA Userform
Untitled-3.jpg

Mark Wills

Looks very much like your columns and the array are out of sequence -

note the -4 30 in the second screen shot. That is Short and Sailing from the top screen shot - and you are loading Short into array member 0... so by all acounts the interesting asian characters are comArtNr (obviously using unicode data types somewhere)

Could be the odbc driver ? Would be inclined to check the column sequence being returned...

24Carat

ASKER

I've changed the sequence of the array so that it has the sequence as the SQL script (top screenshot)

The problem remains.

replacing rs.Fields("CombArtNr").Value & ""
with rs.Fields(3).Name
shows the collumns name CombArtNr

interesting fact

rs.Fields("CombArtNr").Value & "abc" gives the same result where abc have also changed into chinese characters

"abc" & rs.Fields("CombArtNr").Value gives abc(+ the same chinese chars)

Could it be because CombArtNr contains an _ ? (the other collums are shown correctly.
If so why does it conflict with this script and not with other scripts wherein it's used.

I'm currently using MySQL Connector/ODBC 5.1
If it is the odbc driver there is nothing i can do about it?

Steve Bink

What character set is the table in MSSQL? Have you tried explicitly converting the character set in the query?

24Carat

ASKER

productlist character set: utf8 -- UTF-8 Unicode
prdbesarch character set: utf8 -- UTF-8 Unicode
teststock character set: utf8 -- UTF-8 Unicode

24Carat

ASKER

I've just tried changing those 3 to: latin1 -- cp1252 West European ; collation: latin1_general_ci

but the problem remaines

I believe the problem is on the VBA side or the ODBC connector.

As you can see on first screenshot (taken from Navicat GUI) it works.
The second screenshot (taken from the VBA application, which is to be used on every computer in the network) it does not work.

btw this is a MySQL server. Maybe it's because it 's one of those 'free' programs that it has its bugs and limitations somewhere, i don't know.

ASKER CERTIFIED SOLUTION

Steve Bink

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve Bink

Why the 'C' grade? What information are you missing? I do not think it is appropriate here.

24Carat

ASKER

How can i apply it to my current code in VBA (macros in excel)?

i've tried
aStockArray(i - 1, 3) = Encoding.ASCII.GetString(Encoding.Unicode.GetBytes(rs.Fields("CombArtNr").Value))

Regarding the grade, i'm at beginner level with programming. So, if i have to grade this, i would just have to rely on how understandable it is to me, or to how detailed/complete the answer is.

Steve Bink

If you needed more information, the appropriate response would have been to ask for it.

I don't know that there is a built-in VBA function to convert Unicode. You can certainly make your own though. See here:

http://unicode.org/charts/