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
objConn.Open strConnect ' from connect.inc
' Create a Command object and set its ActiveConnection, CommandText,
' and CommandType properties...
Set objCmd = Server.CreateObject("ADODB
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...
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..."
"...Microsoft OLE DB Provider for ODBC Drivers error '80040e18'
The rowset was built over a live data feed and cannot be restarted..."
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..."
"...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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 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.
I'm glad that was helpful.
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