Problem with sql server text data type

[Kevin Quigley]

Hi ,

I have a method in an asp.net application that calls a sql server stored
procedure. The stored procedure inserts data to Text field. Here is the
..net procedure...

public int Insert(int articleId,string textData)
{
int rowsAffected;
SqlParameter[] parameters = {
new SqlParameter("@articleId",SqlDbType.Int,4),
new SqlParameter("@text",SqlDbType.Text),
new SqlParameter("@pageId",SqlDbType.Int,4)
};
parameters[0].Value = articleId;
parameters[1].Value = textData;
parameters[2].Direction = ParameterDirection.Output;

try
{
RunProcedure("weeklyreport_inserttext",parameters,out rowsAffected);
}
catch(SqlException exc)
{
return 0;
}

return (int)parameters[2].Value;
}

My problem is that only so much of the text parameter gets stored and
the rest is just cut off.

Has anyone had a similar problem?

Any help would be gratefully appreciated.

Thanks,
Kevin.

 

[Miha Markic]

Hi Kevin,

How is your sp declared?
How is the target table declared?

 

[Kevin Quigley]

Thanks for replying,

Here is my stored procedure....

CREATE PROCEDURE WeeklyReport_InsertText

@articleId int,
@text text,
@pageId int out

AS

BEGIN

INSERT INTO tbl_weeklyreport (libraryid,[text])
VALUES (@articleid,@text)

SET @pageId = @@IDENTITY

END
GO

And the table schema is as follows....

pageid int 4
libraryid int 4
datecreated smalldatetime 4
text text 16

 

[Miha Markic]

Hi Kevin,

Are you sure that text is trimmed?
How do you check?
How long is the trimmed text?

 

[Kevin Quigley]

Hi,

The string wasn't trimmed but I put in code to do the trimming and it
makes no difference.

The length of the text I am trying to enter is 1500 and it seems that
only the first 1000 characters are being saved.

 

[Miha Markic]

The length of the text I am trying to enter is 1500 and it seems that
only the first 1000 characters are being saved.

Yes, but how do you know that only 1000 chars are saved?

 

[Kevin Quigley]

I went in to Query Analyzer and ran the following...

select datalength(text) from tbl_weeklyreport
where pageid = 4609

.. the result returned was 1000.

I also ran a select statement selecting 'the text' field and only the
first 1000 characters were there.

I have already changed the results option in QA to return 2000 records.

 

[Miha Markic]

I went in to Query Analyzer and ran the following...

select datalength(text) from tbl_weeklyreport
where pageid = 4609

. the result returned was 1000.

I also ran a select statement selecting 'the text' field and only the
first 1000 characters were there.

I have already changed the results option in QA to return 2000 records.

That's weird.
I've recreated your situation and it works just fine - storing 2000 chars
with ease.
Can you check it with profiler - what's the actual parameter value passed?