ADO.NET and SQL2000 and Arabic data

[mamian]

I have a project made with VB6 using RDO connection to connect to SQL 2000 to a database with collation sequence SQL_Latin1_General_CP1_CI_AS, same collation is used for the columns, This database contains Arabic Data in VARCHAR fields
I wrote a small program with VB.Net 2005 that connect to the same database using ADO.Net.
The problem I faced is that this program couldn’t read and save Arabic in the database. I tried to change the fields’ data type from VARCHAR to NVARCHAR this way I could save Arabic and read only the newly entered data after the data type changed after that the old VB6 project stopped read Arabic.
I need these 2 projects to read and write Arabic data
Do you have any suggestions? Please help.

[mamian]

the RDO connection I'm using is :
dim cn as As rdoConnection
cn.Connect = "uid=" & UserName & ";pwd=" & Pwd & ";server=" & ServerName & ";driver={SQL Server};database=" & DBName & ";AutoTranslate='No';DSN='';"
cn.CursorDriver = rdUseOdbc
cn.EstablishConnection rdDriverNoPrompt
cn.QueryTimeout = 3000

the ADO.Net connection i'm using is :
Dim connection As New SqlConnection("server=" & ServerName & ";uid=" & UserName & ";pwd=" & Pwd & ";database=" & DBName & ";Connect Timeout=3000")


If my question is not clear please let me know I'm waiting your help thank you.

[hspc]

First I hope that you have a backup of your data (before converting to nvarchar)
Maybe you should try to use COLLATE clause (http://msdn2.microsoft.com/en-us/library/ms184391.aspx) when selecting data.
I don't know a direct solution to this problem. If you can post a subset of the database, (export subset of a table to another DB and post it), I can make some troubleshooting.

[mamian]

tkx for ur reply

you see the data like this :

Code Name
------- -------------------------------
1 ATV ãÔÊÑíÇÊ
2 Ðãã ÏÇÆäÉ ÌÇä ÚÇÒÇÑ
3 ÈáÏíÉ ÇáÕÝÑÇ

I tryed to select the data as u suggest:
select code, convert(nvarchar(40),name) collate SQL_Latin1_General_CP1256_CI_AS from TableName it returns the same

I don't know what to do

[hspc]

try Arabic_CI_AS not SQL_Latin1_General_CP1256_CI_AS
both on DBs (after and before changing to nvarchar)

[mamian]

I tryed that as u suggest:
select Code, Name=convert(nvarchar(40),name collate Arabic_CI_AS) collate Arabic_CI_AS from TableName
and it gives the following result:

Code Name
------- -------------------------------
1 ATV ?OE??CE
2 ??? IC??E ?C? ?C?C?
3 E?I?E C????C

[hspc]

Have you tried it on old data (if you still have it) and without converting to nvarchar ?

[mamian]

Thanks again for your help
you mean like this :
select Code, name collate Arabic_CI_AS from TableName
it's giving the same result with the "?"
by the way i'm working on the old data and sturcture of the table is as following :
CREATE TABLE [TbaleName] (
[Code] [int] ,
[Name] [varchar] (40) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

[mamian]

I could solve it this way:

I created an empty database with the same structure of the database, I changed the collation sequense to SQL_Latin1_General_CP1256_CI_AS
and then import the data to it from the old database.

This way I can read arabic from the VB6 with RDO and from VB.net with ADO.Net

I'm still looking how can I solve it without changing the database just by running a script directly on the original database

thanks again for your help