?
Solved

Query result gives strange (wrong) characters when executed.

Posted on 2008-11-10
17
Medium Priority
?
296 Views
Last Modified: 2012-05-05
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
Comment
Question by:24Carat
13 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22926945
varSQLcmd = varSQLcmd & vrLIMIT   '"LIMIT 0 , 500"

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

varSQLcmd = varSQLcmd & "LIMIT 0 , 500"
0
 

Author Comment

by:24Carat
ID: 22929716
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
 

Author Comment

by:24Carat
ID: 22929756
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:24Carat
ID: 22929852
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23041559
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
 

Author Comment

by:24Carat
ID: 23045619
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
 
LVL 51

Expert Comment

by:Steve Bink
ID: 23566350
What character set is the table in MSSQL?  Have you tried explicitly converting the character set in the query?
0
 

Author Comment

by:24Carat
ID: 23570615
productlist character set: utf8 -- UTF-8 Unicode
prdbesarch character set: utf8 -- UTF-8 Unicode
teststock character set: utf8 -- UTF-8 Unicode
0
 

Author Comment

by:24Carat
ID: 23570757
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
 
LVL 51

Accepted Solution

by:
Steve Bink earned 1500 total points
ID: 23577141
>>> 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
 
LVL 51

Expert Comment

by:Steve Bink
ID: 23618245
Why the 'C' grade?  What information are you missing?  I do not think it is appropriate here.
0
 

Author Comment

by:24Carat
ID: 23620462
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
 
LVL 51

Expert Comment

by:Steve Bink
ID: 23638969
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question