rascal
asked on
Why are my Chinese characters showing up as ??? in my ntext SQL column?
I am having difficulty with Unicode Chinese characters from my ASP web page being written to my SQL database.
The SQL column (called 'Company') used to store the Chinese characters is defined as follows:
Company nvarchar(255) NULL
and it has a COLLATION of: SQL_Latin1_General_CP1_CI_ AS
My ASP web page has the following defined at the top of the page:
<%@LANGUAGE="VBSCRIPT" CodePage = 65001%>
<%
Option Explicit
Session.CodePage = 65001
Response.charset ="utf-8"
Session.LCID = 1033
%>
The SQL Statement used to write out the Chinese string to the Company column is as follows:
UPDATE Customers set Company = N'秋诺儿' WHERE loginid = 19
The update occurs but when written to the database (and upon subsequent display on the page), there are just question marks ('???') in the Company field.
Can someone help me understand where I am going wrong with this attempt to write Chinese characters to a SQL database?
Thanks Experts!
The SQL column (called 'Company') used to store the Chinese characters is defined as follows:
Company nvarchar(255) NULL
and it has a COLLATION of: SQL_Latin1_General_CP1_CI_
My ASP web page has the following defined at the top of the page:
<%@LANGUAGE="VBSCRIPT" CodePage = 65001%>
<%
Option Explicit
Session.CodePage = 65001
Response.charset ="utf-8"
Session.LCID = 1033
%>
The SQL Statement used to write out the Chinese string to the Company column is as follows:
UPDATE Customers set Company = N'秋诺儿' WHERE loginid = 19
The update occurs but when written to the database (and upon subsequent display on the page), there are just question marks ('???') in the Company field.
Can someone help me understand where I am going wrong with this attempt to write Chinese characters to a SQL database?
Thanks Experts!
ASKER
Thanks, but still question marks (????)
I am not getting that issue..Which SQL Server version are you using..
CREATE TABLE Customersx
(
loginid INT
,Company nvarchar(255) NULL
)
GO
INSERT INTO Customersx(loginid) VALUES (19)
UPDATE Customersx set Company = N'秋诺儿' WHERE loginid = 19
select * from Customersx
Output
----------------
loginid Company
19 秋诺儿
ASKER
Hi Pawan,
I should note that the problem only occurs when the ASP web page is performing the SQL update.
I put in a response.write statement in my ASP page at the point where it performs the update and copied and pasted the UPDATE statement directly into my SQL Management Studio for the table and it worked perfectly.
It is only when the web page performs the update that the problem occurs.
I suspect it therefore has something to do with the codepage and charset I have defined at the top of my ASP page (see the original question above for full details).
I should note that the problem only occurs when the ASP web page is performing the SQL update.
I put in a response.write statement in my ASP page at the point where it performs the update and copied and pasted the UPDATE statement directly into my SQL Management Studio for the table and it worked perfectly.
It is only when the web page performs the update that the problem occurs.
I suspect it therefore has something to do with the codepage and charset I have defined at the top of my ASP page (see the original question above for full details).
sure ..pls check the code in more detail ..Check which data type you are using that you are passing to the SQL.
ASKER
I'm sorry, I don't quite understand your question? Which data type I am passing to the SQL?
You must be using a variable in the asp code and from where we are passing that value to the SQL code , what kind of variable is that ?
ASKER
I see. The variable is just a "dim" variable used as follows:
dim sCompany
sCompany = trim(request.form("company "))
sSQL = "UPDATE Customers set Company = N'" & sCompany & "' WHERE loginid = 19"
dim sCompany
sCompany = trim(request.form("company
sSQL = "UPDATE Customers set Company = N'" & sCompany & "' WHERE loginid = 19"
I think we must be having issue at
could you pls print this sCompany and check?
dim sCompany
sCompany = trim(request.form("company"))
could you pls print this sCompany and check?
ASKER
I added a response.write immediately after the request.form("company") - here it is:
sCompany=报价与订单
sCompany=报价与订单
Pls try this-
I think & ( concat ) may be converting that the varchar..
I think & ( concat ) may be converting that the varchar..
sSQL = "UPDATE Customers set Company = N'" & CAST(sCompany AS NVARCHAR(255)) & "' WHERE loginid = 19"
@Pawan, the code above is expected to work in VB.NET, which it won't, I believe.
Furthermore, the String data-type in .NET is Unicode by default, so I don't think it will have any issues there.
@rascal, could you please also response.write the sSQL variable as well?
Furthermore, the String data-type in .NET is Unicode by default, so I don't think it will have any issues there.
@rascal, could you please also response.write the sSQL variable as well?
Nitin Sir , he is not using .Net but asp. :)
@Pawan, Thanks for pointing that out.
However, still notice that the "CAST(sCompany AS NVARCHAR(255))" will try to execute within the context of the language he is using (be it VBScript) and will not execute because it is T-SQL. Isn't it?
However, still notice that the "CAST(sCompany AS NVARCHAR(255))" will try to execute within the context of the language he is using (be it VBScript) and will not execute because it is T-SQL. Isn't it?
No , Thats a T-SQL code, which will be executed at SSMS only... :)
@Pawan, just to avoid confusion, as I am sure you already know, I am referring to following piece of code:
I sincerely believe that this is not a T-SQL code. If so, where is @ before sSQL? Is @ really optional, i really don't know.
Furthermore, OP is referring to the variable, sCompany which is declared in the asp code.
Anyway, let us wait OP to come back.
sSQL = "UPDATE Customers set Company = N'" & CAST(sCompany AS NVARCHAR(255)) & "' WHERE loginid = 19"
I sincerely believe that this is not a T-SQL code. If so, where is @ before sSQL? Is @ really optional, i really don't know.
Furthermore, OP is referring to the variable, sCompany which is declared in the asp code.
Anyway, let us wait OP to come back.
We need below at SQL ---
UPDATE Customersx set Company = CAST(N'秋诺儿' AS NVARCHAR(255)) WHERE loginid = 19
@Author - Could you modify the screen like above..
UPDATE Customersx set Company = CAST(N'秋诺儿' AS NVARCHAR(255)) WHERE loginid = 19
@Author - Could you modify the screen like above..
Please look at the UPDATE command in SQL Profiler. It should show ???? most probably. If yes then you have to look at your connection properties. Does the SQL connection allow Chinese? Or does it support utf-8 => utf-16 conversion? SQL Server saves nchar and ntext data in UTF-16. E.g. ODBC connection has "Perform translation of character data" as one of the parameters.
ASKER
I updated the UPDATE statement to include the CAST function but unfortunately it still did not work (still question marks in the resulting column). Below is the query that got sent to SQL:
UPDATE Customers set Company = CAST(N'报价与订单' AS NVARCHAR(255)) WHERE loginid = 19
PCelba: Regarding the connection allowing Chinese - is there any special parameter I need to add to my connection string to allow this? Below is the connection string I am using:
Provider=SQLOLEDB;Server=x xx.xxx.xxx .xxx;datab ase=custom er;network =DBMSSOCN; uid=xxxxx; pwd=xxxxxx
UPDATE Customers set Company = CAST(N'报价与订单' AS NVARCHAR(255)) WHERE loginid = 19
PCelba: Regarding the connection allowing Chinese - is there any special parameter I need to add to my connection string to allow this? Below is the connection string I am using:
Provider=SQLOLEDB;Server=x
Which SQL version r u using?
utf8 should be set before queries, this will help:
https://stackoverflow.com/questions/14456313/cant-insert-chinese-character-into-mysql
https://stackoverflow.com/questions/14456313/cant-insert-chinese-character-into-mysql
ASKER
Ok, good news - I have found the culprit. I don't understand why this is the culprit, but here it is:
When I execute a SQL query from my ASP code, I build the usual execution code - that is, I create a connection object, passing the connection string, then perform the Connection object's EXECUTE statement. But I execute a stored procedure and pass that stored procedure the SQL string (instead of just executing the SQL directly from the code). Somehow, the stored procedure was the culprit, unable to handle the Chinese chars. When I avoid calling the stored procedure and instead just execute the SQL statement directly in my code, it WORKS!
For your reference, here is the Stored Procedure I was calling:
For now I am just going to no longer call the stored procedure, but this means I have to convert all the places in my site (lots) that call that stored procedure. It would be great to understand what it was about the stored procedure that caused this to fail. But I thank everyone for their help!!
When I execute a SQL query from my ASP code, I build the usual execution code - that is, I create a connection object, passing the connection string, then perform the Connection object's EXECUTE statement. But I execute a stored procedure and pass that stored procedure the SQL string (instead of just executing the SQL directly from the code). Somehow, the stored procedure was the culprit, unable to handle the Chinese chars. When I avoid calling the stored procedure and instead just execute the SQL statement directly in my code, it WORKS!
For your reference, here is the Stored Procedure I was calling:
CREATE PROCEDURE [dbo].[sp_genericquery_return_recordcount]
@sQuery ntext
AS
SET NOCOUNT ON
DECLARE @nUserRowsAffected int
exec (@sQuery)
Set @nUserRowsAffected = @@ROWCOUNT
SELECT @nUserRowsAffected AS '@@ROWSAFFECTED'
IF @@ERROR = 0
BEGIN
RETURN 0
END
ELSE
BEGIN
RAISERROR('Error executing statement', 16, 1)
RETURN -1
END
And here is how I was calling it: '--------------------------
' GenericQueryReturningRowsAffected
'--------------------------
function GenericQueryReturningRowsAffected(ByVal sSQL, ByVal sDatabase)
if IsOnErrorResumeNextActive() then on error resume next
GenericQueryReturningRowsAffected=0
dim SQLConn,sDSN,rsSQL,rsSQL2,bIsSelectStatement
' Create the ADO objects
Set SQLConn = Server.CreateObject("ADODB.Connection")
sSQL=trim(sSQL)
bIsSelectStatement=false
err.clear
' fetch the connection string to the SQL database
sDatabase = lcase(sDatabase)
if sDatabase=POWERMAX_DATABASE then sDSN=GetPowerMaxDSNString()
if sDatabase=INVENTORY_DATABASE or sDatabase="" then sDSN=GetSQLDSNString()
' Open the SQL database
SQLConn.Open sDSN
if Err.Number <> 0 then
SQLConn.close
set SQLConn=nothing
exit function
end if
' Before FixQuoting the string, see if this is a select statement. When SELECT statements
' are executed by our stored procedure, the @@ROWSAFFECTED will be in the 2nd resultset
' returned.
if lcase(left(sSQL,6))="select" then bIsSelectStatement=true
' Since we are calling a stored procedure that takes the entire
' query as a parm, we need to double-up any quotes once again since we are
' passing the string as a string parm.
sSQL = FixQuotes(sSQL)
sSQL = "sp_genericquery_ret_recordcount '" & sSQL & "'"
'response.write("sSQL=" & sSQL)
mytrace("GenericQueryReturningRowsAffected: About to execute query: " & sSQL)
Set rsSQL = Server.CreateObject("ADODB.Recordset")
'response.write(sSQL)
' execute the query
rsSQL.Open sSQL,SQLConn
'any found?
if err.number=0 then
if bIsSelectStatement then
' Since this was a select statement, 2 recordsets were returned by the stored procedure.
' so fetch the NEXT recordset to get the rows affected
set rsSQL2 = rsSQL.NextRecordSet
GenericQueryReturningRowsAffected = rsSQL2("@@ROWSAFFECTED")
rsSQL2.close
set rsSQL2=nothing
else
GenericQueryReturningRowsAffected = rsSQL("@@ROWSAFFECTED")
end if
end if
' close DB
on error resume next
if NOT bIsSelectStatement then rsSQL.close
set rsSQL=nothing
SQLConn.close
set SQLConn=nothing
end function
For now I am just going to no longer call the stored procedure, but this means I have to convert all the places in my site (lots) that call that stored procedure. It would be great to understand what it was about the stored procedure that caused this to fail. But I thank everyone for their help!!
just as a side note, you probably should consider using a parametrized query instead of concatenating your sql sentence:
"sSQL = "UPDATE Customers set Company = N'" & sCompany & "' WHERE loginid = 19""
since this is an asp page and I am thinking probably one that is open to the internet it is prone to sql injection, not that has to do with your chinese problem but it mayhelp you avoid problems
"sSQL = "UPDATE Customers set Company = N'" & sCompany & "' WHERE loginid = 19""
since this is an asp page and I am thinking probably one that is open to the internet it is prone to sql injection, not that has to do with your chinese problem but it mayhelp you avoid problems
@Author, thanks for an update. In your original post, you obviously didn't mention that the sSQL is actually passed as a parameter to the stored procedure. Passing fully constructed sql statement to a stored procedure, I must admit, is not a standard practice by any stretch of imagination.
Hence, I keep insisting OPs to give full information possible.
Thanks, anyways!
Hence, I keep insisting OPs to give full information possible.
Thanks, anyways!
Why dont you create a proc and pass parameters from UI rather than a complete string..
ASKER
Totally agree - this was inherited code, and I plan on parameterizing the SQL calls as soon as the dust settles :) My apologies for not disclosing the stored procedure call earlier, I just detected it myself. Thanks for everyone for their insightful help!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you pcelba - that was it!! I never would have though to look at the parameter passed to the stored procedure in that way!
Response.CodePage = 65001