Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 616
  • Last Modified:

Get Retrieve Dataset Rows from Stored Procedure into Classic ASP Web Page

Greetings All,

I am doing something that is, I am sure, very simple, but I am having a hard time wrapping my head around how to do it. I have a legacy site that is in classic ASP. I am trying to call a stored procedure that will return all the rows back to the page and display them. I have to use a stored procedure (I believe) because the query uses a dynamic pivot command.

Here is the stored procedure:

SET NOCOUNT ON;
	
	declare @cmd varchar(max)
	declare @columns varchar(max)
	
	set @columns = ''

	select @columns = @columns + '['+CAST(MeetingDate AS CHAR(10))+'],' from morning_call_dates WHERE Display = 1 order by MeetingDate asc
	select @columns = substring(@columns, 0, len(@columns))

	SET @cmd = '  
		SELECT *   
		FROM vw_morning_call
		PIVOT(MAX(Entry) FOR MeetingDate IN ('+@columns+')) AS MeetingDate'  
	exec (@cmd)

Open in new window


When I execute this on the server I get a nice recordset that looks like this:

MeetingAttendee       2012-12-02        2013-12-03       2013-12-04        2013-12-05
Smith                          No                      No                     Yes                     No
Jones                           Yes                     No                     No                      Yes
Adams                         Yes                     Yes                    No                      Yes

So my questions is how do I call the procedure from classic ASP to return the data above so that I can display it on my web page. I know how to do this with a regular ADO call to the server using a query but not using a stored procedure.

Thanks in advance for your help.
0
Russell Scheinberg, MCSE Data Platform 2012
Asked:
Russell Scheinberg, MCSE Data Platform 2012
  • 2
1 Solution
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
best way would be to use a command object, set your parameters (if any exist), and run it like you normally would:

                    sql = "exec sp_NameOfSP ?, ?"
                    with cmd
                        .ActiveConnection = conn
                        .CommandText = sql
                        .Parameters.Append  .CreateParameter( "param1", adInteger, adParamInput, , parameter1 )
                        .Parameters.Append  .CreateParameter( "param2", adInteger, adParamInput, , parameter2 )
                    rs.Open cmd


for more info on the command object: http://www.w3schools.com/ado/ado_ref_command.asp
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
if you have no parameters and want to do it the way you're use to, it would be very similar:

sql = "exec sp_NameOfSP"
set rs = conn.Execute( sql )
0
 
Russell Scheinberg, MCSE Data Platform 2012Author Commented:
I knew it would be something pretty simple but I could have spent hours overthinking this and killing myself in the process. Thanks so much.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now