Nested Repeat - ASP

[Guest]

I've know that out of the box, DW won't let you nest multiple recordsets within each other.

So this would need to be a proprietary fix.I've done something like this before but how do you tie the second recordset to the current record in a repeat region? What is the "MM" server variable to use or is this an ASP?

[Lon_Winters]

You would have to write the query as a single recordset, and you may need to use a nested select statemetn in SQL.

Or check out this extension:

http://www.tom-muck.com/extensions/help/simulatednestedregion/

[Guest]

Here is an example of what I am trying to do.

I need to have a recordset with multiple products. The "Warning" needs to pull in from another recordset because it's repeating

For this to work, the program needs to know what record (Product 1, 2 or 3) the item is on in order to pull in the right Warning labels.

On on another page (detail page with one record) this works just fine because I am able to pass a variable to the stored procedure.

If it isn't possible to have two recordsets nested, could a few lines be added to my stored procedure that repeats? It doesn't seem as though this would work because it needs to bring in multiple rows and if each Product has multiple rows, it would be duplicating items ( 2 Product 1...)

*************************************************

Product 1

Price

     Warning - Choking Hazard

Product 2

Price

     Warning - Choking Hazard

     Warning - Small Parts

Product 3

Price

     Warning - Small Parts

[bregent]

I don't quite understand where you are having the problem. The product id from the outer (product) recordset would be used in the where clause of the inner (warning) recordset. What exactly is the problem?

[Guest]

I'm actually already doing that to the page

[Guest]

Each product can have different/multiple Warnings

I've "Normalized" the tables:

I have one table with product listings

One table that has just warning codes and text.

Third table that contains product ID and Warning Number

So, if a product has two warnings, I think I would need two recordsets: one to pull in the products and another that pulls in the warning codes and text. I don't know how to tie these together.

[bregent]

>So, if a product has two warnings, I think I would need two recordsets:

>one to pull in the products and another that pulls in the warning codes

>and text. I don't know how to tie these together.

I understand that. You have an outer recordset that lists the products. You have a inner recordset that displays the warnings below each product. The product id in the outer recordset is used in the where clause of the inner recordset used to display the warning. The inner recordset gets executed once for each product displayed. This is straightforward so I'm not sure where the breakdown is occuring.

[Guest]

What do I use in the second recordset to pull in the correct Warnings from each product listing?

Normally I use:

Request.Querystring("warning")

or Session("ssWarning")

or Request.Form("Warning")

[bregent]

The warnings recordset query would require the product id value from the current row of the product recordset. Something like:

<%=(objRS.Fields.Item("ProductID").Value)%>

There are various ways to reference values from the recordset. Use the same syntax that is used elsewhere in the generated code.

[Guest]

I looked at that a little more and I don't really understand where to plug that in?

[Guest]

This is straightforward so I'm not sure where the breakdown is occuring.

Could you explain how to do it?

[Guest]

ok, i'LL TRY THAT OUT

[Guest]

So I open up the recordset in Dreamweaver that I want use to pull in the Warnings.

Since this is using advanced mode, I type the following in the Advanced Window:

1. Name of Recordset

2. Connection

3. SQL: {call user.procedurename(variable1)}

4. Parameters (+) Add parameter

     Name

     Type Text

     Value:???

     Default value:7-1203-12

OK...

I hope this makes sense.

[bregent]

You are going to have to do some of this manually. If you want to use the advanced recordset behavior, then just plug in any value. Then, edit your SQL in the generated code and add the code to the where clause.

[Guest]

I'm doing this with Stored Procedures so the where statement isn't in the page itself but in the Database

[bregent]

Then just pass the recordset value as a parameter to the procedure. Just curious...why are you using a stored procedure for a simple select?

[Guest]

All my recordsets are done with SP.

It's easier to manage a bunch of pages if all I have to do is edit one SP to make a slight change. You very well could be right but I think this is the first time I've heard someone suggesting SP shouldn't be used.

[Guest]

By the way, thanks for helping. I know it's a difficult thing to explain, so many dimensions to it.

I just wish we had the old NNTP servers so that more people could contribute and it all wouldn't be on one person as it seems to be here.

[Guest]

I'm doing this with Stored Procedures so the where statement isn't in the page itself but in the Database

I don't understand how the where statement has anything to do with this.

If I have a stored procedure that is pulling in a variable (part number), it shouldn't have a problem pulling in the Warning if it has a partnumber that matches.

My problem is figuring out how to get that variable that contains the current row of the product.

Does this make sense? Do you have an example of something that you have done like this that I could look at?

[Guest]

<%
Dim rsRecordset__name
rsRecordset__name = "7-1203-4" 'default item
If (Request.QueryString("current") <> "") Then 
  rsRecordset__name = Request.QueryString("current")
End If
%>
<%
Dim rsRecordset
Dim rsRecordset_cmd
Dim rsRecordset_numRows

Set rsRecordset_cmd = Server.CreateObject ("ADODB.Command")
rsRecordset_cmd.ActiveConnection = MM_connShnta_STRING
rsRecordset_cmd.CommandText = "{call changed.storedprocedurename(?)}" 
rsRecordset_cmd.Prepared = true
rsRecordset_cmd.Parameters.Append rsRecordset_cmd.CreateParameter("param1", 200, 1, 255, rsRecordset__name) ' adVarChar

Set rsRecordset = rsRecordset_cmd.Execute
rsRecordset_numRows = 0
%>

Above is a typical recordset that calls a SP. I think the If statement is where the update needs to be. Instead of Request.Querystring... what should go there that would pull in the current record? Am I right about this?

[bregent]

You could try something like this. Get rid of that first conditional block of code.

<%
Dim rsWarning
Dim rsWarning_cmd
Dim rsWarning_numRows

Set rsWarning_cmd = Server.CreateObject ("ADODB.Command")
rsWarning_cmd.ActiveConnection = MM_connShnta_STRING
rsWarning_cmd.CommandText = "sp_GetWarning" 
rsWarning_cmd.CommandType = adCmdStoredProc

rsWarning_cmd.Parameters.Refresh
rsWarning_cmd(1) = rsProduct("ProductID")
Set rsWarning = rsRecordset_cmd.Execute
rsWarning_numRows = 0
%>

Also, investigate re-using your connections. Opening and closing connections is expensive and your inner query needs to execute once for every product returned in the outer query.

[Guest]

ADODB.Command error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

I got this error when I tried to plug this in.

[Guest]

I changed my SP and since mine is expecting a variable, I expect it will error out. However, I don't see an obvious error that's looks like it's related to this.

<%
Dim rsWarning
Dim rsWarning_cmd
Dim rsWarning_numRows

Set rsWarning_cmd = Server.CreateObject ("ADODB.Command")
rsWarning_cmd.ActiveConnection = MM_connShnta_STRING
rsWarning_cmd.CommandText = "sp_GetWarning" 
rsWarning_cmd.CommandType = adCmdStoredProc

For some reason, it makes it down to the line below (line 57) I changed the SP so that it wouldn't look for the variable but would just pull in all the files. However, I'm not sure if the code below does this.

rsWarning_cmd.Parameters.Refresh
rsWarning_cmd(1) = rsProduct("ProductID")'line57

All I get is the error.

ADODB.Command error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/pagename.asp line 57

It looks like it's just not finding any tables named ProductID

Set rsWarning = rsRecordset_cmd.Execute
rsWarning_numRows = 0
%>

[bregent]

>It looks like it's just not finding any tables named ProductID

Just so we are clear on this, my example code used variable names that I made up, so you would have to substitute your variable names or change them. What is the name of your outer recordset? What is the name of the column (or procedure output parm in this case) that contains the ProductID?

[Guest]

Yes, I swapped those out.  Thanks for checking.

I've gone over it a couple times and I can't find the problem. I've not done it this way before so it's possible I've messed it up somehow.