Looping within a Loop

Looping within a Loop

Post by Tim Hanse » Fri, 21 Feb 2003 03:51:34



OK, I'm stumped.
Below is some sample code that is failing.  Specifially
when I parse the code I get:
  Error Description: [Expected End Of Statment]
on line marked <***> below

If I comment out the i_ctr on the same line I get:
   ['Loop' without 'Do']

What I am doing is, for each record in the recordset,
check [many] variables and add data about them to another
table for a report source.  At one point I need to split a
[6 digit] string into 3 [2 digit] strings ([stExitCodeX]).
I then need to check/act on each of the three strings.  
Hence the For/Next loop within the Do While Loop.  It
appears that ActiveX/SQLServer 7 doesn't like this.

This same process works without a problem in Access/VBA.  
I'd like it to work on the SQLServer so that machine can
do the work.

Any ideas?  All help is greatly appreciated.
Thanks
Tim Hansen

========================

' ** Sample Code that fails **
Function Main()
'Set connection and recordset(s)
'DIM variables

Do While Not rsRead.EOF
  'Do some stuff
  'Do some more stuff
  i_Ctr = 0
  For i_Ctr = 1 To 3
     Select Case i_Ctr
       Case 1: stCheckCase = stExitCode1
       Case 2: stCheckCase = stExitCode2
       Case 3: stCheckCase = stExitCode3
       Case Else
     End Select
     Select Case stCheckCase
       Case "ValidstCheckCase1"
          'Do some stuff
       Case "ValidstCheckCase2"
          'Do some stuff
       Case "ValidstCheckCase3"
          'Do some stuff
       Case "ValidstCheckCase4"
          'Do some stuff
       Case Else
     End Select
  Next i_Ctr  '*** Line that Error Message references
rsRead.MoveNext
Loop
'Do some closing statements
Main = DTSTaskExecResult_Success
End Function

' ** END Sample Code that fails **

 
 
 

Looping within a Loop

Post by Aaron Bertrand [MVP » Fri, 21 Feb 2003 03:58:53


Quote:>   Next i_Ctr  '*** Line that Error Message references

My guess is that either you shouldn't be using i_Ctr as part of that line
(just Next should be sufficient) or that there is other code inside your
SELECT structures which might be causing the error.

Maybe you could post the actual code (instead of generic and fruitless 'do
some stuff') to a newsgroup dedicated to the language you're working in
(can't tell if it's VBScript or VB)?

--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/

Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.

 
 
 

Looping within a Loop

Post by Allan Mitchel » Fri, 21 Feb 2003 04:06:14


Get rid of the Next i_Ctr and replace with Next

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


> OK, I'm stumped.
> Below is some sample code that is failing.  Specifially
> when I parse the code I get:
>   Error Description: [Expected End Of Statment]
> on line marked <***> below

> If I comment out the i_ctr on the same line I get:
>    ['Loop' without 'Do']

> What I am doing is, for each record in the recordset,
> check [many] variables and add data about them to another
> table for a report source.  At one point I need to split a
> [6 digit] string into 3 [2 digit] strings ([stExitCodeX]).
> I then need to check/act on each of the three strings.
> Hence the For/Next loop within the Do While Loop.  It
> appears that ActiveX/SQLServer 7 doesn't like this.

> This same process works without a problem in Access/VBA.
> I'd like it to work on the SQLServer so that machine can
> do the work.

> Any ideas?  All help is greatly appreciated.
> Thanks
> Tim Hansen

> ========================

> ' ** Sample Code that fails **
> Function Main()
> 'Set connection and recordset(s)
> 'DIM variables

> Do While Not rsRead.EOF
>   'Do some stuff
>   'Do some more stuff
>   i_Ctr = 0
>   For i_Ctr = 1 To 3
>      Select Case i_Ctr
>        Case 1: stCheckCase = stExitCode1
>        Case 2: stCheckCase = stExitCode2
>        Case 3: stCheckCase = stExitCode3
>        Case Else
>      End Select
>      Select Case stCheckCase
>        Case "ValidstCheckCase1"
>           'Do some stuff
>        Case "ValidstCheckCase2"
>           'Do some stuff
>        Case "ValidstCheckCase3"
>           'Do some stuff
>        Case "ValidstCheckCase4"
>           'Do some stuff
>        Case Else
>      End Select
>   Next i_Ctr  '*** Line that Error Message references
> rsRead.MoveNext
> Loop
> 'Do some closing statements
> Main = DTSTaskExecResult_Success
> End Function

> ' ** END Sample Code that fails **

 
 
 

Looping within a Loop

Post by Tim Hanse » Fri, 21 Feb 2003 04:18:01


Quote:>-----Original Message-----
>>   Next i_Ctr  '*** Line that Error Message references

>My guess is that either you shouldn't be using i_Ctr as
part of that line
>(just Next should be sufficient) or that there is other
code inside your
>SELECT structures which might be causing the error.

As I have stated in my orginal post, when I use just NEXT,
I get:  ['Loop' without 'Do']

Quote:>Maybe you could post the actual code (instead of generic
and fruitless 'do
>some stuff') to a newsgroup dedicated to the language
you're working in
>(can't tell if it's VBScript or VB)?

Here is the ActiveX/VBScript Code contained in my DTS
package:
'**********************************************************
************
'  Visual Basic ActiveX Script
'**********************************************************
**************

Function Main()
'In the interest of bandwidth I will not post code that
works correctly (Connection strings/recordset connections)

Do While Not rsRead.EOF
'Part II - SecA
'SecA - Total
If Not IsNull(rsRead.Fields("EnrollmentDate")) Then
  rsWrite.AddNew
  rsWrite("PartNo").Value = "Part II"
  rsWrite("SecLetter").Value = "A"
  rsWrite("ItemSortOrder").Value = 0
  rsWrite("CountIt").Value = 1
rsWrite("GrntCode").Value = rsRead.Fields
("Header_GrantCode").Value
  rsWrite("ItemNum").Value = "PtII_SecA_Total"
rsWrite("socSN").Value = rsRead.Fields
("PartIV_SecA_Total").Value
  rsWrite("AppNum").Value = rsRead.Fields("appNum").Value
  rsWrite.Update
End If
'The above gets repeated with slight variation (and works)
and in the interest of bandwith I will NOT post the ENTIRE
code.  Only the RELEVANT ERROR areas
' ** Items #9, 10, 11 **
i_Ctr = 0
  For i_Ctr = 1 To 3
    Select Case i_Ctr
      Case 1: stCheckCase = stExitCode1
      Case 2: stCheckCase = stExitCode2
      Case 3: stCheckCase = stExitCode3
    End Select

    Select Case stCheckCase
    ' ** Item #9 **
      Case "16"
        rsWrite.AddNew
        rsWrite("PartNo") = "Part II"
        rsWrite("SecLetter") = "B"
        rsWrite("ItemSortOrder") = 9
        rsWrite("ExitDate") = rsRead.Fields("ExitDate")
        rsWrite("Exited") = -1
        rsWrite("GrntCode") = rsRead.Fields
("Header_GrantCode")
        rsWrite("ItemNum") = "PtII_SecB_09"
        rsWrite("CountIt") = 0
        rsWrite("ExitCount") = 1
        rsWrite("socSN") = rsRead.Fields
("PartIV_SecA_Total")
        rsWrite("AppNum") = rsRead.Fields("appNum")
        rsWrite.Update

     ' ** Item #10 **
      Case "10", "12", "13", "18"
        rsWrite.AddNew
        rsWrite("PartNo") = "Part II"
        rsWrite("SecLetter") = "B"
        rsWrite("ItemSortOrder") = 10
        rsWrite("ExitDate") = rsRead.Fields("ExitDate")
        rsWrite("Exited") = -1
        rsWrite("GrntCode") = rsRead.Fields
("Header_GrantCode")
        rsWrite("ItemNum") = "PtII_SecB_10"
        rsWrite("CountIt") = 0
        rsWrite("ExitCount") = 1
        rsWrite("socSN") = rsRead.Fields
("PartIV_SecA_Total")
        rsWrite("AppNum") = rsRead.Fields("appNum")
        rsWrite.Update

      ' ** Item #11 **
      Case "06", "07", "09", "11", "14", "15", "17"
        rsWrite.AddNew
        rsWrite("PartNo") = "Part II"
        rsWrite("SecLetter") = "B"
        rsWrite("ItemSortOrder") = 11
        rsWrite("ExitDate") = rsRead.Fields("ExitDate")
        rsWrite("Exited") = -1
        rsWrite("GrntCode") = rsRead.Fields
("Header_GrantCode")
        rsWrite("ItemNum") = "PtII_SecB_11"
        rsWrite("CountIt") = 0
        rsWrite("ExitCount") = 1
        rsWrite("socSN") = rsRead.Fields
("PartIV_SecA_Total")
        rsWrite("AppNum") = rsRead.Fields("appNum")
        rsWrite.Update
      Case Else
    End Select
Next i_Ctr

rsRead.MoveNext
Loop

rsRead.Close
rsRead_Services.Close
rsWrite.Close

'ExitHere:
    Set rsRead = Nothing
    Set rsRead_Services = Nothing
    Set rsWrite = Nothing
    Set rsGrantCode = Nothing
    Set myConn = Nothing

Main = DTSTaskExecResult_Success
End Function

' *** END CODE
Tim Hansen

========================

 
 
 

Looping within a Loop

Post by Tim Hanse » Fri, 21 Feb 2003 04:20:58


Quote:>-----Original Message-----
>Get rid of the Next i_Ctr and replace with Next

BUT:
If I comment out the i_ctr on the same line I get:
    ['Loop' without 'Do']

When I parse the code with only [NEXT] I get the above
error description.
--
HTH
Tim Hansen

========================

 
 
 

Looping within a Loop

Post by Tim Hanse » Fri, 21 Feb 2003 04:26:11


DOH!!!!
Nevermind
I have found the culprit piece of code (stray WITH
statement)

Tim (sheepishly) Hansen

========================

>-----Original Message-----
>>-----Original Message-----
>>Get rid of the Next i_Ctr and replace with Next

>BUT:
>If I comment out the i_ctr on the same line I get:
>    ['Loop' without 'Do']

>When I parse the code with only [NEXT] I get the above
>error description.
>--
>HTH
>Tim Hansen

>========================
>.

 
 
 

Looping within a Loop

Post by David Brown » Fri, 21 Feb 2003 04:30:20


Look for an unclosed block of some sort inside the omitted code.
An if without end if, for instance.

This can cause compiler errors in other places.

David

> OK, I'm stumped.
> Below is some sample code that is failing.  Specifially
> when I parse the code I get:
>   Error Description: [Expected End Of Statment]
> on line marked <***> below

> If I comment out the i_ctr on the same line I get:
>    ['Loop' without 'Do']

> What I am doing is, for each record in the recordset,
> check [many] variables and add data about them to another
> table for a report source.  At one point I need to split a
> [6 digit] string into 3 [2 digit] strings ([stExitCodeX]).
> I then need to check/act on each of the three strings.
> Hence the For/Next loop within the Do While Loop.  It
> appears that ActiveX/SQLServer 7 doesn't like this.

> This same process works without a problem in Access/VBA.
> I'd like it to work on the SQLServer so that machine can
> do the work.

> Any ideas?  All help is greatly appreciated.
> Thanks
> Tim Hansen

> ========================

> ' ** Sample Code that fails **
> Function Main()
> 'Set connection and recordset(s)
> 'DIM variables

> Do While Not rsRead.EOF
>   'Do some stuff
>   'Do some more stuff
>   i_Ctr = 0
>   For i_Ctr = 1 To 3
>      Select Case i_Ctr
>        Case 1: stCheckCase = stExitCode1
>        Case 2: stCheckCase = stExitCode2
>        Case 3: stCheckCase = stExitCode3
>        Case Else
>      End Select
>      Select Case stCheckCase
>        Case "ValidstCheckCase1"
>           'Do some stuff
>        Case "ValidstCheckCase2"
>           'Do some stuff
>        Case "ValidstCheckCase3"
>           'Do some stuff
>        Case "ValidstCheckCase4"
>           'Do some stuff
>        Case Else
>      End Select
>   Next i_Ctr  '*** Line that Error Message references
> rsRead.MoveNext
> Loop
> 'Do some closing statements
> Main = DTSTaskExecResult_Success
> End Function

> ' ** END Sample Code that fails **

 
 
 

Looping within a Loop

Post by Aaron Bertrand [MVP » Fri, 21 Feb 2003 04:33:22


Other than removing the unnecessary CASE ELSE line, I'd suggest the error
originates higher up.  Another experiment might be to take out all the
SELECT CASE code and just debug.print a value.  In other words, instead of
having so much code in there that you can't post it, simplify it to the
point where you can get it to fail with minimal code, and then add
complexity as you go.  If you can only get it to fail when it's so complex
you can't provide enough code for us to try and reproduce or at least break
it down, we can't help you very well...

--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/

Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.


> >-----Original Message-----
> >>   Next i_Ctr  '*** Line that Error Message references

> >My guess is that either you shouldn't be using i_Ctr as
> part of that line
> >(just Next should be sufficient) or that there is other
> code inside your
> >SELECT structures which might be causing the error.

> As I have stated in my orginal post, when I use just NEXT,
> I get:  ['Loop' without 'Do']

> >Maybe you could post the actual code (instead of generic
> and fruitless 'do
> >some stuff') to a newsgroup dedicated to the language
> you're working in
> >(can't tell if it's VBScript or VB)?

> Here is the ActiveX/VBScript Code contained in my DTS
> package:
> '**********************************************************
> ************
> '  Visual Basic ActiveX Script
> '**********************************************************
> **************

> Function Main()
> 'In the interest of bandwidth I will not post code that
> works correctly (Connection strings/recordset connections)

> Do While Not rsRead.EOF
> 'Part II - SecA
> 'SecA - Total
> If Not IsNull(rsRead.Fields("EnrollmentDate")) Then
>   rsWrite.AddNew
>   rsWrite("PartNo").Value = "Part II"
>   rsWrite("SecLetter").Value = "A"
>   rsWrite("ItemSortOrder").Value = 0
>   rsWrite("CountIt").Value = 1
> rsWrite("GrntCode").Value = rsRead.Fields
> ("Header_GrantCode").Value
>   rsWrite("ItemNum").Value = "PtII_SecA_Total"
> rsWrite("socSN").Value = rsRead.Fields
> ("PartIV_SecA_Total").Value
>   rsWrite("AppNum").Value = rsRead.Fields("appNum").Value
>   rsWrite.Update
> End If
> 'The above gets repeated with slight variation (and works)
> and in the interest of bandwith I will NOT post the ENTIRE
> code.  Only the RELEVANT ERROR areas
> ' ** Items #9, 10, 11 **
> i_Ctr = 0
>   For i_Ctr = 1 To 3
>     Select Case i_Ctr
>       Case 1: stCheckCase = stExitCode1
>       Case 2: stCheckCase = stExitCode2
>       Case 3: stCheckCase = stExitCode3
>     End Select

>     Select Case stCheckCase
>     ' ** Item #9 **
>       Case "16"
>         rsWrite.AddNew
>         rsWrite("PartNo") = "Part II"
>         rsWrite("SecLetter") = "B"
> rsWrite("ItemSortOrder") = 9
> rsWrite("ExitDate") = rsRead.Fields("ExitDate")
> rsWrite("Exited") = -1
> rsWrite("GrntCode") = rsRead.Fields
> ("Header_GrantCode")
> rsWrite("ItemNum") = "PtII_SecB_09"
> rsWrite("CountIt") = 0
> rsWrite("ExitCount") = 1
> rsWrite("socSN") = rsRead.Fields
> ("PartIV_SecA_Total")
> rsWrite("AppNum") = rsRead.Fields("appNum")
> rsWrite.Update

>      ' ** Item #10 **
>       Case "10", "12", "13", "18"
> rsWrite.AddNew
> rsWrite("PartNo") = "Part II"
> rsWrite("SecLetter") = "B"
> rsWrite("ItemSortOrder") = 10
> rsWrite("ExitDate") = rsRead.Fields("ExitDate")
> rsWrite("Exited") = -1
> rsWrite("GrntCode") = rsRead.Fields
> ("Header_GrantCode")
> rsWrite("ItemNum") = "PtII_SecB_10"
> rsWrite("CountIt") = 0
> rsWrite("ExitCount") = 1
> rsWrite("socSN") = rsRead.Fields
> ("PartIV_SecA_Total")
> rsWrite("AppNum") = rsRead.Fields("appNum")
> rsWrite.Update

>       ' ** Item #11 **
>       Case "06", "07", "09", "11", "14", "15", "17"
> rsWrite.AddNew
> rsWrite("PartNo") = "Part II"
> rsWrite("SecLetter") = "B"
> rsWrite("ItemSortOrder") = 11
> rsWrite("ExitDate") = rsRead.Fields("ExitDate")
> rsWrite("Exited") = -1
> rsWrite("GrntCode") = rsRead.Fields
> ("Header_GrantCode")
> rsWrite("ItemNum") = "PtII_SecB_11"
> rsWrite("CountIt") = 0
> rsWrite("ExitCount") = 1
> rsWrite("socSN") = rsRead.Fields
> ("PartIV_SecA_Total")
> rsWrite("AppNum") = rsRead.Fields("appNum")
> rsWrite.Update
>       Case Else
>     End Select
> Next i_Ctr

> rsRead.MoveNext
> Loop

> rsRead.Close
> rsRead_Services.Close
> rsWrite.Close

> 'ExitHere:
>     Set rsRead = Nothing
>     Set rsRead_Services = Nothing
>     Set rsWrite = Nothing
>     Set rsGrantCode = Nothing
>     Set myConn = Nothing

> Main = DTSTaskExecResult_Success
> End Function

> ' *** END CODE
> Tim Hansen

> ========================

 
 
 

Looping within a Loop

Post by Tim Hanse » Fri, 21 Feb 2003 04:50:06


DOH!!!!
Nevermind
I have found the culprit piece of code (stray WITH
statement)

Tim (sheepishly) Hansen

========================

Quote:>-----Original Message-----
>Other than removing the unnecessary CASE ELSE line, I'd
suggest the error
>originates higher up.  Another experiment might be to
take out all the
>SELECT CASE code and just debug.print a value.  In other
words, instead of
>having so much code in there that you can't post it,
simplify it to the
>point where you can get it to fail with minimal code, and
then add
>complexity as you go.  If you can only get it to fail

when it's so complex
Quote:>you can't provide enough code for us to try and reproduce
or at least break
>it down, we can't help you very well...

>--
>Aaron Bertrand, SQL Server MVP
>http://www.aspfaq.com/

>Please reply in the newsgroups, but if you absolutely
>must reply via e-mail, please take out the TRASH.

 
 
 

Looping within a Loop

Post by Shawn B » Fri, 21 Feb 2003 05:55:41


Seeing as how there is  not WITH statement in the posted code, it's a good
example of why we couldn't help due to incomplete code.

Thanks,
Shawn


> DOH!!!!
> Nevermind
> I have found the culprit piece of code (stray WITH
> statement)

> Tim (sheepishly) Hansen

> ========================

> >-----Original Message-----
> >Other than removing the unnecessary CASE ELSE line, I'd
> suggest the error
> >originates higher up.  Another experiment might be to
> take out all the
> >SELECT CASE code and just debug.print a value.  In other
> words, instead of
> >having so much code in there that you can't post it,
> simplify it to the
> >point where you can get it to fail with minimal code, and
> then add
> >complexity as you go.  If you can only get it to fail
> when it's so complex
> >you can't provide enough code for us to try and reproduce
> or at least break
> >it down, we can't help you very well...

> >--
> >Aaron Bertrand, SQL Server MVP
> >http://www.aspfaq.com/

> >Please reply in the newsgroups, but if you absolutely
> >must reply via e-mail, please take out the TRASH.

 
 
 

1. Need help with a loop script (won't loop)

Hi and thanks in advance

Sorry but got stocked in this problem.

Here is what I'm trying to do is.
From 4 FMP DB.
(participants.fp5, Questions.fp5, Test.fp5 and selected Questions.fp5)

I start out from within Participants.fp5 and generate ( script wise ) new
record in Test.fp5 and uses "set field" to define User_ID and test type.

Her I can manually ( through a value list ) in a portal row select the test
Questions, but only one by one.

I want to automate this !! so that all question that match a find (java) in
Question.fp5 would be generated in selected Questions.fp5

I use 2 scripts to try to accomplish this.

1. is in Questions.fp5 and produce a find. (works)

2. is in Test.fp5 and should produce a loop I think ??
   It doesn't work properly, it only generate 1 Selected Question in the
   portal row and stops.
-----------------------------
Go to Portal Row [First]
Loop
    Set Field ["Selected Question :: Question_ID","Questions::Question_ID"]
    Perform Script [Sub-scripts,External:"Questions.fp5"]
    Go to Record/Request/page [exit after last, Next]
End loop
-----------------------------

Is this a totally wrong approach to my issue. ????

Any idea or clue is appreciated

Lars Luciano Arlr               - Graphic Designer & Systems Architect

2. OLE bound pictures

3. HELP (VFP3) My Loop Doesn't Loop?

4. sql2000 triggers

5. Loops will not loop any more

6. wIntegrate on UV - Break keys?

7. Help Help For Loops and incrementing fields each loop

8. help !!! forms in vb

9. Alter Table within Loop using variable column

10. @@IDENTITY within outer recordset loop

11. EXIT within nested loops

12. Export to Text Files, Looping within Stored Procedure

13. Transaction problems when calling recordset from within a loop