Can I Insert/Update Large Text Field To Database Without Bulk Insert?

I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.

You can't just use a plain old update statement and set the column = a parameter of the correct datatype?

|||

How do you indicate that a SqlParameter is of type nvarchar(max)? Any numeric length up to 4000 is easy, but beyond that I've come up empty.

|||

When I add a parameter to a command, I use the AddWithValue method instead of the Add method. That way I don't have to type in the datatype and the length, I just pass it text and it works.

It's possible that it will truncate on you using that method, but I've used it with ntext and long text values before

|||

cmd.Parameters.Add("@.Blobby",SqlDbType.Nvarchar)

or

cmd.Parameters.Add("@.Blobby",SqlDbType.Nvarchar,-1)

|||

cmd.Parameters.AddWithValue("@.Blobby",myTextBox.Text)

(or any other object's value instead of myTextBox)

|||

I normally don't recommend AddWithValue because it can cause some problems when it's unclear what the conversions (if any) should be. This comes into play when the result to be passed could possibly be a nvarchar or a more specific data type (integers, dates). Under certain circumstances, .NET decides to send the data to SQL Server as a nvarchar, and when it gets there, it realizes that it needs to be converted to a more specific data type, but the information needed to do the conversion correctly (because of culture formatting) isn't available on the server, or it uses the servers culture rather than the culture of the running page.

Using .Add with a specified datatype insures that the data conversion is done by .NET before sending the parameter on to SQL Server.