Invalid For loop control variable, nested ResultSet SQL

Question

I'm attempting to use records in a resultSet from an initial SQL query as loop control and to populate values into a subsequent SQL statement. The latter SQL statements resultSet will then be used to populate a HTML table of sorts.

While trying to achieve this I hit this problem

Microsoft VBScript compilation error '800a0410'

Invalid 'for' loop control variable

/ps/testcli.asp, line 83

For Each objFieldValue in RS.Fields

-----------------------^

My initial thoughts was that it was not able to discern between the clientRS and the RS, but that does not seem to be the case. The lines below start from 39 - 97

SQLDel = "DELETE * FROM ClientModuleList"
SQLDel2 = "DELETE FROM PSS.dbo.TestCli"

'------   
SQLGetClientsV1MVSOld = "SELECT ClientID FROM PSS.dbo.CLIENTS WHERE CardpacVersion='Cardlink 1.0' AND OS='MVS'AND Base='Old'AND Active='1'"
SQLGetClientsV1MVSNew = "SELECT ClientID FROM CLIENTS WHERE CardpacVersion='Cardlink 1.0' , OS='MVS', Base='New', Active='1'"
SQLGetClientsV1AS4Old = "SELECT ClientID FROM CLIENTS WHERE CardpacVersion='Cardlink 1.0' , OS='AS4', Base='Old', Active='1'"
SQLGetClientsV2MVSOld = "SELECT ClientID FROM CLIENTS WHERE CardpacVersion='Cardlink 2.0' , OS='MVS', Base='Old', Active='1'"
'------
SQLGetModules01 = "SELECT Patchlib FROM "
SQLGetModules02 = " WHERE Type='B' AND Required='1'"

'-- Connection --
Set connDBO = Server.CreateObject("ADODB.Connection")

'-- ResultSets --
Set clientRS = Server.CreateObject("ADODB.RecordSet")
Set RS = Server.CreateObject("ADODB.RecordSet")

If Request.Form("submit") <> "" Then

'--Delete All Existing Rows --S001--    
    connDBO.open cString
    connDBO.execute(SQLDel2)
    connDBO.close
'--Delete All Existing Rows --E001--    

'--Get all Clients which satisfy (Cardlink 1.0, MVS, Old, Active) into clientsRS --S002--
    clientRS.Open SQLGetClientsV1MVSOld, cString

    While NOT clientRS.EOF
        For Each objFieldValue in clientRS.Fields
            SQLGetModuleFull = "" & SQLGetModule01 & objFieldValue.Value & SQLGetModule02 & ""
            RS.Open SQLGetModuleFull, cString

                Response.Write("<table border='1'>" )
                Response.Write("<tr>" )
                For Each objFieldName in RS.Fields
                    Response.Write("<th>" & objFieldName.Name & "</th>" )
                Next
                Response.Write("</tr>" )
                If NOT RS.EOF Then
                    While NOT RS.EOF
                        Response.Write("<tr>" )
                            For Each objFieldValue in RS.Fields
                                Response.Write("<td>" & objFieldValue.Value & "</td>" )
                            Next
                        Response.Write("</tr>" )
                        RS.MoveNext
                    WEnd        
                    Response.Write("</table>" )
                End If
        Next
    WEnd

    RS.Close
    clientRS.Close

End If

Any hints on why this happens? I'm very new to VBScript used in ASP Classic

Dan Bechard · Accepted Answer · 2013-10-18 14:30:53Z

4

"When you nest loops, each loop must have a unique element variable." http://msdn.microsoft.com/en-us/library/vstudio/5ebk1751.aspx

You can't reuse the same element name twice in nested For Each loops:

For Each objFieldValue in clientRS.Fields
[...]
    For Each objFieldValue in RS.Fields

Change the second to objFieldValue2 (or a better name) and see if it compiles.

answered Oct 18, 2013 at 14:30

Dan Bechard

5,2933 gold badges38 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Invalid For loop control variable, nested ResultSet SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related