Problem storing a string with special characters

[lechuck]

I've noticed that a string I stored into SQL server does not appeare to be the same as the orginal string.
If I have the string "Œ‘•" and store it into the database field that is of type varChar, then I read the string and get the value: "O_'¤". I'm using the VB code bellow.
Can someone please explain why the string is converted, and what I shall do to get the exact string stored in the database without this unexpected conversion.

Regards
LeChuck

Dim rst As New ADODB.Recordset
Dim sBeforeStored As String
Dim sReadFromSQL As String
Dim i As Integer

sBeforeStored = Chr(140) & Chr(141) & Chr(145) & Chr(149) & Chr(15)

'Store the string in the SQL server.
rst.Open "SELECT * FROM myTable", myConnection
rst.AddNew
rst("myColumn&quot = sBeforeStored
rst.Update
rst.Close

'Reads the string from the SQL server.
rst.Open "SELECT * FROM myTable"
sReadFromSQL = rst("myColumn&quot
For i = 1 To Len(sReadFromSQL)
MsgBox "sBeforeStored: " & Asc(Mid(sBeforeStored,i,1)) & vbCrLf & "sReadFromSQL: " & Asc(Mid(sReadFromSQL,i,1))
Next i
rst.close

 

[chiph]

How is the column declared?

Chip H.

 

[lechuck]

The column is varChar(50). I found this in SQL Server Books Online:

Checking the Validity of Saved Data When Using the Automatic ANSI to OEM Conversion Option
Another way to reveal the code page is to set Automatic ANSI to OEM conversion to OFF and query the data from SQL Server Query Analyzer.

For example, assume you have saved the character “±” on a server using the OEM code page 437. If you select this data from SQL Server Query Analyzer (which is using ANSI code page 1252) when Automatic ANSI to OEM conversion is on, you see the “±” character. The OEM 437 “±” (which has an ASCII value of 241) has been converted to the ANSI 1252 “±” (ASCII 177). However, if you select the data from SQL Server Query Analyzer when Automatic ANSI to OEM conversion is off, you see the “ñ” character (ASCII 241). The OEM 437 ASCII value of 241 has been directly replaced by the ANSI 1252 ASCII value of 241.

 

[chiph]

Can you change the column type to NVarChar(50) to take advantage of Unicode?

Chip H.