Inserting large amounts of text using ntext or text
- From: "DHass [MCP]" <DHassMCP@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 16 Aug 2005 06:44:02 -0700
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.
.
- Follow-Ups:
- Prev by Date: Server.Execute
- Next by Date: Re: Could not determine which columns uniquely identify the rows for [Table Name]
- Previous by thread: Server.Execute
- Next by thread: Re: Inserting large amounts of text using ntext or text
- Index(es):
Relevant Pages
|
