ADO.NET and SQL2000 and Arabic data
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 couldnt 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.
[734 byte] By [
mamian] at [2007-11-20 11:27:09]
# 1 Re: ADO.NET and SQL2000 and Arabic data
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.
mamian at 2007-11-9 13:45:38 >
# 2 Re: ADO.NET and SQL2000 and Arabic data
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.
hspc at 2007-11-9 13:46:38 >
# 3 Re: ADO.NET and SQL2000 and Arabic data
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
mamian at 2007-11-9 13:47:37 >
# 4 Re: ADO.NET and SQL2000 and Arabic data
try Arabic_CI_AS not SQL_Latin1_General_CP1256_CI_AS
both on DBs (after and before changing to nvarchar)
hspc at 2007-11-9 13:48:44 >
# 5 Re: ADO.NET and SQL2000 and Arabic data
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
mamian at 2007-11-9 13:49:43 >
# 6 Re: ADO.NET and SQL2000 and Arabic data
Have you tried it on old data (if you still have it) and without converting to nvarchar ?
hspc at 2007-11-9 13:50:43 >
# 7 Re: ADO.NET and SQL2000 and Arabic data
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 at 2007-11-9 13:51:47 >
# 8 Re: ADO.NET and SQL2000 and Arabic data
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
mamian at 2007-11-9 13:52:40 >
