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

24CaratAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
varSQLcmd = varSQLcmd & vrLIMIT   '"LIMIT 0 , 500"

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

varSQLcmd = varSQLcmd & "LIMIT 0 , 500"
0
24CaratAuthor Commented:
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.

0
24CaratAuthor Commented:
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

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

24CaratAuthor Commented:
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
0
Mark WillsTopic AdvisorCommented:
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...
0
24CaratAuthor Commented:
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?
0
Steve BinkCommented:
What character set is the table in MSSQL?  Have you tried explicitly converting the character set in the query?
0
24CaratAuthor Commented:
productlist character set: utf8 -- UTF-8 Unicode
prdbesarch character set: utf8 -- UTF-8 Unicode
teststock character set: utf8 -- UTF-8 Unicode
0
24CaratAuthor Commented:
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.
0
Steve BinkCommented:
>>> Maybe it's because it 's one of those 'free' programs that it has its bugs and limitations somewhere

So the database holds the information correctly, a third-party app displays it correctly, but because VB doesn't show the data correctly you believe the database is at fault?  This is more likely a case of Microsoft insisting you do things their way instead of by the standards that the rest of the development community acknowledges...they do have that reputation...

Case in point, try this:

http://nishantpant.wordpress.com/2007/01/04/how-to-convertdisplay-a-unicode-string-in-vbnet/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve BinkCommented:
Why the 'C' grade?  What information are you missing?  I do not think it is appropriate here.
0
24CaratAuthor Commented:
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.
0
Steve BinkCommented:
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/

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.