Inserting large amounts of text using ntext or text

[Guest]

I am not sure if this is a VB.NET or a SQL Server problem, but those are the
technologies that I am using. I have a stored procedure that accepts
parameters and performs an Insert into my database. Everything works fine,
except for the column that holds a very large (35,000+ characters) amount of
text. I have tried this with both text and ntext datatypes, but I am getting
no results.

The code runs, the stored procedure commits and most of the desired results
are achieved, except that the text column remains blank (not <null>, but
blank). I have tried a great many variations to make this work, but the best
result I have achieved so far is to have the first 16 characters of my string
inserted.

Here is a simplified version of my code (just leaving out a few colums for
clarity):

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



Now, I have modified the portion of the code that declares the "@Details"
parameter as such:

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

as well as a few other minor alterations to this syntax.

It has been suggested elsewhere that I declare my column width in my stored
procedure, but Enterprise Manager doesn't allow that. Does anyone else have a
suggestion or tip?

Any help is appreciated.

 

[Earl]

Have you run your stored procedure in Query Analyzer with sample data?

 

[Guest]

Up until you suggested that, I didn't know it could be done. Thank-you for
the useful tip. After reading up on the process, I tried it out and am
getting an error (unrelated to my procedure, I believe) that says, "Attempt
to initialize OLE library failed. Check for correct versions of OLE DLLs on
this machine." I will try to resolve this error and run the test again.