Problem with sql server text data type

  • Thread starter Thread starter Kevin Quigley
  • Start date Start date
K

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.
 
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
 
Hi Kevin,

Are you sure that text is trimmed?
How do you check?
How long is the trimmed text?
 
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.
 
The length of the text I am trying to enter is 1500 and it seems that
only the first 1000 characters are being saved.
Click to expand...

Yes, but how do you know that only 1000 chars are saved?
 
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.
 
Kevin Quigley said:
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.
Click to expand...

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?
 
Back
Top