CODEWITHSUNDEEP
.netsql-serverstringado.net
vukashin
vukashin

Reputation: 71

Using very long (10000+ characters) strings as SqlParameter value

I am trying to execute an insert command into the database and one of the columns is of type nvarchar(MAX). Insert command is created by using .NET SqlCommand class and each of the parameters is represented by one SqlParameter object.

My command gets executed always, but when I pass string which length is large (10000+ characters) and which is used as a value for the SqlParameter mapped to the column of type nvarchar(MAX) after the insertion, that particular column is left empty. I repeat, no exception is thrown, INSERT command is executed, but column is empty.

Following examples are how I've tried to create parameter:

// message is the large string variable
// first solution
insertCommand.Parameters.Add("@message", SqlDbType.NVarChar, -1);

// second solution
insertCommand.Parameters.Add("@message", SqlDbType.NVarChar, message.Length);

// third solution
insertCommand.Parameters.Add("@message", SqlDbType.NVarChar, message.Length * 2);

None of these solution gave result. If anyone knows what is the problem please tell me.

I am using MS SQL Server 2008.

Thanks in advance.

Upvotes: 7

Views: 3573

Answers (5)

ZXX
ZXX

Reputation: 4772

Do you still have the problem if you explicitly create SqlParameter, like:

SqlParameter t = new SqlParameter("@" + name, type);
t.Value = value;

and then add it to parameters?

Also if you are invoking a sproc, just to make sure that sproc's param is also declared as nvarchar(max), or if you are not using a sproc try to use it. I've been pushing varbinary(max) blobs without any issues so maybe a binary gets better treatment.

Upvotes: 0

Beth
Beth

Reputation: 9617

According to MSDN

NVarChar:

String. A variable-length stream of Unicode characters ranging between 1 and 4,000 characters. Implicit conversion fails if the string is greater than 4,000 characters. Explicitly set the object when working with strings longer than 4,000 characters.

Here's ntext:

String. A variable-length stream of Unicode data with a maximum length of 2 30 - 1 (or 1,073,741,823) characters.

also check out this

Upvotes: 5

cjk
cjk

Reputation: 46465

Have you tried calling AddWithValue(String name, Object value)?

Upvotes: 3

The King
The King

Reputation: 4650

One Suggesstion.. Have you tried using SqlDbType.XML... There is no necessary to parse the xml in the other end... you can simply store the value in varchar.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135848

Another suggestion to try: use SqlDbType.NText instead of SqlDbType.NVarChar.

Upvotes: 1

Related Questions