error "80040e21", String data, right truncation when append large

I am writing data to an SQL field (data type = text).


I am getting this error when the form is submitted with a large amount of
text. Smaller amount of text worked fine.
When I traced the size of the text being passed into the sp, this is the
value="15189"

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

[Microsoft][ODBC SQL Server Driver]String data, right truncation.


However, when I test the stored proc in the Query Analyzer, it worked fine.


I hope someone can help.
Here is the code of the param.


cmd.Parameters.Append
cmd.CreateParameter("@Content",adBSTR,adParamInput,,frmConte nt)

Re: error "80040e21", String data, right truncation when append large

qtlee wrote:
> I am writing data to an SQL field (data type = text).
>
>
> I am getting this error when the form is submitted with a large
> amount of text. Smaller amount of text worked fine.
> When I traced the size of the text being passed into the sp, this is
> the value="15189"
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

Probably nothing to do with your problem, but you should stop using ODBC.
Use the SQLOLEDB provider instead (see www.connectionstrings.com)

>
> [Microsoft][ODBC SQL Server Driver]String data, right truncation.
>
>
> However, when I test the stored proc in the Query Analyzer, it worked
> fine.

How did you manage to test it with that amount of data in Query Analyzer?
;-)

>
>
> I hope someone can help.
> Here is the code of the param.
>
>
> cmd.Parameters.Append
> cmd.CreateParameter("@Content",adBSTR,adParamInput,,frmConte nt)

adBSTR is not the correct dataype constant. Could you show us the parameter
declarations in your CREATE PROCEDURE statement? The correct constant is
probably adLongVarChar (see
http://www.carlprothman.net/Technology/DataTypeMapping/tabid /97/Default.aspx),
but we would need to see the CREATE PROCEDURE statement to be sure.

You may need to use AppendChunk to set the parameter value
(http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthapp chunk.asp)

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: error "80040e21", String data, right truncation when append la

I did not think the test in the Query Analyzer would work, but it did. I
just copy and paste a large amount of text as the value for the @Content
parameter.

This is the stored procedure:

CREATE PROCEDURE dbo.Add_News
@ReleaseDate smalldatetime,
@NewsHeader nvarchar(200),
@Content text,
@Harbor text,
@ID int OUTPUT
AS

INSERT INTO tblNews (ReleaseDate,NewsHeader,Content,Harbor)
VALUES (@ReleaseDate,
@NewsHeader,
@Content,
@Harbor)

This was the statement to run a test within the Query Analyzer:

declare @ReturnedID int
exec add_news
@ReleaseDate = '1/1/1900',
@NewsHeader = 'test',
@Harbor = 'test',
@Content = '',
@ID = @ReturnedID output

I just added about 15,000 characters for the value of @Content.



"Bob Barrows [MVP]" wrote:

> qtlee wrote:
> > I am writing data to an SQL field (data type = text).
> >
> >
> > I am getting this error when the form is submitted with a large
> > amount of text. Smaller amount of text worked fine.
> > When I traced the size of the text being passed into the sp, this is
> > the value="15189"
> >
> > Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
>
> Probably nothing to do with your problem, but you should stop using ODBC.
> Use the SQLOLEDB provider instead (see www.connectionstrings.com)
>
> >
> > [Microsoft][ODBC SQL Server Driver]String data, right truncation.
> >
> >
> > However, when I test the stored proc in the Query Analyzer, it worked
> > fine.
>
> How did you manage to test it with that amount of data in Query Analyzer?
> ;-)
>
> >
> >
> > I hope someone can help.
> > Here is the code of the param.
> >
> >
> > cmd.Parameters.Append
> > cmd.CreateParameter("@Content",adBSTR,adParamInput,,frmConte nt)
>
> adBSTR is not the correct dataype constant. Could you show us the parameter
> declarations in your CREATE PROCEDURE statement? The correct constant is
> probably adLongVarChar (see
> http://www.carlprothman.net/Technology/DataTypeMapping/tabid /97/Default.aspx),
> but we would need to see the CREATE PROCEDURE statement to be sure.
>
> You may need to use AppendChunk to set the parameter value
> (http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthapp chunk.asp)
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>

Re: error "80040e21", String data, right truncation when append la

Here is the stored proc:

CREATE PROCEDURE dbo.Add_News
@ReleaseDate smalldatetime,
@NewsHeader nvarchar(200),
@Content text,
@Harbor text,
@ID int OUTPUT
AS
BEGIN TRANSACTION

INSERT INTO tblNews (ReleaseDate,NewsHeader,Content,Harbor)
VALUES (@ReleaseDate,
@NewsHeader,
@Content,
@Harbor)



This test was run within the Query Analyzer:

declare @ReturnedID int
exec add_news
@ReleaseDate = '1/1/1900',
@NewsHeader = 'test',
@Harbor = 'test',
@Content = '',
@ID = @ReturnedID output


I added about 15,000 characters for the value of @Content



"Bob Barrows [MVP]" wrote:

> qtlee wrote:
> > I am writing data to an SQL field (data type = text).
> >
> >
> > I am getting this error when the form is submitted with a large
> > amount of text. Smaller amount of text worked fine.
> > When I traced the size of the text being passed into the sp, this is
> > the value="15189"
> >
> > Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
>
> Probably nothing to do with your problem, but you should stop using ODBC.
> Use the SQLOLEDB provider instead (see www.connectionstrings.com)
>
> >
> > [Microsoft][ODBC SQL Server Driver]String data, right truncation.
> >
> >
> > However, when I test the stored proc in the Query Analyzer, it worked
> > fine.
>
> How did you manage to test it with that amount of data in Query Analyzer?
> ;-)
>
> >
> >
> > I hope someone can help.
> > Here is the code of the param.
> >
> >
> > cmd.Parameters.Append
> > cmd.CreateParameter("@Content",adBSTR,adParamInput,,frmConte nt)
>
> adBSTR is not the correct dataype constant. Could you show us the parameter
> declarations in your CREATE PROCEDURE statement? The correct constant is
> probably adLongVarChar (see
> http://www.carlprothman.net/Technology/DataTypeMapping/tabid /97/Default.aspx),
> but we would need to see the CREATE PROCEDURE statement to be sure.
>
> You may need to use AppendChunk to set the parameter value
> (http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthapp chunk.asp)
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>

Re: error "80040e21", String data, right truncation when append la

Here is the stored proc:

CREATE PROCEDURE dbo.Add_News
@ReleaseDate smalldatetime,
@NewsHeader nvarchar(200),
@Content text,
@Harbor text,
@ID int OUTPUT
AS
BEGIN TRANSACTION

INSERT INTO tblNews (ReleaseDate,NewsHeader,Content,Harbor)
VALUES (@ReleaseDate,
@NewsHeader,
@Content,
@Harbor)



This test was run within the Query Analyzer:

declare @ReturnedID int
exec add_news
@ReleaseDate = '1/1/1900',
@NewsHeader = 'test',
@Harbor = 'test',
@Content = '',
@ID = @ReturnedID output


I added about 15,000 characters for the value of @Content

"Bob Barrows [MVP]" wrote:

> qtlee wrote:
> > I am writing data to an SQL field (data type = text).
> >
> >
> > I am getting this error when the form is submitted with a large
> > amount of text. Smaller amount of text worked fine.
> > When I traced the size of the text being passed into the sp, this is
> > the value="15189"
> >
> > Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
>
> Probably nothing to do with your problem, but you should stop using ODBC.
> Use the SQLOLEDB provider instead (see www.connectionstrings.com)
>
> >
> > [Microsoft][ODBC SQL Server Driver]String data, right truncation.
> >
> >
> > However, when I test the stored proc in the Query Analyzer, it worked
> > fine.
>
> How did you manage to test it with that amount of data in Query Analyzer?
> ;-)
>
> >
> >
> > I hope someone can help.
> > Here is the code of the param.
> >
> >
> > cmd.Parameters.Append
> > cmd.CreateParameter("@Content",adBSTR,adParamInput,,frmConte nt)
>
> adBSTR is not the correct dataype constant. Could you show us the parameter
> declarations in your CREATE PROCEDURE statement? The correct constant is
> probably adLongVarChar (see
> http://www.carlprothman.net/Technology/DataTypeMapping/tabid /97/Default.aspx),
> but we would need to see the CREATE PROCEDURE statement to be sure.
>
> You may need to use AppendChunk to set the parameter value
> (http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthapp chunk.asp)
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>

Re: error "80040e21", String data, right truncation when append la

So what happens when you use the constant I recommended in your
createparameter call?

qtlee wrote:
> Here is the stored proc:
>
> CREATE PROCEDURE dbo.Add_News
> @ReleaseDate smalldatetime,
> @NewsHeader nvarchar(200),
> @Content text,
> @Harbor text,
> @ID int OUTPUT
> AS
> BEGIN TRANSACTION
>
> INSERT INTO tblNews (ReleaseDate,NewsHeader,Content,Harbor)
> VALUES (@ReleaseDate,
> @NewsHeader,
> @Content,
> @Harbor)
>
>
>
> This test was run within the Query Analyzer:
>
> declare @ReturnedID int
> exec add_news
> @ReleaseDate = '1/1/1900',
> @NewsHeader = 'test',
> @Harbor = 'test',
> @Content = '',
> @ID = @ReturnedID output
>
>
> I added about 15,000 characters for the value of @Content
>
> "Bob Barrows [MVP]" wrote:
>
>> qtlee wrote:
>>> I am writing data to an SQL field (data type = text).
>>>
>>>
>>> I am getting this error when the form is submitted with a large
>>> amount of text. Smaller amount of text worked fine.
>>> When I traced the size of the text being passed into the sp, this is
>>> the value="15189"
>>>
>>> Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
>>
>> Probably nothing to do with your problem, but you should stop using
>> ODBC.
>> Use the SQLOLEDB provider instead (see www.connectionstrings.com)
>>
>>>
>>> [Microsoft][ODBC SQL Server Driver]String data, right truncation.
>>>
>>>
>>> However, when I test the stored proc in the Query Analyzer, it
>>> worked
>>> fine.
>>
>> How did you manage to test it with that amount of data in Query
>> Analyzer? ;-)
>>
>>>
>>>
>>> I hope someone can help.
>>> Here is the code of the param.
>>>
>>>
>>> cmd.Parameters.Append
>>> cmd.CreateParameter("@Content",adBSTR,adParamInput,,frmConte nt)
>>
>> adBSTR is not the correct dataype constant. Could you show us the
>> parameter declarations in your CREATE PROCEDURE statement? The
>> correct constant is probably adLongVarChar (see
>> http://www.carlprothman.net/Technology/DataTypeMapping/tabid /97/Default.aspx),
>> but we would need to see the CREATE PROCEDURE statement to be sure.
>>
>> You may need to use AppendChunk to set the parameter value
>> (http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthapp chunk.asp)
>>
>> Bob Barrows
>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so
>> I
>> don't check it very often. If you must reply off-line, then remove
>> the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: error "80040e21", String data, right truncation when append la

At first I got the error: The application has improperly defined a Parameter
object.
when I changed the data type to adLongVarChar.

I added in the size of 64000 and that seemed to do the trick.

The data is saved correctly. The strange thing now is that the value for @ID
parameter that the SQL server returns is out of wack. The value keep
changing for each input (529845528). But if I enter small amount of data,
then the returned @ID value is correct.

Thanks so very much for your help. The links you provided was exactly what
I needed.




"Bob Barrows [MVP]" wrote:

> So what happens when you use the constant I recommended in your
> createparameter call?
>
> qtlee wrote:
> > Here is the stored proc:
> >
> > CREATE PROCEDURE dbo.Add_News
> > @ReleaseDate smalldatetime,
> > @NewsHeader nvarchar(200),
> > @Content text,
> > @Harbor text,
> > @ID int OUTPUT
> > AS
> > BEGIN TRANSACTION
> >
> > INSERT INTO tblNews (ReleaseDate,NewsHeader,Content,Harbor)
> > VALUES (@ReleaseDate,
> > @NewsHeader,
> > @Content,
> > @Harbor)
> >
> >
> >
> > This test was run within the Query Analyzer:
> >
> > declare @ReturnedID int
> > exec add_news
> > @ReleaseDate = '1/1/1900',
> > @NewsHeader = 'test',
> > @Harbor = 'test',
> > @Content = '',
> > @ID = @ReturnedID output
> >
> >
> > I added about 15,000 characters for the value of @Content
> >
> > "Bob Barrows [MVP]" wrote:
> >
> >> qtlee wrote:
> >>> I am writing data to an SQL field (data type = text).
> >>>
> >>>
> >>> I am getting this error when the form is submitted with a large
> >>> amount of text. Smaller amount of text worked fine.
> >>> When I traced the size of the text being passed into the sp, this is
> >>> the value="15189"
> >>>
> >>> Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
> >>
> >> Probably nothing to do with your problem, but you should stop using
> >> ODBC.
> >> Use the SQLOLEDB provider instead (see www.connectionstrings.com)
> >>
> >>>
> >>> [Microsoft][ODBC SQL Server Driver]String data, right truncation.
> >>>
> >>>
> >>> However, when I test the stored proc in the Query Analyzer, it
> >>> worked
> >>> fine.
> >>
> >> How did you manage to test it with that amount of data in Query
> >> Analyzer? ;-)
> >>
> >>>
> >>>
> >>> I hope someone can help.
> >>> Here is the code of the param.
> >>>
> >>>
> >>> cmd.Parameters.Append
> >>> cmd.CreateParameter("@Content",adBSTR,adParamInput,,frmConte nt)
> >>
> >> adBSTR is not the correct dataype constant. Could you show us the
> >> parameter declarations in your CREATE PROCEDURE statement? The
> >> correct constant is probably adLongVarChar (see
> >> http://www.carlprothman.net/Technology/DataTypeMapping/tabid /97/Default.aspx),
> >> but we would need to see the CREATE PROCEDURE statement to be sure.
> >>
> >> You may need to use AppendChunk to set the parameter value
> >> (http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthapp chunk.asp)
> >>
> >> Bob Barrows
> >> --
> >> Microsoft MVP - ASP/ASP.NET
> >> Please reply to the newsgroup. This email account is my spam trap so
> >> I
> >> don't check it very often. If you must reply off-line, then remove
> >> the "NO SPAM"
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>