Czech letters missing SQL & Windows collation

Hi,

I was indeed using just varchar fields. Changing them to nVarchar did not help. I am still not able to save
some of the letters.
Do you think the regional settings might have anything to do with it?

http://msdn2.microsoft.com/en-us/library/aa902644(sql.80).aspx

ALTER DATABASE Products
COLLATE Czech_CI_AI

Oops its further down....

CREATE TABLE jobs
(
   job_id  smallint
      IDENTITY(1,1)
      PRIMARY KEY CLUSTERED,
   job_desc varchar(50)
      COLLATE Czech_CI_AI
      NOT NULL
      DEFAULT 'New Position - title not formalized yet',
)

Another thing...  VB6 may or may not be Unicode aware:

http://www.vbforums.com/showthread.php?t=365738

I altered all the regional setting on my server in Czech to Czech including default non-unicode language.
I can type but cannot save 4 letters still.
I have been trying different collations without success.

Good anymore tricks?

There is one more thing that you can do...

Convert your data to UTF-8 or plain text...  you should have those weird \x?? equivalents of your czech specific strings then save it.

You can convert it back for user display.

This is if all fails.

hec",)

Not really sure what you mean. How can I convert my strings like that and how can I see the results?

I was thinking..... could this have anything to do with an ADO issue?

TJust to let you know which letters I am missing look at this link:
http://www.science.co.il/Language/Character-Code.asp?s=1250

Decimals: 200, 204, 216 & 217

ADO command object type parameter should be adVarWChar (unicode string null terminated).

Yer getting a little too technical on me the harris.
Could you please break that down into laymans terms for me.

Cheers

Can you post your VB code here.

Which code are you refering to? The update statement?
If so, there's a problem with that, namely the characters cannot be used here.
Still want it?

This is what is called as "Internationlization using VB"(google for it to learn more)...its a bit of a clumpsy thing. We have once tried to do internationlization with Collations...but there seemed to be problem with VB and not with SQL...if you use your query analyzer and try to insert with any collation it will accept but not with VB...
so after many thoughts we used some unicode third party controls that are available in the market. I can suggest you to use them, but it nearly took a month for us to do the convertion...but the results were promising.

this was some years ago(we are in .net now and no third part controls)...iam not sure wht is up in the market for VB anymore...

>Which code are you refering to? The update statement?
yes, the SQL code you use, as well as the part of the vb code you use to run the sql statement

Hey Dennis,

Are you checked if the characters aren't updated or they are just problematic to pull / display ?

Eg. What is saved instead of that characters, it's a low possibility for the sql server to just pass them. Have you tried to get the length like
select len(myField) from myTable where ID = xx
does it show the length of the string without counting the missing characters?

if they seem to be there, can you try something like
select ascii(substring(myField,characterLocationInTheString,1)) from myTable where ID = xx
and see if the character code you get is right?

btw, try not to use column collations and use db collation for the production db.

try these from query analyzer,


declare @myString varchar(50)
set @myString = char(200) -- your problematic character
select @myString, ascii(@myString)
update myTable set myField = @myString where ID = xx
select myField, len(myField), ascii(myField) from myTable where ID = xx


and


declare @myString varchar(50)
set @myString = 'A' -- your problematic character
select @myString, ascii(@myString)
update myTable set myField = @myString where ID = xx
select myField, len(myField), ascii(myField) from myTable where ID = xx

jaiganeshsrinivasan:
I have been asured that Czech is a non-unicode language, and want to avoid months of reprogramming as I only have a couple of weeks to get this up and running.

Harris / Angellll (Thanks for taking a look AngelIII !!)
Here is the code.

Public Function OpenADOcnn() As Boolean
Dim sConnStr As String
    Set AdoConn = New ADODB.Connection
    On Error GoTo errorhandler
    With AdoConn      
        sConnStr = "Provider=" & Trim(gsProvider) & "" & ";" & "Data Source=" & Trim(gsDataSource) & ""   & ";" & "OLE DB Services= -2;uid=TestDB;pwd=12345;Initial Catalog=" & Trim(gsCatalog) & "" & ";"
        .ConnectionString = sConnStr
        .Open
    End With
    AdoConn.Execute "SP_SetAppRole 'TraxIT', {ENCRYPT N '12345'}, 'odbc'"
    OpenADOcnn = True
    intConns = intConns + 1
    Exit Function
errorhandler:
    OpenADOcnn = False
    errorM "OpenADOcnn"
End Function

Private Sub UpdateCustomer()
On Error GoTo errorhandler

    OpenADOcnn
    SQLExec = "UPDATE tblCust SET " & _
              " Naam = " & SQLQuote(Me.txtNaam) & ", " & _
              " DebNr = " & Me.txtNRDebNr & ", " & _
              " CredNr = " & Me.txtNRCredNr & ", " & _
              " Straat = " & SQLQuote(Me.txtAdres) & ", " & _
              " Postcode = " & SQLQuote(Me.txtPC) & ", " & _
              " Plaats = " & SQLQuote(Me.txtPlaats) & ", " & _
              " Land= " & glLandID & ", " & _
              " Telefoonnummer = " & SQLQuote(Me.txtTelefoon) & ", " & _
              " Faxnummer = " & SQLQuote(Me.txtFax) & ", " & _
              " Email = " & SQLQuote(Me.txtEMail) & ", " & _
              " Website = " & SQLQuote(Me.txtWeb) & ", " & _
              " PBPostbus = " & Trim(SQLQuote(Me.txtPBPB)) & ", " & _
              " PBPostcode = " & SQLQuote(Me.txtPBPC) & ", " & _
              " PBPlaats = " & SQLQuote(Me.txtPBPLaats) & ", " & _
              " PBLand = " & glPBLandID & ", " & _
              " BankNaam = " & SQLQuote(Me.txtBanknaam) & ", " & _
              " BankAdres = " & SQLQuote(Me.txtBankadres) & ", " & _
              " BankPlaats = " & SQLQuote(Me.txtBankplaats) & ", " & _
              " RekeningNr = " & SQLQuote(Me.txtRekNr) & ", " & _
              " IBAN = " & SQLQuote(Me.txtIBAN) & ", " & _
              " BIC = " & SQLQuote(Me.txtBIC) & ", " & _
              " Opmerkingen = " & SQLQuote(Me.txtOpm) & ", " & _
              " BTWNR = " & SQLQuote(Me.txtBTWNr) & " " & _
              " WHERE AdresID= " & Me.txtcustID
    AdoConn.Execute SQLExec

    AdoConn.Close
    Set AdoConn = Nothing
    Exit Sub
errorhandler:
    errore frmRelatieGegevens, "UpdateCustomer"
End Sub

Copying SQLExec to Query Analyzer shows all the correct charaters.
Running it in QA works but the results are missing the right accents.

You can try to change server level collation if you havnt done that already instead of database or column level collation. But to change server level collation now you would need to rebuild the master database.

SQL_Latin1_General_CP1_CI_AS. - this is US english

ah direct sql query...

you may want to try parametrized query, or better a stored procedure. that helps to package parameters better.

MrRobot,

>>Are you checked if the characters aren't updated or they are just problematic to pull / display ?
SEM / Open rows & QA both dispaly the letters incorrectly (Only the accents (caron & ring) are missing)

>>Eg. What is saved instead of that characters, it's a low possibility for the sql server to just pass them.
Sorry, I was unclear. The letters are there but are not the correct letters.

I am only using column colations at the moment. Will try changing the db collation as well.
brb on that one.....

>>set @myString = char(200) -- your problematic character
Not sure what u mean by this. (char(200)'A') ???

>>declare @myString varchar(50)
    set @myString = 'A' -- your problematic character
     select @myString, ascii(@myString)
     update myTable set myField = @myString where ID = xx
     select myField, len(myField), ascii(myField) from myTable where ID = xx

Displays the letter without the accent ring, len = 1
 e + caron = ascii 101
 c + caron = ascii 99
 r + caron = ascii 114
 u + ring = ascii 117

Just to let you know which letters I am missing look at this link:
http://www.science.co.il/Language/Character-Code.asp?s=1250

Decimals: 200, 204, 216 & 217

let me suggest this difference:
    set @myString = N'A' -- your problematic character
    select @myString, ascii(@myString)

now, what code page is your windows os installed?
the character might be stored correctly, but your os is not "capable" of displaying it correctly?

angelIII :

it seems like it's not about displaying the character, the returned ascii codes show the ascii value is not the right one.

>>btw, try not to use column collations and use db collation for the production db.
IT WORKED !!!
What's the point of using column collations, if you can't just change it and have it work?

btw,

create a new table after the db collation is changed, and try it on the new table.

nice to know it worked.

column collations are to hold data in that collation only, they can't handle most of the conversions, so you have to sort any issues.

>>column collations are to hold data in that collation only, they can't handle most of the conversions, so you have to sort any issues.
But it wasn't holding the right data. Missing 4 letters is a very major con don't you think.
What needs to be converted? ascii codes? Doesn't/shouldn't it just return the stored value?

 e + caron = ascii 236
 c + caron = ascii 232
 r + caron = ascii 248
 u + ring = ascii 249

Oh no, my boss wants me to go to Prague by car next week now !!  1000+km :-(
I'll postpone and fly :-)

Thanks Guys !!

(AngelIII, thanks again for your attention.)

good job, people !

ok, just to "laugh" at me, can you try this on a database that is NOT Czech_CI_AI collation (for example, the master db?)

declare @myString nvarchar(30)
set @myString = N'A' COLLATE Czech_CI_AI   -- your problematic character
select @myString, ascii(@myString)

what gives?