Stored Procedure Parameters (text)

[Chopper]

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 Barrows]

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 B]

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

 

[Chopper]

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 Schaefer]

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

:
: : > 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]

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]

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]

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

Meindert, MCP

 

[Tom B]

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.

 

[Chopper]

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]

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