asp/net sql update against ntext datatype

Guys Ihave a table that among the other columns has a column of ntext.

I also have another table that has another ntext column...
due to normalizations I need to merge the tables and set the column on the first table plus the value from the second table...(based on some where clause...

How can this be done?
if data type is varchar it is no problem..but due to text datatype I am unable to perform this update...

please help.

FrankUnfortunately you cannot concatenate an ntext field. I think the best you are going to be able to do is something like this:

SELECT
CAST(CAST(myText AS nVarchar(4000)) + CAST(myText AS nVarchar(4000)) AS ntext)
FROM
Test

However this will obviously truncate both columns at 4000 before putting them together.

Terri|||but the length exceeds 8000.

that is actually the problem.|||My only suggestion then is to run a VB.NET (or C#) program that selects in the data from both sources, concatenates the nText data, then updates the nText field in the desired database table. I don't think you are going to be able to do this within just Transact-SQL itself.

Terri|||well that requires application code..it must be a way of doing it too...in sql..
thanks anyway.|||There is no way to concatenate nText fields to each other. If you find a way around it, let us know.

Quoting directly from the Books Online:

+ (String Concatenation)
An operator in a string expression that concatenates two or more character or binary strings, columns, or a combination of strings and column names into one expression (a string operator).

Syntax
expression + expression

Arguments
expression

Is any valid Microsoft® SQL Server? expression of any of the data types in the character and binary data type category,except the image, ntext, or text data types. Both expressions must be of the same data type, or one expression must be able to be implicitly converted to the data type of the other expression.

Terri

ASP.net 2.0 Users (aspnet_Users table) create constraint problem

Hi all,
I am using ASP.NET 2.0 Membership system, which is driving me nuts. I
have my users, which I create correctly. Then I have a table "Models"
which contains models, created by different users. So I obviously need
to create a relationship between the users and the models. I wanted at
first to create a relationship like I'm used to do it, that is to add
an int field to my Model table that would be a reference on the primary
key of my Primary Table. However, as the primary key on the
aspnet_Users table is a uniqueidentifier, I decided to add a field
model_username instead. Then, I tried to create a relationship between
the aspnet_Users.UserName row and Model.model_username, but sql server
won't let me create it, I don't know why.
What is supposed to be the correct way to implement a constraint with
asp.net 2.0 users? I want to disallow the deletion of a user if he has
created a model.
Thank you,
ibiza
On 9 Mar 2006 11:41:46 -0800, ibiza wrote:

> However, as the primary key on the
> aspnet_Users table is a uniqueidentifier, I decided to add a field
> model_username instead.
Why? I assume you mean you added a field model_username to your new table
(not to the asp.net users table), right? If so, did you make sure it's the
same type and size as the aspnet username field (ie nvarchar(256))?
Also, primary keys must be unique, so if you can have mulitple models per
user, you need to have some kind of sequence number or other value in
addition to username to create your primary key.

> Then, I tried to create a relationship between
> the aspnet_Users.UserName row and Model.model_username, but sql server
> won't let me create it, I don't know why.
What is the error you are getting? It seems to work for me.

> What is supposed to be the correct way to implement a constraint with
> asp.net 2.0 users? I want to disallow the deletion of a user if he has
> created a model.
I'd create a uniqueidentifier in your table, and then create a foreign key
constraint on that. This is guaranteed unique, even if another user is
created with the same name.

ASP.net 2.0 Users (aspnet_Users table) create constraint problem

Hi all,
I am using ASP.NET 2.0 Membership system, which is driving me nuts. I
have my users, which I create correctly. Then I have a table "Models"
which contains models, created by different users. So I obviously need
to create a relationship between the users and the models. I wanted at
first to create a relationship like I'm used to do it, that is to add
an int field to my Model table that would be a reference on the primary
key of my Primary Table. However, as the primary key on the
aspnet_Users table is a uniqueidentifier, I decided to add a field
model_username instead. Then, I tried to create a relationship between
the aspnet_Users.UserName row and Model.model_username, but sql server
won't let me create it, I don't know why.
What is supposed to be the correct way to implement a constraint with
asp.net 2.0 users? I want to disallow the deletion of a user if he has
created a model.
Thank you,
ibizaOn 9 Mar 2006 11:41:46 -0800, ibiza wrote:

> However, as the primary key on the
> aspnet_Users table is a uniqueidentifier, I decided to add a field
> model_username instead.
Why? I assume you mean you added a field model_username to your new table
(not to the asp.net users table), right? If so, did you make sure it's the
same type and size as the aspnet username field (ie nvarchar(256))?
Also, primary keys must be unique, so if you can have mulitple models per
user, you need to have some kind of sequence number or other value in
addition to username to create your primary key.

> Then, I tried to create a relationship between
> the aspnet_Users.UserName row and Model.model_username, but sql server
> won't let me create it, I don't know why.
What is the error you are getting? It seems to work for me.

> What is supposed to be the correct way to implement a constraint with
> asp.net 2.0 users? I want to disallow the deletion of a user if he has
> created a model.
I'd create a uniqueidentifier in your table, and then create a foreign key
constraint on that. This is guaranteed unique, even if another user is
created with the same name.