Function to Return Stored Procedure Record Set

Discussion in 'ASP General' started by SethM, Sep 19, 2008.

  1. SethM

    SethM Guest

    I have a stored procedure that returns a record set. I want to
    functionalize this so I can have multiple presentations of the same
    record set. However, I can not get rs_event.open StoreProc to pass
    through the function, so I can use rs_event("Title"), etc, etc. Is
    this possible to do? If so how? Thanks.

    Regards,

    Seth
    SethM, Sep 19, 2008
    #1
    1. Advertising

  2. SethM wrote:
    > I have a stored procedure that returns a record set. I want to
    > functionalize this so I can have multiple presentations of the same
    > record set. However, I can not get rs_event.open StoreProc to pass
    > through the function, so I can use rs_event("Title"), etc, etc. Is
    > this possible to do? If so how? Thanks.
    >

    Not sure what you mean by "functionalize" or "multiple presentations of the
    same record set" (why would you want to do that??)

    Maybe if you show some code, we can get a better idea of what you are trying
    to do.

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Sep 19, 2008
    #2
    1. Advertising

  3. SethM

    SethM Guest

    On Sep 19, 2:49 pm, "Bob Barrows [MVP]" <>
    wrote:
    > Not sure what you mean by "functionalize" or "multiple presentations of the
    > same record set" (why would you want to do that??)
    >
    > Maybe if you show some code, we can get a better idea of what you are trying
    > to do.
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"


    Thanks for the reply. The following is what I have now.

    Function DisplayEvents(strEventType)

    Set cmdStoredProcEvent = Server.CreateObject("ADODB.Command")
    set rs_event = server.createobject("ADODB.recordset")
    With cmdStoredProcEvent
    .ActiveConnection = conn
    .CommandText = "get_event_list_sp"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("@retval", adInteger,
    adParamOutput)
    'add input parameters
    .Parameters.Append .CreateParameter("@prod_fam", adVarChar,
    adParamInput,150,strProductFamily)
    .Parameters.Append .CreateParameter("@event_type", adVarChar,
    adParamInput,15,strEventType)
    .Execute, , adExecuteNoRecords
    retval = .Parameters("@retval")
    End With
    IF strEventType = "webcast" THEN

    response.Write("<h5> Webcasts</h5>")
    rs_event.Open cmdStoredProcEvent

    ....displays values from the recordset

    End Function

    I would like to remove the display of the recordset from the function
    to enable me to have different presentations of the information. I
    would like to see if I could just call DisplayEvents(strEventType) and
    get the object rs_event and control the display of the information at
    the page level rather than at the function level. Thanks for any help.

    Regards,

    Seth
    SethM, Sep 19, 2008
    #3
  4. SethM wrote:
    > On Sep 19, 2:49 pm, "Bob Barrows [MVP]" <>
    > wrote:
    >> Not sure what you mean by "functionalize" or "multiple presentations
    >> of the same record set" (why would you want to do that??)
    >>
    >> Maybe if you show some code, we can get a better idea of what you
    >> are trying to do.
    >>
    >> --
    >> Microsoft MVP - ASP/ASP.NET
    >> Please reply to the newsgroup. This email account is my spam trap so
    >> I don't check it very often. If you must reply off-line, then remove
    >> the "NO SPAM"

    >
    > Thanks for the reply. The following is what I have now.
    >
    > Function DisplayEvents(strEventType)
    >
    > Set cmdStoredProcEvent = Server.CreateObject("ADODB.Command")
    > set rs_event = server.createobject("ADODB.recordset")
    > With cmdStoredProcEvent
    > .ActiveConnection = conn
    > .CommandText = "get_event_list_sp"
    > .CommandType = adCmdStoredProc
    > .Parameters.Append .CreateParameter("@retval", adInteger,
    > adParamOutput)
    > 'add input parameters
    > .Parameters.Append .CreateParameter("@prod_fam", adVarChar,
    > adParamInput,150,strProductFamily)
    > .Parameters.Append .CreateParameter("@event_type", adVarChar,
    > adParamInput,15,strEventType)
    > .Execute, , adExecuteNoRecords
    > retval = .Parameters("@retval")
    > End With
    > IF strEventType = "webcast" THEN
    >
    > response.Write("<h5> Webcasts</h5>")
    > rs_event.Open cmdStoredProcEvent
    >
    > ...displays values from the recordset
    >
    > End Function
    >
    > I would like to remove the display of the recordset from the function
    > to enable me to have different presentations of the information. I
    > would like to see if I could just call DisplayEvents(strEventType) and
    > get the object rs_event and control the display of the information at
    > the page level rather than at the function level. Thanks for any help.
    >
    > Regards,
    >
    > Seth


    So add

    Set DisplayEvents = rs_event

    before the End Function line so the function can return the recordset to the
    caller, which calls it by:

    Set rs = DisplayEvents(eventtype)

    I'm still not clear what you are trying to do. Why would a single page need
    to display the same recordset data in multiple ways? I hope you're not
    planning to call this function multiple times in the same page ... it would
    be extremely wasteful to make several trips to the database to retrieve the
    same set of data ...

    Perhaps you should leave this function as is and declare a page-level
    recordset variable, allowing you to call the function once.
    I would also suggest you use a disconnected recordset so you can close your
    connection while processing the recordset.

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Sep 19, 2008
    #4
  5. SethM

    Bob Lehmann Guest

    "SethM" <> wrote in message
    news:...
    On Sep 19, 2:49 pm, "Bob Barrows [MVP]" <>
    wrote:
    > Not sure what you mean by "functionalize" or "multiple presentations of

    the
    > same record set" (why would you want to do that??)
    >
    > Maybe if you show some code, we can get a better idea of what you are

    trying
    > to do.
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"


    Thanks for the reply. The following is what I have now.

    Function DisplayEvents(strEventType)

    Set cmdStoredProcEvent = Server.CreateObject("ADODB.Command")
    set rs_event = server.createobject("ADODB.recordset")
    With cmdStoredProcEvent
    ..ActiveConnection = conn
    ..CommandText = "get_event_list_sp"
    ..CommandType = adCmdStoredProc
    ..Parameters.Append .CreateParameter("@retval", adInteger,
    adParamOutput)
    'add input parameters
    ..Parameters.Append .CreateParameter("@prod_fam", adVarChar,
    adParamInput,150,strProductFamily)
    ..Parameters.Append .CreateParameter("@event_type", adVarChar,
    adParamInput,15,strEventType)
    ..Execute, , adExecuteNoRecords
    retval = .Parameters("@retval")
    End With
    IF strEventType = "webcast" THEN

    response.Write("<h5> Webcasts</h5>")
    rs_event.Open cmdStoredProcEvent

    ....displays values from the recordset

    End Function

    I would like to remove the display of the recordset from the function
    to enable me to have different presentations of the information. I
    would like to see if I could just call DisplayEvents(strEventType) and
    get the object rs_event and control the display of the information at
    the page level rather than at the function level. Thanks for any help.

    Regards,

    Seth

    Why not pass the recordset to functions that only do one thing (cohesion),
    and have each function have different output?

    Bob Lehmann
    Bob Lehmann, Sep 20, 2008
    #5
  6. SethM

    SethM Guest

    On Sep 19, 3:31 pm, "Bob Barrows [MVP]" <>
    wrote:
    > SethM wrote:
    > > On Sep 19, 2:49 pm, "Bob Barrows [MVP]" <>
    > > wrote:
    > >> Not sure what you mean by "functionalize" or "multiple presentations
    > >> of the same record set" (why would you want to do that??)

    >
    > >> Maybe if you show some code, we can get a better idea of what you
    > >> are trying to do.

    >
    > >> --
    > >> Microsoft MVP - ASP/ASP.NET
    > >> Please reply to the newsgroup. This email account is my spam trap so
    > >> I don't check it very often. If you must reply off-line, then remove
    > >> the "NO SPAM"

    >
    > > Thanks for the reply. The following is what I have now.

    >
    > > Function DisplayEvents(strEventType)

    >
    > > Set cmdStoredProcEvent = Server.CreateObject("ADODB.Command")
    > > set rs_event = server.createobject("ADODB.recordset")
    > > With cmdStoredProcEvent
    > > .ActiveConnection = conn
    > > .CommandText = "get_event_list_sp"
    > > .CommandType = adCmdStoredProc
    > > .Parameters.Append .CreateParameter("@retval", adInteger,
    > > adParamOutput)
    > > 'add input parameters
    > > .Parameters.Append .CreateParameter("@prod_fam", adVarChar,
    > > adParamInput,150,strProductFamily)
    > > .Parameters.Append .CreateParameter("@event_type", adVarChar,
    > > adParamInput,15,strEventType)
    > > .Execute, , adExecuteNoRecords
    > > retval = .Parameters("@retval")
    > > End With
    > > IF strEventType = "webcast" THEN

    >
    > > response.Write("<h5> Webcasts</h5>")
    > > rs_event.Open cmdStoredProcEvent

    >
    > > ...displays values from the recordset

    >
    > > End Function

    >
    > > I would like to remove the display of the recordset from the function
    > > to enable me to have different presentations of the information. I
    > > would like to see if I could just call DisplayEvents(strEventType) and
    > > get the object rs_event and control the display of the information at
    > > the page level rather than at the function level. Thanks for any help.

    >
    > > Regards,

    >
    > > Seth

    >
    > So add
    >
    > Set DisplayEvents =  rs_event
    >
    > before the End Function line so the function can return the recordset to the
    > caller, which calls it by:
    >
    > Set rs = DisplayEvents(eventtype)
    >
    > I'm still not clear what you are trying to do. Why would a single page need
    > to display the same recordset data in multiple ways? I hope you're not
    > planning to call this function multiple times in the same page ... it would
    > be extremely wasteful to make several trips to the database to retrieve the
    > same set of data ...
    >
    > Perhaps you should leave this function as is and declare a page-level
    > recordset variable, allowing you to call the function once.
    > I would also suggest you use a disconnected recordset so you can close your
    > connection while processing the recordset.
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"


    Hi Bob and Bob

    **Side note - thoughts of Office Space running through my head
    now...haha, anyway I digress.

    Thank you for your responses. After reading my posts, I am not sure I
    explained what I am doing well enough. So let me try again.

    I have a stored procedure which gets a event type input to it and
    returns the necessary information based on that input. I have this
    stored procedure within a function so that whenever I want to output
    the information to any part of page on my site I can. In an effort to
    separate data from presentation, I would like to have the specific
    page that I am calling the function on handle the display of the
    return record set. There are potentially three different types of
    events. Webcasts, Tradeshows and training. So on a single page I could
    be calling the function between 1 and 3 times. I tried returning the
    recordset like Set rs = DisplayEvents(eventtype), however I got an
    error of the response buffer reaching its limit. I want to do this
    because on different pages I want the recordset displayed within a
    table, while on another page I may want it displayed in a list. I hope
    this helps to clarify what I am trying to do with this function.
    Thanks for the help.

    Regards,

    Seth
    SethM, Sep 22, 2008
    #6
  7. SethM wrote:
    > On Sep 19, 3:31 pm, "Bob Barrows [MVP]" <>
    > wrote:
    >> SethM wrote:
    >>> On Sep 19, 2:49 pm, "Bob Barrows [MVP]" <>
    >>> wrote:
    >>>> Not sure what you mean by "functionalize" or "multiple
    >>>> presentations of the same record set" (why would you want to do
    >>>> that??)

    >>
    >>>> Maybe if you show some code, we can get a better idea of what you
    >>>> are trying to do.

    >>
    >>
    >>
    >> I'm still not clear what you are trying to do. Why would a single
    >> page need to display the same recordset data in multiple ways? I
    >> hope you're not planning to call this function multiple times in the
    >> same page ... it would be extremely wasteful to make several trips
    >> to the database to retrieve the same set of data ...
    >>
    >> Perhaps you should leave this function as is and declare a page-level
    >> recordset variable, allowing you to call the function once.
    >> I would also suggest you use a disconnected recordset so you can
    >> close your connection while processing the recordset.
    >>

    >
    > Hi Bob and Bob
    >
    > **Side note - thoughts of Office Space running through my head
    > now...haha, anyway I digress.
    >
    > Thank you for your responses. After reading my posts, I am not sure I
    > explained what I am doing well enough. So let me try again.
    >
    > I have a stored procedure which gets a event type input to it and
    > returns the necessary information based on that input. I have this
    > stored procedure within a function so that whenever I want to output
    > the information to any part of page on my site I can. In an effort to
    > separate data from presentation, I would like to have the specific
    > page that I am calling the function on handle the display of the
    > return record set. There are potentially three different types of
    > events. Webcasts, Tradeshows and training. So on a single page I could
    > be calling the function between 1 and 3 times. I tried returning the
    > recordset like Set rs = DisplayEvents(eventtype), however I got an
    > error of the response buffer reaching its limit. I want to do this
    > because on different pages I want the recordset displayed within a
    > table, while on another page I may want it displayed in a list. I hope
    > this helps to clarify what I am trying to do with this function.
    > Thanks for the help.
    >

    How many records could be potentially returned for each event type?
    Frankly, I've never seen a response buffer limit error from opening a
    recordset!

    Now it sounds as if you need to use this function on multiple pages, so
    it needs to live in a server-side include file (SSI), right?

    --
    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 [MVP], Sep 22, 2008
    #7
    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.

Share This Page