Stored Procedure Parameters (text)

I have a stored procedure that takes parameter @description with datatype
set to text.
In my ASP I have the following:
.Parameters.Append
.CreateParameter("@description",adVarChar,adParamInput,255,varDesc))

I've seen other examples of SPs with adVarChar set to 1000 and up! Is this
the way it should be done or is there a matching parameter type I should be
using?

Any help appreciated.

chopper

Bob
Mon Sep 15 07:53:38 CDT 2003

Chopper wrote:
> I have a stored procedure that takes parameter @description with
> datatype set to text.
> In my ASP I have the following:
> .Parameters.Append
> .CreateParameter("@description",adVarChar,adParamInput,255,varDesc))
>
> I've seen other examples of SPs with adVarChar set to 1000 and up! Is
> this the way it should be done or is there a matching parameter type
> I should be using?
>
I've written a Stored Procedure Code Generator which is
available for download at
http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear

You can look up the datatype mappings at
http://www.able-consulting.com/ADODataTypeEnum.htm if you wish to continue
coding these things by hand.


HTH,
Bob Barrows.

Tom
Mon Sep 15 07:56:00 CDT 2003

In your database (I assume Access) you would have set the length of the text
field. That's the value to use.


"Chopper" <chopper@despammed.com> wrote in message
news:3f65ab22$0$249$cc9e4d1f@news.dial.pipex.com...
> I have a stored procedure that takes parameter @description with datatype
> set to text.
> In my ASP I have the following:
> .Parameters.Append
> .CreateParameter("@description",adVarChar,adParamInput,255,varDesc))
>
> I've seen other examples of SPs with adVarChar set to 1000 and up! Is this
> the way it should be done or is there a matching parameter type I should
be
> using?
>
> Any help appreciated.
>
> chopper
>
>

Chopper
Mon Sep 15 08:09:22 CDT 2003


"Tom B" <shuckle@hotmail.com> wrote in message
news:eKbD5i4eDHA.892@TK2MSFTNGP12.phx.gbl...
> In your database (I assume Access) you would have set the length of the
text
> field. That's the value to use.
>
>
<snip>

Thanks for your reply.
It's SQL Server 2000. The field in the database is of type 'Text' with
length 16.
I cannot find a matching type when passing the parameter using ASP which is
what I'm trying to figure out.

Ken
Mon Sep 15 08:57:50 CDT 2003

Length = 16 is only the data that is stored inside the row. If your total
data for the Text field is 16 characters or less, it is stored in the row.
Otherwise, a pointer is stored to the data page (out of row) that holds the
data.

Check Bob Barrows' info, or, you can use the code on David Penton's side
(he's an SQL Server MVP):
http://www.davidpenton.com/testsite/scratch/adodb.command3.asp

You need to scroll down to get the point where he appends the parameter for
the Text datatype (adLongVarChar)

Cheers
Ken

"Chopper" <chopper@despammed.com> wrote in message
news:3f65b9f7$0$251$cc9e4d1f@news.dial.pipex.com...
:
: "Tom B" <shuckle@hotmail.com> wrote in message
: news:eKbD5i4eDHA.892@TK2MSFTNGP12.phx.gbl...
: > In your database (I assume Access) you would have set the length of the
: text
: > field. That's the value to use.
: >
: >
: <snip>
:
: Thanks for your reply.
: It's SQL Server 2000. The field in the database is of type 'Text' with
: length 16.
: I cannot find a matching type when passing the parameter using ASP which
is
: what I'm trying to figure out.
:
:

PB4FUN
Mon Sep 15 10:47:21 CDT 2003

Assuming your @description is the first parameter you can do this :

With Cmd
.activeconnection = CN
.Commandtext = strTheNameOfYourStoredProcedure
.commandtype = adcmdStoredProc
.Parameters(1) = strDescription (or .Parameters("@description") =
strDescription)
.execute ( or Set RS = .execute if you want a recordset
back)
End With

Define the rest in your stored procedure

Works fine in my sites

Meindert, MCP

"Chopper" <chopper@despammed.com> wrote in message
news:3f65ab22$0$249$cc9e4d1f@news.dial.pipex.com...
> I have a stored procedure that takes parameter @description with datatype
> set to text.
> In my ASP I have the following:
> .Parameters.Append
> .CreateParameter("@description",adVarChar,adParamInput,255,varDesc))
>
> I've seen other examples of SPs with adVarChar set to 1000 and up! Is this
> the way it should be done or is there a matching parameter type I should
be
> using?
>
> Any help appreciated.
>
> chopper
>
>

Chopper
Mon Sep 15 11:05:57 CDT 2003


"PB4FUN" <this_adress@doesnot.exist> wrote in message
news:e0h%231C6eDHA.1200@TK2MSFTNGP09.phx.gbl...
> Assuming your @description is the first parameter you can do this :
>
> With Cmd
> .activeconnection = CN
> .Commandtext = strTheNameOfYourStoredProcedure
> .commandtype = adcmdStoredProc
> .Parameters(1) = strDescription (or .Parameters("@description") =
> strDescription)
> .execute ( or Set RS = .execute if you want a recordset
> back)
> End With
>
> Define the rest in your stored procedure
>
> Works fine in my sites
>
> Meindert, MCP

Thanks for your reply.
Any idea why it works though? Surely it will just default...or will it work
out what the options should be?

BTW, is this a top posting group? I'm not one of these people that get hung
up about the whole top/bottom stuff (unless it's a lady :) but just curious
so someone doesn't start throwing their toys out of their pram.

PB4FUN
Mon Sep 15 11:33:10 CDT 2003

No idea why this works as well.
But I know its a lot easier programming than the .CreateParameter stuff.

Meindert, MCP

> > Assuming your @description is the first parameter you can do this :
> >
> > With Cmd
> > .activeconnection = CN
> > .Commandtext = strTheNameOfYourStoredProcedure
> > .commandtype = adcmdStoredProc
> > .Parameters(1) = strDescription (or .Parameters("@description")
=
> > strDescription)
> > .execute ( or Set RS = .execute if you want a
recordset
> > back)
> > End With
> >
> > Define the rest in your stored procedure
> >
> > Works fine in my sites
> >
> > Meindert, MCP
>
> Thanks for your reply.
> Any idea why it works though? Surely it will just default...or will it
work
> out what the options should be?
>
> BTW, is this a top posting group? I'm not one of these people that get
hung
> up about the whole top/bottom stuff (unless it's a lady :) but just
curious
> so someone doesn't start throwing their toys out of their pram.
>
>

Tom
Mon Sep 15 12:16:21 CDT 2003

Along the same lines, without a Command object you can do

Set RS=ConnectionObject("exec strTheNameOfYourStoredProcedure '" &
strDescription & "'")
or
Set RS=ConnectionObject.strTheNameOfYourStoredProcedure(strDescription)

Beware of SQL Injection though.


"PB4FUN" <this_adress@doesnot.exist> wrote in message
news:e0h%231C6eDHA.1200@TK2MSFTNGP09.phx.gbl...
> Assuming your @description is the first parameter you can do this :
>
> With Cmd
> .activeconnection = CN
> .Commandtext = strTheNameOfYourStoredProcedure
> .commandtype = adcmdStoredProc
> .Parameters(1) = strDescription (or .Parameters("@description") =
> strDescription)
> .execute ( or Set RS = .execute if you want a recordset
> back)
> End With
>
> Define the rest in your stored procedure
>
> Works fine in my sites
>
> Meindert, MCP
>
> "Chopper" <chopper@despammed.com> wrote in message
> news:3f65ab22$0$249$cc9e4d1f@news.dial.pipex.com...
> > I have a stored procedure that takes parameter @description with
datatype
> > set to text.
> > In my ASP I have the following:
> > .Parameters.Append
> > .CreateParameter("@description",adVarChar,adParamInput,255,varDesc))
> >
> > I've seen other examples of SPs with adVarChar set to 1000 and up! Is
this
> > the way it should be done or is there a matching parameter type I should
> be
> > using?
> >
> > Any help appreciated.
> >
> > chopper
> >
> >
>
>

Chopper
Tue Sep 16 05:24:44 CDT 2003


"Tom B" <shuckle@hotmail.com> wrote in message
news:uMpvY06eDHA.460@TK2MSFTNGP12.phx.gbl...
> Along the same lines, without a Command object you can do
>
> Set RS=ConnectionObject("exec strTheNameOfYourStoredProcedure '" &
> strDescription & "'")
> or
> Set RS=ConnectionObject.strTheNameOfYourStoredProcedure(strDescription)
>
> Beware of SQL Injection though.
>
>

Thanks all. Will try your suggestions out this afternoon.

Chopper
Mon Sep 22 17:14:40 CDT 2003

> "Tom B" <shuckle@hotmail.com> wrote in message
> news:uMpvY06eDHA.460@TK2MSFTNGP12.phx.gbl...
> > Along the same lines, without a Command object you can do
> >
> > Set RS=ConnectionObject("exec strTheNameOfYourStoredProcedure '" &
> > strDescription & "'")
> > or
> > Set RS=ConnectionObject.strTheNameOfYourStoredProcedure(strDescription)
> >
> > Beware of SQL Injection though.
> >
> >
>
> Thanks all. Will try your suggestions out this afternoon.
>

Only just got round to doing it. This is what I did:

In the SP:
@description text

In the ASP:
.Parameters.Append
.CreateParameter("@description",adLongVarChar,adParamInput,len(varDesc),varD
esc)

I think this is the best way as you are specifying the length of data you
are passing. I would assume that the more explicit you are, the better the
performance would be as you are leaving less for the interpreter to guess or
work out for itself.

Any feedback, comments or criticism is welcome.

Thanks.

chopper