Stored Procedure Parameters (text)

Discussion in 'ASP General' started by Chopper, Sep 15, 2003.

  1. Chopper

    Chopper Guest

    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, Sep 15, 2003
    #1
    1. Advertising

  2. Chopper

    Bob Barrows Guest

    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.
    Bob Barrows, Sep 15, 2003
    #2
    1. Advertising

  3. Chopper

    Tom B Guest

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


    "Chopper" <> wrote in message
    news:3f65ab22$0$249$...
    > 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
    >
    >
    Tom B, Sep 15, 2003
    #3
  4. Chopper

    Chopper Guest

    "Tom B" <> wrote in message
    news:...
    > 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.
    Chopper, Sep 15, 2003
    #4
  5. Chopper

    Ken Schaefer Guest

    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" <> wrote in message
    news:3f65b9f7$0$251$...
    :
    : "Tom B" <> wrote in message
    : news:...
    : > 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, Sep 15, 2003
    #5
  6. Chopper

    PB4FUN Guest

    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" <> wrote in message
    news:3f65ab22$0$249$...
    > 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
    >
    >
    PB4FUN, Sep 15, 2003
    #6
  7. Chopper

    Chopper Guest

    "PB4FUN" <> wrote in message
    news:e0h%...
    > 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.
    Chopper, Sep 15, 2003
    #7
  8. Chopper

    PB4FUN Guest

    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.
    >
    >
    PB4FUN, Sep 15, 2003
    #8
  9. Chopper

    Tom B Guest

    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" <> wrote in message
    news:e0h%...
    > 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" <> wrote in message
    > news:3f65ab22$0$249$...
    > > 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
    > >
    > >

    >
    >
    Tom B, Sep 15, 2003
    #9
  10. Chopper

    Chopper Guest

    "Tom B" <> wrote in message
    news:...
    > 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, Sep 16, 2003
    #10
  11. Chopper

    Chopper Guest

    > "Tom B" <> wrote in message
    > news:...
    > > 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
    Chopper, Sep 22, 2003
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. kavitha N via .NET 247

    passing parameters to stored procedure from crystal reports

    kavitha N via .NET 247, Jun 8, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    10,789
    galahad
    Feb 15, 2011
  2. TaeHo Yoo
    Replies:
    0
    Views:
    395
    TaeHo Yoo
    Aug 14, 2003
  3. =?Utf-8?B?SklNLkgu?=

    Q: number of parameters in stored procedure

    =?Utf-8?B?SklNLkgu?=, Jan 11, 2005, in forum: ASP .Net
    Replies:
    2
    Views:
    1,275
    Steve C. Orr [MVP, MCSD]
    Jan 12, 2005
  4. =?Utf-8?B?TWlrZQ==?=
    Replies:
    0
    Views:
    743
    =?Utf-8?B?TWlrZQ==?=
    Jun 22, 2005
  5. Mike P
    Replies:
    0
    Views:
    3,284
    Mike P
    Jun 19, 2006
Loading...

Share This Page