Link to home
Start Free TrialLog in
Avatar of ptpovo
ptpovo

asked on

Loop through a Recordset - Display/group rows in separate tables...


<!-- #include virtual="/adovbs.inc" -->
<!-- #include virtual="/connect.inc" -->

<%

' Grab the value posted to this page from form.  This value is passed to
' the stored procedure as a parameter.  It is also used to customize the page text

      Set procVar = Request.Form("value1")

' Establish a connection with a datasource

      Set objConn = Server.CreateObject("ADODB.Connection")
      objConn.Open strConnect      ' from connect.inc

' Create a Command object and set its ActiveConnection, CommandText,
' and CommandType properties...

      Set objCmd = Server.CreateObject("ADODB.Command")
      objCmd.ActiveConnection = objConn
      objCmd.CommandText = "{call sp_myprocedure(?)}"
      objCmd.CommandType = adCmdText

' use the Command object's Parameters.Refresh method to create and populate a Parameter object...

      objCmd.Parameters.Refresh
      objCmd(0) = procVar            'This is where value1 from the form goes...

' Call the command object's Execute method.  If you are expecting a recordset object
' from the stored procedure, the Execute method returns a recordset object...

      Set objRs = objCmd.Execute

%>

Currently, I'm dumping objRs into a single table.  For the sake of this example, say each row contains a
ZIP code field (I won't know what the actual values will be).  I'd like to display the rows in groups,
(ie...a separate table for group of rows with the same ZIP code. I'm used to using Crystal Reports for this
type of visual grouping and it's quite easy.  However, I don't want to use Crystal Web Reports or Automation
Server, just straight ASP will do...
Avatar of TTom
TTom

Once you have the recordset, you can filter it for each grouping and loop to create separate tables.

rs.Filter = "Zip < 11111"
rs.MoveFirst
response.write "<table>"
do while not rs.EOF
   response.write "<tr><td>...."
   rs.MoveNext
loop
response.write "</table>"
rs.Filter = "Zip > 11110"
rs.MoveFirst

and repeat the loop.

Should work fine, as long as you can come up with some valid criteria to evaluate and group on.

I have done this successfully in a single table with a limited number of options.  Instead of creating a new table, I only wanted to insert a new row which indicated my grouping.  Worked fine.

HTH,

Tom
Avatar of ptpovo

ASKER

Thanks for your quick response, however I'm getting the following error.

"...Microsoft OLE DB Provider for ODBC Drivers error '80040e18'

The rowset was built over a live data feed and cannot be restarted..."
Avatar of ptpovo

ASKER

Thanks for your quick response, however I'm getting the following error.

"...Microsoft OLE DB Provider for ODBC Drivers error '80040e18'

The rowset was built over a live data feed and cannot be restarted..."
Hi,

Why not try to use plain SQL statement?
After you dumb objRS to a table, said tDumb, you can make a query to that table, like this:

<%...
  rs.open "Select * From tDumb Group By ZipCode", strConnect

....%>

Good Luck
Avatar of ptpovo

ASKER

Hencah, my apologies I wasn't entirely clear.  objRs is populating an <HTML> table rather than a database table.  TTom's answer is on the right track.
ptpovo:

May have to do with cursor location.  Are you working with a "client-side" cursor?  If not, this method may not work.  It predicates that all the records are at the client machine, and it simply displays them differently.

I will take a look at my code and see if I can post a working model.

Tom
ASKER CERTIFIED SOLUTION
Avatar of mayhew
mayhew

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ptpovo

ASKER

Thanks mayhew! You got it exactly.
I added one thing to end the Do...

....
   objRs.MoveNext

'Capture EOF and exit the Do loop
   if objRs.EOF then
      exit do
   end if

   if objRs(0) <> strTemp then
....
I always forget that part (movenext).  :)

I'm glad that was helpful.