[Webinar] Streamline your web hosting managementRegister Today

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

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...
0
ptpovo
Asked:
ptpovo
  • 4
  • 2
  • 2
  • +1
1 Solution
 
TTomCommented:
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
0
 
ptpovoAuthor Commented:
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..."
0
 
ptpovoAuthor Commented:
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..."
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
HencahCommented:
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
0
 
ptpovoAuthor Commented:
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.
0
 
TTomCommented:
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
0
 
mayhewCommented:
If I understand what you're saying, there might be a relatively easy solution.

First let me make sure I understand the question.

Say you have a dataset like the following:

Zip   ....
11111  ....
11111  ....
11111  ....
11222  ....
11222  ....
11222  ....
11222  ....
13456  ....
13456  ....
13456  ....


You're saying you want to print each group of zip codes in a separate table, so the output would look something like:

11111  ....
11111  ....
11111  ....

11222  ....
11222  ....
11222  ....
11222  ....

13456  ....
13456  ....
13456  ....


Is that correct?

Assuming that your zip codes are grouped together (do an order by in your stored proc), you should be able to do the following:


response.write "<table>"
objRs.movefirst

strTemp = objRs(0)
'Assumes the zip or whatever field, is the first in the resultset.

do while not objRs.EOF
   response.write "<tr><td>...."
   objRs.MoveNext

   if objRs(0) <> strTemp then
     response.write "</table>"
     response.write "<table>"

     strTemp = objRs(0)
   end if
loop

response.write "</table>"



I didn't test this particular piece of code, but I've used that algorithm before.

This should put you pretty close.  Either that or I've missed the point of the question.
0
 
ptpovoAuthor Commented:
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
....
0
 
mayhewCommented:
I always forget that part (movenext).  :)

I'm glad that was helpful.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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