Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Unicode data, stored procedure and SQL Server 2000

Status
Not open for further replies.

wbodger

Programmer
Apr 23, 2007
769
US
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

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

Code:
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?
 
Hi,

Try to use SqlDbType.NVarChar instead of adVarChar


sabin MCP
 
Great, thanks. I will look that up and give it a shot later today
 
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
Code:
N建議替代方案
and I am going crazy here. I have
Code:
<%@ 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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top