Unicode data, stored procedure and SQL Server 2000

[wbodger]

I have an application that is sending potentially Unicode data to a script that then posts it into my SQL Server 2000 db. I have confirmed that the data is still unicode when it hits my page and after I place it into a variable. However, posting it to my SQL Server seems to be a problem.Using this code

	dim cmd
	set cmd = server.CreateObject("ADODB.command")
	set cmd.ActiveConnection = dbConn
    cmd.commandtype = adCmdText

SET cmd = server.CreateObject("ADODB.Command")

with cmd
    .ActiveConnection = dbConn
    .CommandText = "onyx.dbo.Insert3PComments"
    .CommandType = 4
    
        .Parameters.Append .createparameter("@sn", adVarChar, adParamInput, 50)
        .Parameters.Append .createparameter("@comments", adVarChar, adParamInput, 4000)
        .Parameters.Append .createparameter("@name", adVarChar, adParamInput, 50)
        .Parameters.Append .createparameter("@email", adVarChar, adParamInput, 50)
        .Parameters.Append .createparameter("@rating", adVarChar, adParamInput, 50)
        .Parameters.Append .createparameter("@authorized", adVarChar, adParamInput, 50)
        .Parameters.Append .createparameter("@version", adVarChar, adParamInput, 20)
        .Parameters.Append .createparameter("@productid", adVarChar, adParamInput, 20)
        .Parameters.Append .createparameter("@databuild", adInteger, adParamInput, 0)
        .Parameters.Append .createparameter("@country", adVarChar, adParamInput, 50)
        .Parameters.Append .createparameter("@company", adVarChar, adParamInput, 50)
        .Parameters.Append .createparameter("@productname", adVarChar, adParamInput, 75)
        .Parameters.Append .createparameter("@productnum", adVarChar, adParamInput, 50)
        .Parameters.Append .createparameter("@lang", adVarChar, adParamInput, 10)
        .Parameters.Append .createparameter("@oslang", adVarChar, adParamInput, 10)
        
    .parameters("@sn") = sn
    .parameters("@comments") = comments
    .parameters("@name") = name
    .parameters("@email") = email
    .parameters("@rating") = rating
    .parameters("@authorized") = authorized
    .parameters("@version") = version
    .parameters("@productid") = productid
    .parameters("@databuild") = databuild
    .parameters("@country") = country
    .parameters("@company") = company
    .parameters("@productname") = productname
    .parameters("@productnum") = productnum
    .parameters("@lang") = lang
    .parameters("@oslang") = oslang
    .execute
        
end with
set cmd = nothing

I post to this

CREATE  PROCEDURE [dbo].[Insert3PComments] (

@sn nVarChar(50),
@comments nVarChar(4000),
@name nVarChar(50),
@email nVarChar(50),
@rating nVarChar(50),
@authorized nVarChar(50),
@version nVarChar(20),
@productid nVarChar(20),
@databuild Int,
@country nVarChar(50),
@company nVarChar(50),
@productname nVarChar(75),
@productnum nVarChar(50),
@lang nVarChar(10),
@oslang nVarChar(10)
) AS

BEGIN
	INSERT INTO table(sn, comments, name, email, rating, authorized, version, productid, databuild, country, company, productname, productnum, lang, oslang)
	VALUES(@sn, @comments, @name, @email, @rating, @authorized, @version, @productid, @databuild, @country, @company, @productname, @productnum, @lang, @oslang)
END
GO

I post this: '建議替代方案' into comments
And it inserts this: 建議替代方案
I see that I am supposed to Have an N before any constant, but how do I use that same logic in the stored procedure where I am using a passed value, not a constant?

 

[sabinUE]

Hi,

Try to use SqlDbType.NVarChar instead of adVarChar


sabin MCP

 

[fredericofonseca]

sqldbtype.nvarchar is not for adodb.


try using adVarWChar instead of adVarChar as datatype for the unicode variables

http://www.w3schools.com/ado/met_comm_createparameter.asp

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[sabinUE]

sqldbtype.nvarchar is not for adodb.

I are right; sry about that


sabin MCP

 

[wbodger]

Great, thanks. I will look that up and give it a shot later today

 

[wbodger]

I have tried multiple variations of this and numerous other suggestions. Most come back to saying you have to preface with 'N'. I have tried using adVarWChar (then nvarchar as the actual db datatype), I have tried varbinary, I have tried adding the 'N' in various places (when creating the variable, when assigning it to the parameter etc.) and nothing has worked. As a for instance, I can place
建議替代方案
directly into the db and it works fine. However, when I try to write that to the db using the web page and stored procedure, I get

N建議替代方案

and I am going crazy here. I have

<%@ Language=VBScript CodePage="65001"%>
<% option explicit
Response.CharSet = "utf-8" %>
<html>
<head>
<meta http-equiv="content-type" content="text/html;charset=utf-8" />

Probably overkill, but...

Any other suggestions how to get this to work? I need to write unicode data from a classic asp web page to a sql server 2000 db. The db can handle unicode data, I just can't seem to get it to write properly.

Thanks,
wb