Building Unicode Database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Building Unicode Database

Author

Topic

pcperera
Starting Member

12 Posts

Posted - 2007-06-18 : 00:07:10

I have a problem with Insert and Update Unicode data to database
I can send Unicode data using string with N prefix (Ref 001 below)

Below I write some line of my SP
But It does not write Unicode data to table, it writes like ????????
How to insert data using n prefix with Declared Variable?
How can avoid this problem?
Any other method to pass unicode data using SP?
Please help me...

column UnitName nvarchar

Sample Stored Procedure (SP)
SP header:

CREATE PROCEDURE ltrsp_AddEditUnit
@UnitID char(4),
@UnitName nvarchar(20),
@UtrID int

Insert:

INSERT INTO ltrtb_Unit (UnitID,UnitName,UtrID) VALUES (@UnitID,@UnitName,@UtrID)

Update:
---
UPDATE
ltrtb_Unit
SET
UnitName=@UnitName, UtrID=@UtrID

WHERE
UnitID=@UnitID

Ref 001:
found a method to insert unicode data to table (Using N' prefix), but I want to add using declared variable

INSERT INTO ltrtb_UserLevel (UserLevel,Description) VALUES (1,N'Unicode String')
From the URL,
http://databases.aspfaq.com/general/why-do-some-sql-strings-have-an-n-prefix.html

But I need to know how to pass the data using stored procedure

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-06-18 : 03:24:04

I'm not following the issue

You are passing the Unicode data in ltrsp_AddEditUnit


exec  ltrsp_AddEditUnit 'U12',N'MyUnicode',1

Can you please explain a little more of what you are trying to accomplish?
[/code]

Kristen
Test

22859 Posts

Posted - 2007-06-18 : 04:17:58

"it writes like ????????"

Maybe it is writing it correctly, but the application cannot display it properly??

kristen

pcperera
Starting Member

12 Posts

Posted - 2007-06-21 : 04:32:55

Thanks Vinnie and Kristen,
I can pass a string using N preficx,
My problem is how to write data in a varialbe
Example:
@myUniStr varchar(20)
I want write the data containing in @myUniStr to Tables Column,
with this variable, How can I use N prefix

Ref 001:
found a method to insert unicode data to table (Using N' prefix), but I want to add using declared variable

INSERT INTO ltrtb_UserLevel (UserLevel,Description) VALUES (1,N'Unicode String')
From the URL,
http://databases.aspfaq.com/general/why-do-some-sql-strings-have-an-n-prefix.html

pcperera
Starting Member

12 Posts

Posted - 2007-06-21 : 04:35:39

Application displays correctly, I use Visual Basic .NET

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-06-21 : 05:13:55

I'm still not following you


DECLARE @myTABLE TABLE(NVARCHARCol nvarchar(40))

DECLARE @NValue nvarchar(20)
SET @Nvalue = N'Test 12321'

INSERT INTO @MyTABLE
SELECT @Nvalue

DECLARE @Value varchar(20)
SET @Value = 'Test 3424324'

INSERT INTO @MyTABLE
SELECT @Value

SELECT * FROM @MyTABLE

DECLARE @NewNVal nvarchar(20)
SET @NewNVal = @Value

SELECT @NValue AS OriginalNVarch
SELECT @Value AS OriginalValue
SELECT @NewNVal AS OriginalValueConvertedTONVARCHAR

--You can even use CONVERT but it's really not necessary in most cases if your Storing strings in english

SELECT CONVERT(nvarchar(20),@Value)

Kristen
Test

22859 Posts

Posted - 2007-06-21 : 08:14:33

"My problem is how to write data in a varialbe
Example:
@myUniStr varchar(20)"

@myUniStr Nvarchar(20)

Kristen

pcperera
Starting Member

12 Posts

Posted - 2007-06-22 : 04:52:25

Vinnie881,UR following me
your wrote

DECLARE @NValue nvarchar(20)
SET @Nvalue = N'Test 12321'
you SETting value to @Nvalue in side the stored procedure

it works, it write to column

as example my stored procedure like below

CREATE PROCEDURE ltrsp_AddEditUnit
@UnitID char(4),
@UnitName nvarchar(20),
@UtrID int

Value for @unitName pass from the program, I use .NET command variable

Nomal INSERT statement not generate any error but it writes data unitname column like ?????, means it cant write unicode data, I think we have to N prefix,

But my problem is how to use N prefix with Insert statement

INSERT INTO ltrtb_Unit (UnitID,UnitName,UtrID) VALUES (@UnitID,@UnitName,@UtrID)

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-06-22 : 05:20:54

Let's start from the beginning.

Can you execute the Procedure w/o issue outside the .net application?

If yes post your .net code, and i'll take a look at it.

pcperera
Starting Member

12 Posts

Posted - 2007-06-22 : 05:51:39

my Stored Procedure is

CREATE PROCEDURE ltrsp_AddEditUnit
@UnitID char(4),
@UnitName nvarchar(20),
@UtrID int
AS

BEGIN TRAN

IF EXISTS (SELECT UnitID FROM ltrtb_Unit WHERE UnitID=@UnitID)
--EDIT UNIT
UPDATE
ltrtb_Unit
SET
UnitName=@UnitName,
UtrID=@UtrID

WHERE
UnitID=@UnitID

ELSE
-- NEW UNIT
BEGIN
DECLARE @prefix char(2),@LastNo int
EXEC ltrsp_GetSetLastNo 'UNITID',@prefix OUTPUT, @LastNo OUTPUT
SET @UnitID=@prefix + LTRIM(STR(@LastNo))
INSERT INTO ltrtb_Unit (UnitID,UnitName,UtrID) VALUES (@UnitID,@UnitName,@UtrID)
END

COMMIT TRAN

my Class to handle Database is / I put only code to manage Command Object

Imports System.Data.OleDb
Public Class clsDbs

Public Sub SetCmd(ByVal spName As String)
'1 - SetCmd() 2 - AddParameter() 3 - ExecuteSP() / ExecuteSP_R()
OpenConnection()
myCmd = New OleDbCommand
With myCmd
.Connection = myCon
.CommandText = spName
.CommandType = CommandType.StoredProcedure
End With
End Sub

Public Sub AddParameter(ByVal pName As String, ByVal pType As OleDbType, ByVal pSize As Integer, ByVal pValue As Object, ByVal pDirection As ParameterDirection)
myCmd.Parameters.Add(pName, pType, pSize).Value = pValue
myCmd.Parameters(myCmd.Parameters.Count - 1).Direction = pDirection
End Sub
Public Function ExecuteSP() As Integer
ExecuteSP = myCmd.ExecuteNonQuery()
myCmd.Dispose()
myCmd = Nothing
CloseConnection()
End Function

Public Function ExecuteSP_R() As String
myCmd.ExecuteNonQuery()

ExecuteSP_R = myCmd.Parameters(0).Value

myCmd.Dispose()
myCmd = Nothing
CloseConnection()

End Function

End Class

before use command object I tried to run the stored procedure using query analyzer
I can write unicode data, but they don't goto database
it doesn't work,

Public Sub Save()
Dim myDb As New clsDbs

With myDb
.SetCmd("ltrsp_AddEditUnit ")

.AddParameter("UnitID ", OleDb.OleDbType.Char, 4, UnitID, ParameterDirection.Input)
.AddParameter("UnitName", OleDb.OleDbType.varChar, 20, UnitName, ParameterDirection.Input)
.AddParameter("UtrID", OleDb.OleDbType.integer, 1, UtrID, ParameterDirection.Input)

.ExecuteSP()

End With

myDb = Nothing
End Sub

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-06-22 : 08:08:06

I successfully passed the variable Unitname into a Nvarchar column in my table here is the exact code I used


Imports System.Data.OleDb
Public Class clsDbs
    Dim myCon As OleDbConnection
    Dim myCmd As OleDbCommand
    Public Sub SetCmd(ByVal spName As String)
        '1 - SetCmd() 2 - AddParameter() 3 - ExecuteSP() / ExecuteSP_R()

        myCon = New OleDbConnection
        myCmd = New OleDbCommand
        myCon.ConnectionString = "Provider=sqloledb;" & _
           "Data Source=(Local);" & _
           "Initial Catalog=MyDatabase;" & _
           "Integrated Security=SSPI"

        With myCmd
            .Connection = myCon
            .CommandText = spName
            .CommandType = CommandType.StoredProcedure
        End With
    End Sub

    Public Sub AddParameter(ByVal pName As String, ByVal pType As OleDbType, ByVal pSize As Integer, ByVal pValue As Object, ByVal pDirection As ParameterDirection)
        myCmd.Parameters.Add(pName, pType, pSize).Value = pValue
        myCmd.Parameters(myCmd.Parameters.Count - 1).Direction = pDirection
    End Sub
    Public Function ExecuteSP() As Integer
        myCmd.Connection.Open()
        ExecuteSP = myCmd.ExecuteNonQuery()
        myCmd.Dispose()
        myCmd = Nothing
    End Function

    Public Function ExecuteSP_R() As String
        myCmd.ExecuteNonQuery()

        ExecuteSP_R = myCmd.Parameters(0).Value

        myCmd.Dispose()
        myCmd = Nothing

    End Function

End Class

Public Class savevar
    Public Sub Save()
        Dim myDb As New clsDbs

        With myDb
            .SetCmd("ltrsp_AddEditUnit ")

            .AddParameter("@UnitID", OleDb.OleDbType.Char, 4, "3234", ParameterDirection.Input)
            .AddParameter("@UnitName", OleDb.OleDbType.VarChar, 20, "Testing123", ParameterDirection.Input)
            .AddParameter("@UtrID", OleDb.OleDbType.Integer, 1, 23, ParameterDirection.Input)

            .ExecuteSP()

        End With

        myDb = Nothing
    End Sub
    Shared Sub main()
        Dim x As New savevar
        x.Save()
    End Sub
End Class

Here is the stored procedure I used


/*
create Table TestTable(Col1 nvarchar(20))
*/

create PROCEDURE ltrsp_AddEditUnit
@UnitID char(4),
@UnitName nvarchar(20),
@UtrID int
as


insert into TestTable(Col1)
Select @UnitName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources