• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

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

0
24Carat
Asked:
24Carat
1 Solution
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now