469,271 Members | 1,047 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

Function to Return Stored Procedure Record Set

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
Sep 19 '08 #1
6 3309
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"
Sep 19 '08 #2
On Sep 19, 2:49*pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
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("<h5Webcasts</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
Sep 19 '08 #3
SethM wrote:
On Sep 19, 2:49 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
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("<h5Webcasts</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"
Sep 19 '08 #4


"SethM" <se*************@sbcglobal.netwrote in message
news:35**********************************@c58g2000 hsc.googlegroups.com...
On Sep 19, 2:49 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
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("<h5Webcasts</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
Sep 19 '08 #5
On Sep 19, 3:31*pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:
SethM wrote:
On Sep 19, 2:49 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
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("<h5Webcasts</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
Sep 22 '08 #6
SethM wrote:
On Sep 19, 3:31 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:
>SethM wrote:
>>On Sep 19, 2:49 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
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.
Sep 22 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Rhino | last post: by
6 posts views Thread by CSINVA | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.