Input of SQLServer TEXT data type via formview textbox

[JanVolleyM]

This problem is driving me crazy and I can't find anything about this anywhere. I am using SQLServer 2000 and VS2005. My database table has a field of TEXT data type. I want to allow the user to input a value for this field via a formview, so I've created an asp:textbox control on the formview:

<asp:TextBox ID="TestEvaluationMethodsTextBox" runat="server" TextMode="multiLine" Width="890px" Rows="12" Text=',%# Bind("TestEvaluationMethods") %>'</asp:TextBox>

When I click the Save button, the value of TestEvaluationMethodsTextBox is cut off at 3738 characters for some reason when I'm inputting 6000 characters. A weird thing is that this field was originally a VARCHAR(4000) in the db and I modified to the TEXT data type and 3738 is kind of close to 4000, but don't know if that has anything to do with it.

When I run the application in debug mode, the value for this field in my form object is not even getting anything more than 3738 characters, so I can't even get past that part. So the problem is not with any code that updates the db because the value is truncated before it even gets that far. It seems to be truncated immediately.

Can anyone figure out what I'm doing wrong?

Jan

 

[jbenson001]

How are you sending the data to the DB? Are you calling a stored procedure with parameters? Are you using one of the aweful datasource controls?

 

[JanVolleyM]

I'm using one of the awful datasource controls as follows:

<asp:ObjectDataSource ID="LADataSource" runat="server" DataObjectTypeName="LA" InsertMethod="InsertLA" SelectMethod="GetLA" UpdateMethod="UpdateLA"
TypeName="ISDFormsDBManager" OnInserted="LADataSource_Inserted" OnInserting="LADataSource_Inserting" OnSelected="LADataSource_Selected"
OnUpdated="LADataSource_Updated" OnUpdating="LADataSource_Updating" ConflictDetection="compareAllValues" OldValuesParameterFormatString="original{0}">
<SelectParameters>
<asp:SessionParameter Name="laID" SessionField="laID" Type="String" />
</SelectParameters>
<InsertParameters>
<asparameter Direction="ReturnValue" Name="laID" Type="Int32" />
</InsertParameters>
</asp:ObjectDataSource>

I was wrong though when I said that the value is getting truncated before reaching the InsertLA method to update the db. It just looked that way because when I ran it in debug mode and put the cursor on the field value, it would popup only part of the value. But, when I actually double-clicked on the value, I could see that the whole thing was still there.

I also just figured out that my stored procedure that does the insert had the input paramter as VARCHAR(4000), so that's why it was getting cut off. So, I need to update my stored procedure, however, I have read that inserting and updating TEXT fields have to be done differently. I'm going to try just updating my stored procedure below to change the TestEvaluationMethod field from VARCHAR to TEXT and see if it works, but I'm suspecting it won't:

ALTER PROCEDURE Insert_LA
@LAID int OUTPUT,
@CourseProfileID int,
@LessonTitle varchar(250),
@LessonDesc varchar(2000),
@LearningObjective varchar(4000),
@TestEvaluationMethods varchar(4000),
@LessonPlanCasestudyInd char(2),
@SafetyFactors varchar(2000)
AS
INSERT INTO Learning_Analysis
(CourseProfileID,
displayOrder,
LessonTitle,
LessonDesc,
LearningObjective,
TestEvaluationMethods,
LessonPlanCasestudyInd,
SafetyFactors)
SELECT @CourseProfileID,
ISNULL(MAX(displayOrder) + 1, 1),
@LessonTitle,
@LessonDesc,
@LearningObjective,
@TestEvaluationMethods,
@LessonPlanCasestudyInd,
@SafetyFactors
FROM Learning_Analysis
WHERE CourseProfileID = @CourseProfileID
SET @LAID = @@IDENTITY

 

[jbenson001]

Inserting is the save, but updating is a different story.

 

[JanVolleyM]

So you think inserting will work, but updating won't? Is that what you mean?

When that field gets updated, I want to replace the entire thing, not just part of it. Does that matter?

 

[jbenson001]

Inserting into a Text column is the same as any other Insert, however, updating the text is MUCH different. You will have to look at examples in BOL. It might be eaiser to delete the current row and insert a new one. But I don't know what your architechture is etc. That would be up to you.

 

[JanVolleyM]

I will try the insert first, so I guess that should work.

I have seen examples of updates TEXT fields, so I will give that a try as well. I really don't want to delete the row and insert a new one. That just seems kind of ridiculous to have to do that, plus that would be a pain because I have auto-generated primary keys with child records.

I don't know why it should be so difficult to replace a TEXT value in a db record. I have read about having to get a pointer to the data and also doing the update in chunks. Not sure if I'll have to do both or not.

 

[jbenson001]

A Text column is not the the same as char or varchar. Escentially it is a blob column, so pointers are necessary.

Also remember, that the TEXT datatype will be depreciated in newer releases of sqlserver, but since you are using 2000, you don't have a choice.

 

[JanVolleyM]

I know the TEXT datatype wlil be deprecated. I actually did the development of my app using a SQLServer 2005 Express db. I didn't have much choice actually, but I guess I could've pushed harder for another db server if I had known how much of a difference there was between 2005 and 2000. So now I'm stuck changing a bunch of VARCHAR columns to TEXT in several tables because of the 8000 record length limit. Since this is govt, it will take forever for them to upgrade to 2005, so I don't have a choice in that either.

 

[jbenson001]

I hear ya. I was a government contractor for a few years. Yuck! I hope I never have to do that again.

 

[JanVolleyM]

Ok, my inserts work with no problem and shockingly my updates work as well without having to change any code. And I don't understand why. Can anyone explain this? I thought I had to use pointers to get and write updates to text fields. Maybe I didn't test with long enough data? I input a string of 6000-some characters. I should try much longer. Here is my code:

public static void UpdateLA(LA la, LA originalLa)
{
.
.
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("Update_LA", con);
cmd.CommandType = CommandType.StoredProcedure;
.
.
cmd.Parameters.Add(new SqlParameter("@LearningObjective", SqlDbType.Text));
cmd.Parameters["@LearningObjective"].Value = la.LearningObjective;
cmd.Parameters.Add(new SqlParameter("@TestEvaluationMethods", SqlDbType.Text));
cmd.Parameters["@TestEvaluationMethods"].Value = la.TestEvaluationMethods;
.
.
sqlCommand = cmd.CommandText;
sqlParams = cmd.Parameters;
cmd.ExecuteNonQuery();
.
.
.
My Update_LA stored procedure looks like this:

CREATE PROCEDURE dbo.Update_LA
@LAID int,
@CourseProfileID int OUTPUT,
@LessonTitle varchar(250),
@LessonDesc varchar(2000),
@LearningObjective text,
@TestEvaluationMethods text,
@LessonPlanCasestudyInd char(2),
@SafetyFactors varchar(2000)
AS

UPDATE Learning_Analysis
SET CourseProfileID = @CourseProfileID,
LessonTitle = @LessonTitle,
LessonDesc = @LessonDesc,
LearningObjective = @LearningObjective,
TestEvaluationMethods = @TestEvaluationMethods,
LessonPlanCasestudyInd = @LessonPlanCasestudyInd,
SafetyFactors = @SafetyFactors
WHERE LAID = @LAID

 

[JanVolleyM]

The updates also worked with over 72K characters in the text fields. I'm baffled.

 

[jbenson001]

It seems with a TEXT datatype, you only need the pointers if you you are updating a portion of the text. Since you are just replacing the whole column, you don't need them.

 

[JanVolleyM]

That's the best news I've heard all week!! Yee-hah!

Thanks for your help. Now I can be confident about my app working properly knowing that.

 

[jbenson001]

That is my interpretation from what I read in BOL. Please see examples in BOL and you can also post to the SQL Server Programming forum and ask there to double check.