Size value for CreateParameter with text/ntext fields

When passing a large block of text to an MS SQL Stored procedure using teh
command object and the parameters collection, what value should I use for the
size?

The field in the SQL table is defined as ntext. When the page was originally
written the authors used ado recordset to populate the record in the SQL
table. I've coded a new page that inserts the record using an SQL stored
procedure which accepts the values as parameters for teh SP.

In the ASP page, I create the ADODB.Command oject and then do a create
parameter for each param in the SP. The field on the form is a textarea so
there is no easy way to limit the amount of text a user enters.

Anybody have an answer for this?

Thanks,
John

Re: Size value for CreateParameter with text/ntext fields

John Beschler wrote:
> When passing a large block of text to an MS SQL Stored procedure
> using teh command object and the parameters collection, what value
> should I use for the size?
>

This value is supposed to be ignored for these datatypes, but I have found
that setting it to the actual size of the data being passed (len(parmvalue))
works.



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Size value for CreateParameter with text/ntext fields

Would there be any issues with unicode and Len? Dunno much about it,
suffice to say he is using ntext.


"Bob Barrows [MVP]" wrote in message
news:eQHn0aw3FHA.3600@TK2MSFTNGP12.phx.gbl...
> John Beschler wrote:
> > When passing a large block of text to an MS SQL Stored procedure
> > using teh command object and the parameters collection, what value
> > should I use for the size?
> >
>
> This value is supposed to be ignored for these datatypes, but I have found
> that setting it to the actual size of the data being passed
(len(parmvalue))
> works.
>
>
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Re: Size value for CreateParameter with text/ntext fields

Could be, I've never used n... columns.
It probably can't hurt to double the result from len() with a ntext
parameter.


David Morgan wrote:
> Would there be any issues with unicode and Len? Dunno much about it,
> suffice to say he is using ntext.
>
>
> "Bob Barrows [MVP]" wrote in message
> news:eQHn0aw3FHA.3600@TK2MSFTNGP12.phx.gbl...
>> John Beschler wrote:
>>> When passing a large block of text to an MS SQL Stored procedure
>>> using teh command object and the parameters collection, what value
>>> should I use for the size?
>>>
>>
>> This value is supposed to be ignored for these datatypes, but I have
>> found that setting it to the actual size of the data being passed
>> (len(parmvalue)) works.
>>
>>


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Size value for CreateParameter with text/ntext fields

Actually, the text is plain text. The only reason the field is ntext is
because the people who set it up didn't know any better.

I never thought about using the actual length of the text.


Thanks for the tip.

"Bob Barrows [MVP]" wrote:

> Could be, I've never used n... columns.
> It probably can't hurt to double the result from len() with a ntext
> parameter.
>
>
> David Morgan wrote:
> > Would there be any issues with unicode and Len? Dunno much about it,
> > suffice to say he is using ntext.
> >
> >
> > "Bob Barrows [MVP]" wrote in message
> > news:eQHn0aw3FHA.3600@TK2MSFTNGP12.phx.gbl...
> >> John Beschler wrote:
> >>> When passing a large block of text to an MS SQL Stored procedure
> >>> using teh command object and the parameters collection, what value
> >>> should I use for the size?
> >>>
> >>
> >> This value is supposed to be ignored for these datatypes, but I have
> >> found that setting it to the actual size of the data being passed
> >> (len(parmvalue)) works.
> >>
> >>
>
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Re: Size value for CreateParameter with text/ntext fields

I would suggest they set it up that way because they _did_ know better. I
am wishing I had done this a year or so ago as we now move in to
international markets.


"John Beschler" wrote in message
news:F323A629-E249-4016-9082-822244ABE0EB@microsoft.com...
> Actually, the text is plain text. The only reason the field is ntext is
> because the people who set it up didn't know any better.
>
> I never thought about using the actual length of the text.
>
>
> Thanks for the tip.
>
> "Bob Barrows [MVP]" wrote:
>
> > Could be, I've never used n... columns.
> > It probably can't hurt to double the result from len() with a ntext
> > parameter.
> >
> >
> > David Morgan wrote:
> > > Would there be any issues with unicode and Len? Dunno much about it,
> > > suffice to say he is using ntext.
> > >
> > >
> > > "Bob Barrows [MVP]" wrote in message
> > > news:eQHn0aw3FHA.3600@TK2MSFTNGP12.phx.gbl...
> > >> John Beschler wrote:
> > >>> When passing a large block of text to an MS SQL Stored procedure
> > >>> using teh command object and the parameters collection, what value
> > >>> should I use for the size?
> > >>>
> > >>
> > >> This value is supposed to be ignored for these datatypes, but I have
> > >> found that setting it to the actual size of the data being passed
> > >> (len(parmvalue)) works.
> > >>
> > >>
> >
> >
> > --
> > Microsoft MVP -- ASP/ASP.NET
> > Please reply to the newsgroup. The email account listed in my From
> > header is my spam trap, so I don't check it very often. You will get a
> > quicker response by posting to the newsgroup.
> >
> >
> >

Re: Size value for CreateParameter with text/ntext fields

Actually, this app was designed by a couple of student co-ops with virtually
zero programming experience, so I doubt it was a concious design decision.

Having said that, I msut say that, overall, considering their lack of
experience and education in this area, they did a remarkable job. This is
part of our intranet site, and only one of many tools that they created for
us that are used regularly by the over 500 employees we have at this location.

There is certainly something to be said for youth and enthusiasm!

While we have learned much since the early days of the implementation of our
Intranet, much of what the co-ops designed has remained with only minor
tweaks here and there to fix glitches or add new features.

Thanks again for your help.




"David Morgan" wrote:

> I would suggest they set it up that way because they _did_ know better. I
> am wishing I had done this a year or so ago as we now move in to
> international markets.
>
>
> "John Beschler" wrote in message
> news:F323A629-E249-4016-9082-822244ABE0EB@microsoft.com...
> > Actually, the text is plain text. The only reason the field is ntext is
> > because the people who set it up didn't know any better.
> >
> > I never thought about using the actual length of the text.
> >
> >
> > Thanks for the tip.
> >
> > "Bob Barrows [MVP]" wrote:
> >
> > > Could be, I've never used n... columns.
> > > It probably can't hurt to double the result from len() with a ntext
> > > parameter.
> > >
> > >
> > > David Morgan wrote:
> > > > Would there be any issues with unicode and Len? Dunno much about it,
> > > > suffice to say he is using ntext.
> > > >
> > > >
> > > > "Bob Barrows [MVP]" wrote in message
> > > > news:eQHn0aw3FHA.3600@TK2MSFTNGP12.phx.gbl...
> > > >> John Beschler wrote:
> > > >>> When passing a large block of text to an MS SQL Stored procedure
> > > >>> using teh command object and the parameters collection, what value
> > > >>> should I use for the size?
> > > >>>
> > > >>
> > > >> This value is supposed to be ignored for these datatypes, but I have
> > > >> found that setting it to the actual size of the data being passed
> > > >> (len(parmvalue)) works.
> > > >>
> > > >>
> > >
> > >
> > > --
> > > Microsoft MVP -- ASP/ASP.NET
> > > Please reply to the newsgroup. The email account listed in my From
> > > header is my spam trap, so I don't check it very often. You will get a
> > > quicker response by posting to the newsgroup.
> > >
> > >
> > >
>
>
>