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?

[Guest]

What is the name of the column (or procedure output parm in this case) that contains the ProductID?

I believe it's ProductID

[Guest]

In other words, when looking at the bindings under the parent recordset?

The part number is ProductID

[bregent]

Are you outputting the product id value to the screen in your outer repeating region? What is the syntax used to reference the value there?

Try can try this syntax instead of my original suggestion

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

Substitue your actual outer recordset name for objRS.

[Guest]

I'm not actually able to get past the connection errors

[bregent]

Connection error? What connection error? Do you mean the collections error?

[Guest]

I believe so,  yes.

[Guest]

<%
Dim rsProduct
Dim rsProduct_cmd
Dim rsProduct_numRows

Set rsProduct_cmd = Server.CreateObject ("ADODB.Command")
rsProduct_cmd.ActiveConnection = MM_connShnta_STRING
rsProduct_cmd.CommandText = "{call User.spName(?, ?, ?)}"
rsProduct_cmd.Prepared = true
rsProduct_cmd.Parameters.Append rsProduct_cmd.CreateParameter("param1", 200, 1, 255, rsProduct__searching1) ' adVarChar
rsProduct_cmd.Parameters.Append rsProduct_cmd.CreateParameter("param2", 200, 1, 255, rsProduct__searching2) ' adVarChar
rsProduct_cmd.Parameters.Append rsProduct_cmd.CreateParameter("param3", 200, 1, 255, rsProduct__searching3) ' adVarChar

Set rsProduct = rsProduct_cmd.Execute
rsProduct_numRows = 0
%>



<%
Dim rsWarning
Dim rsWarning_cmd
Dim rsWarning_numRows
Set rsWarning_cmd = Server.CreateObject ("ADODB.Command")
rsWarning_cmd.ActiveConnection = MM_connShnta_STRING
rsWarning_cmd.CommandText = "{call User.spWarning}"
rsWarning_cmd.Prepared = true
'rsWarning_cmd.CommandType = adCmdStoredProc ' Had to remove this...errored out
rsWarning_cmd.Parameters.Refresh
rsWarning_cmd(1) = rsProduct("ProductID") ' It doesn't like this line
Set rsWarning = rsProduct_cmd.Execute
rsWarning_numRows = 0
%>
*********************************************SP that brings in Warnings
ALTER PROCEDURE [User].[spWarning]
/******@variable varchar(50)******/
AS
SELECT  WType.WARNINGTEXT, WARN.WarningID, WARN.WarningID
FROM TBLWARNType WType
INNER JOIN TBLWARNING WARN ON WType.WID = WARN.WarningID

I'm sorry for being such a pain here.

I don't understand how my spWarning procedure knows what to search on. With my procedure, I'm basically just pulling matching data from two joined tables

Should I add a "WHERE" statement to that procedure? How does the SP know which ProductID we're at? On the page I've added the rsWarning_cmd(1) but I don't see how the SToredProc knows what rsWarning_cmd(1) is and that it's using the Current rsProduct.ProductID?

I hope this makes sense.

[bregent]

OK, it probably just is not finding the constant value. Try this code instead:

<%

Dim rsWarning
Dim rsWarning_cmd
Dim rsWarning_numRows
Set rsWarning_cmd = Server.CreateObject ("ADODB.Command")
rsWarning_cmd.ActiveConnection = MM_connShnta_STRING
rsWarning_cmd.CommandText = "User.spWarning"
rsWarning_cmd.Prepared = true
rsWarning_cmd.CommandType = 4 'adCmdStoredProc ' Had to remove this...errored out
rsWarning_cmd.Parameters.Refresh
rsWarning_cmd(1) = rsProduct("ProductID") ' It doesn't like this line
Set rsWarning = rsProduct_cmd.Execute
rsWarning_numRows = 0
%>

[Guest]

ID	WarningID	ProductID
1	1	part0001
2	4	part0002
3	2	part0003
4	3	part0001

ID	WarningID	WarningText
1	1	Warning 1
2	2	Warning 2
3	3	Warning 3
4	4	Warning 4

This is a little more background just to make sure we're on the same page.

The TBLWARNING table holds the part association (there will be multiple ProductIDs with different warning numbers

The other (TBLWARNTYPE) holds the warning number and the actual warning message. There are only nine of these.

The SP then needs to know what partnumber so it can associate the WarningID in the TBLWARNING table with WarningID in the TBLWARNTYPE table.

[Guest]

OK,

I got through the errors with that recordset after your last post but whenever I try to put anything from that recordset on the page, it can't find any records.

Does my Warnings SP look right?

[bregent]

>got through the errors with that recordset

>after your last post but whenever I try to

>put anything from that recordset on the page,

>it can't find any records.

Sorry, I've been out of town. What happens when you try to execute that procedure from your SQL manager tool?

>Does my Warnings SP look right?

It's been a long thread. Can you post the sproc again, I don't see it.

[Guest]

bregent, I've been on another project anyways...thanks

I've simplified the problem down a bit (I believe).

When I do a simple select with DISTINCT in it, I don't get multiples. However, I need to select more than just the PID. I need hazdate and others...As soon as I put them into the SELECT, I go back to getting mulitiples because, of course, it's selecting across all of them.

****This selects a single instance of the 7-1203-4. *************

SELECT DISTINCT PID
FROM         dbo.tblHazard
WHERE      (PID = '7-1203-4')

This is good but I need to be able to select other columns.

Consequently, I tried putting  a subquery in it in order to do a DISTINCT  on just the PID. Below still gives me multiples. I must be doing the subquery wrong because it's not able to use the results of the subquery.

SELECT  PID, hazdate
FROM         dbo.tblHazard AS HAZ
WHERE     (PID IN
                          (SELECT DISTINCT PID
                            FROM          dbo.tblHazard AS HAZ2
                            WHERE      (PID = '7-1203-4')));

I find it odd that this is not covered in a the books I have. It seems like it would be a common issue.

[bregent]

Let's forget about the subquery for a moment. If

SELECT  DISTINCT PID, hazdate
FROM         dbo.tblHazard AS HAZ

WHERE      (PID = '7-1203-4')

is returning more than one row for each PID, it is I assume because for each PID there is more than one hazdate. The DISTINCT keyword is used to eliminate duplicate rows from the result. A duplicate row exists when every column in the select matches another row. Your query with the subselect is not doing anything different than my query above.

The queries will always return more than one row per PID if more than one row exists. So -- assuming that adding hazdate to the query is causing the problem-- if you only want to return a single row and include hazdate, you need to tell the query which hazdate to use.

>I find it odd that this is not covered in a the books I have.

>It seems like it would be a common issue.

What books are you looking in? From the looks of things, you are asking the query to do something that is not logical.

[Guest]

SELECT  DISTINCT PID, hazdate
FROM         dbo.tblHazard AS HAZ

WHERE      (PID = '7-1203-4')

Yes, this pulls in multiples because there is more than one tblHazard with the same PID

SELECT  DISTINCT PID
FROM         dbo.tblHazard AS HAZ

WHERE      (PID = '7-1203-4')

With this above, when I pull in just the PID as DISTINCT, it gives me one result.

With the subquery, I was trying to single out that DISTINCT PID so that it wasn't including the hazdate. As you said, the subquery pulls in the same thing.

What I need is a way to pull in just one PID match from the tblHazard table but still be able to select other columns and have them available to bind to the page.