Stored Proc as native connection object method

Discussion in 'ASP General' started by Stefan Berglund, Oct 21, 2003.

  1. I'm having difficulty making a stored procedure with multiple input parameters work as a
    native method of the connection object. The only way I could get it to work was as below
    and I'm wondering if this falls into the category of dynamic SQL and thereby negates the
    value of the stored proc. I'm fairly certain that I've tried just about every permutation
    possible and unless I've failed with the magic search criteria Google is far from helpful
    in this area for some reason.

    Dim strShowID
    strShowID = 320
    Dim strSort
    strSort = "Owner"
    Dim rs
    Set rs = cn.Execute("exec show_ShowEntries " & strShowID & "," & strSort)

    or alternatively

    Set rs = CreateObject("adodb.recordset")
    rs.Open "show_ShowEntries " & strShowID & "," & strSort, cn, , adCmdStoredProc

    The following code works in VB using all variants and late binding:

    Set rs = CreateObject("ADODB.Recordset")
    cn.show_ShowEntries strShowID,strSort,rs

    but yields this error message when used on an ASP.

    Parameter object is improperly defined. Inconsistent or
    incomplete information was provided.


    For the record the stored proc looks like this:
    CREATE PROCEDURE show_ShowEntries @ShowID INT, @Sort VARCHAR(7)='Number' AS
    SET NOCOUNT ON

    etc.
     
    Stefan Berglund, Oct 21, 2003
    #1
    1. Advertising

  2. Stefan Berglund

    Bob Barrows Guest

    Stefan Berglund wrote:
    >
    > Set rs = CreateObject("ADODB.Recordset")
    > cn.show_ShowEntries strShowID,strSort,rs
    >
    > but yields this error message when used on an ASP.
    >
    > Parameter object is improperly defined. Inconsistent or
    > incomplete information was provided.
    >
    >
    > For the record the stored proc looks like this:
    > CREATE PROCEDURE show_ShowEntries @ShowID INT, @Sort
    > VARCHAR(7)='Number' AS
    > SET NOCOUNT ON


    Have you verified that strShowID and strSort both contain values? The only
    way I can reproduce this error is if I fail to provide a value for either
    strShowID or strSort. If you do not wish to pass a value for strSort, set it
    to Null:

    strShowID = 28
    strSort = null
    Set conn = Server.CreateObject("ADODB.Connection")
    Set rs = Server.CreateObject("ADODB.Recordset")

    conn.open strConn

    Set rs = CreateObject("ADODB.Recordset")
    conn.show_ShowEntries strShowID,strSort,rs

    HTH,
    Bob Barrows
    --
    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.
     
    Bob Barrows, Oct 21, 2003
    #2
    1. Advertising

  3. On Tue, 21 Oct 2003 16:59:06 -0400, "Bob Barrows"
    <> wrote:
    in <#>

    >Stefan Berglund wrote:
    >>
    >> Set rs = CreateObject("ADODB.Recordset")
    >> cn.show_ShowEntries strShowID,strSort,rs
    >>
    >> but yields this error message when used on an ASP.
    >>
    >> Parameter object is improperly defined. Inconsistent or
    >> incomplete information was provided.
    >>
    >>
    >> For the record the stored proc looks like this:
    >> CREATE PROCEDURE show_ShowEntries @ShowID INT, @Sort
    >> VARCHAR(7)='Number' AS
    >> SET NOCOUNT ON

    >
    >Have you verified that strShowID and strSort both contain values? The only
    >way I can reproduce this error is if I fail to provide a value for either
    >strShowID or strSort. If you do not wish to pass a value for strSort, set it
    >to Null:
    >
    >strShowID = 28
    >strSort = null
    >Set conn = Server.CreateObject("ADODB.Connection")
    >Set rs = Server.CreateObject("ADODB.Recordset")
    >
    >conn.open strConn
    >
    > Set rs = CreateObject("ADODB.Recordset")
    > conn.show_ShowEntries strShowID,strSort,rs
    >
    >HTH,
    >Bob Barrows


    That was it Bob, the second parameter was empty on the first
    pass. I wrongly assumed that procedure's default value would
    override that. Thanks again.
     
    Stefan Berglund, Oct 21, 2003
    #3
    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. David Lozzi
    Replies:
    3
    Views:
    1,952
    David Lozzi
    Jun 1, 2005
  2. NevilleDNZ
    Replies:
    9
    Views:
    454
    NevilleDNZ
    Aug 16, 2006
  3. Cindy Lee
    Replies:
    4
    Views:
    1,373
    Mr. Arnold
    Jul 30, 2010
  4. Jean-Hugues ROBERT

    Why no Proc##[]=() ? Why no Proc##replace() ?

    Jean-Hugues ROBERT, May 1, 2004, in forum: Ruby
    Replies:
    14
    Views:
    303
    Jean-Hugues ROBERT
    May 5, 2004
  5. Minkoo Seo

    Proc vs lambda vs proc

    Minkoo Seo, Feb 4, 2007, in forum: Ruby
    Replies:
    19
    Views:
    254
    Brian Candler
    Feb 6, 2007
Loading...

Share This Page