ASP and MSSQL 2005 problem with utf-8 text

tbjornen · #1 (**permalink**) 04-14-09, 10:33

Hello

I have problem inserting non-english characters into MS SQL 2005 database. My page with form that collect data is UTF-8. When I try to insert characters like čć they are stored in my MS SQL database like cc, but characters like šž are ok.

What is the problem, and how to solve it ?

gvee · #2 (**permalink**) 04-14-09, 10:56

What data type is the target field in the database?

tbjornen · #3 (**permalink**) 04-14-09, 12:25

nvarchar, bit and int

gvee · #4 (**permalink**) 04-15-09, 12:10

Hmm...

Can you share your insert code?
What happens if you run the stored procedure with these characters directly from Management Studio?

tbjornen · #5 (**permalink**) 04-16-09, 03:27

This is my INSERT statement:

strSQL = "INSERT INTO tbl_users (my_name, my_lastname) "VALUES ('" & strName & "', '" & strLast & "')"

I tried insert č and ć chars through Management Studio query like this:

USE
my_db

INSERT INTO tbl_users (my_name, my_lastname) VALUES ('Čićići', 'Ičićić')

But when I open table there is Cicici and Icicic. So č and ć doesnt work from management studio query either.

gvee · #6 (**permalink**) 04-16-09, 07:00

Try this:

Code:

INSERT INTO tbl_users (my_name, my_lastname) VALUES (N'Čićići',N'Ičićić')

tbjornen · #7 (**permalink**) 04-16-09, 09:06

That works when I use it as query in Management studio, but when I use this query on my ASP page then it converts my characters into different characters. For example š = Å¡ and č = Ä

gvee · #8 (**permalink**) 04-17-09, 07:32

Code:

...
strSQL = "INSERT INTO tbl_users (my_name, my_lastname) "VALUES ('" & strName & "', '" & strLast & "')"

Response.Write (strSQL)
Response.End

What's the print valued of strSQL exactly?

chamoore · #9 (**permalink**) 04-27-09, 13:23

I think your collation is Latin1_General. Try your local collation.

I had the same problem with Turkish Chars (ı ş İ ).
I changed the collation of DB to Turkish_CI_AS. Now its ok.

tbjornen · #10 (**permalink**) 04-27-09, 13:32

I know that I could change collation to Croatian and then Croatian characters should work but I am building an international application where it could be used croatian, french, italian or russian characters so my database must accept all kinds of characters.

I didn't solve my problem yet.

gvee · #11 (**permalink**) 04-27-09, 17:40

Unicode (nvarchar) is collation free so changing the db collation shouldn't matter.

Can you answer my previous question (exact value of strsql) please?

chamoore · #12 (**permalink**) 04-28-09, 01:15

gvee problem is ADODB is not working properly with ASP.

I know an exact solution. But thats hard way.
You can create a Com+ DLL for executing SQL commands.

We have an international portal application which is working with VB6 Com+ DLL.

tbjornen · #13 (**permalink**) 04-28-09, 04:35

Quote:

Originally Posted by gvee

Code:

...
strSQL = "INSERT INTO tbl_users (my_name, my_lastname) "VALUES ('" & strName & "', '" & strLast & "')"

Response.Write (strSQL)
Response.End

What's the print valued of strSQL exactly?

This is my SQL statement:

INSERT INTO tbl_users (my_name, my_lastname) VALUES ('Miroć', 'Piroć')

and when I open table in MS SQL Management there is Miroc Piroc. There is no ć character.

gvee · #14 (**permalink**) 04-28-09, 04:41

If you add the preceding N in the ASP code, does it work?

Code:

strSQL = "INSERT INTO tbl_users (my_name, my_lastname) "VALUES (N'" & strName & "', N'" & strLast & "')"

tbjornen · #15 (**permalink**) 04-28-09, 05:12

I've found solution to my problem. When I use ADO AddNew/Update then it's all working fine.

I've tried with this code:

Code:

Set rsAdd = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT my_name, my_lastname FROM tbl_users"
rsAdd.Open strSQL, conn, 1, 3
	
rsAdd.AddNew
	
rsAdd("my_name") = strName
rsAdd("my_lastname") = strLastName
	
rsAdd.Update

Now i can insert what ever I want to my database field.

Internet Services

Web Development

Digital Marketing

Consumer Tech