Problem inserting large amounts of text

I am running into a problem inserting large amounts of text into my table. Everything works well when I test with a few simple words but when I try to do a test with larger amounts of text (ie 35,000 characters) the appropriate field is left blank. The Insert still performs (all the other fields recieve their data, but the "Description" field is blank. I have tried this with both "text" and "ntext" datatypes. I am using a stored procedure with input parameters. As I mentioned, the query goes off flawlessly with small amounts of data (eg "Hi there!") but not with the larger amount.
I check and the ntext field claims to be able to accept 1073741823 bytes of data. Is there some other thing I should consider with large amounts of text?

Can you show some code?

Nick

|||

Sure thing. The code looks like this:

Dim conConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))

Dim cmdCommand As New SqlCommand("RGW_Add", conConnection)
cmdCommand.CommandType = CommandType.StoredProcedure

cmdCommand.Parameters.Add("@.Name", txtName.Text)
cmdCommand.Parameters.Add("@.Email", txtEmail.Text)
cmdCommand.Parameters.Add("@.Details", Session("Text"))
Dim paramRecordID As New SqlParameter("@.RecordID", SqlDbType.Int, 4)
paramRecordID.Direction = ParameterDirection.Output
cmdCommand.Parameters.Add(paramRecordID)

conConnection.Open()
cmdCommand.ExecuteScalar()
conConnection.Close()

And here is the stored procedure:

ALTER Procedure RGW_Add
(
@.Name varchar(250),
@.Email varchar(250),
@.Details ntext,
@.RecordID int OUTPUT
)
AS

INSERT INTO RGW_Orders
(
Name,
Email,
Details

)
VALUES
(
@.Name,
@.Email,
@.Details
)

SELECT
@.RecordID = @.@.Identity

I know that the Session variable is holding the proper value, because the text is also sent in an email, which makes it through just fine.

|||I think the issue is you need to set the DB Type for the text field. Its being recognized as a varchar since its not defined, and that's probably where you are having the issue.
Nick|||

I think you are on to something. I tried:

Dim paramDetails As New SqlParameter("@.Details", SqlDbType.NText, 16)
paramDetails.Value = Session("Text")
cmdCommand.Parameters.Add(paramDetails)

This resulted in me getting 16 characters saved from the huge string. I tried it with no number, which got me nothing, then I tried it with 1073741823 (the length of an ntext field) - again no love. I began trying smaller and smaller numbers and as of 900, I still can't get any results in the Details field. I went back and tried 16 again and it worked - for 16 characters. Did I misunderstand your advice? Am I syntactically incorrect?

|||That part is correct, but I think you need to see the length in your stored proc as well.
Update@.Details ntext
to
@.Details ntext(5000) or whatever length it is you need.
Nick|||

Whenever I try to do that, either through Enterprise Manager or Visual Studio, I get the following error upon clicking the "Save" button:

ADO error: Column or parameter #10: Cannot specify a column width on data type ntext. Parameter @.Details has an invalid data type.
I will Google the error and see what I find.