Solved

VB foreach query for string array

Posted on 2014-12-01
3
289 Views
Last Modified: 2014-12-08
I am using the chilkat library for email and having some issues.

I am running a script htat is written in vb6/vba and i need it to check something.

The method is MSG.GetToAddr()

I want to load everything into a string array. Then foreach string in that array I want to run a set of commands to check. I am not running into any luck with the below code. I tkeeps giving me an error on the Next Itm.



Sub Main()
Dim database As New ADODB.Connection
Dim records As New ADODB.Recordset
Dim sql As String
Dim phones As String
Dim numto As Long
numto=MSG.NumTo
Dim recip As  Variant







On Error GoTo erl

database.ConnectionString="Provider=sqloledb; Data Source=192.168.2.121\EMMSDE;Initial Catalog=outlookreport; User Id=jsmith; Password=s5993153492;"
database.Open

If (numto > 0) Then

    For i = 0 To numto - 1
    recip = Array(MSG.GetToAddr(i))
    For Each itm In Array(MSG.GetToAddr(i))

    sql = "SELECT * FROM OUTLOOKREPORT.DBO.MFUSERS where username='" & recip & "'"

records.Open sql,database, adOpenForwardOnly, adLockReadOnly
If records.EOF=False Then

MSG.SaveEml("I:\Mail\" & MSG_UID & Format(Date,"yyyymmdd") & Format(Time,"hhmmss") & ".eml")

Email2DBAccept=False
Next itm
'This is where we conver to eml


'End Code on it
Call AddToLog("Message Posted to Online Portal")
Else
Email2DBAccept = True
End If
records.Close
database.Close
Exit Sub

    Next
    End If





'select the record we want


erl:
Call AddToLog("Script Errror: " & Err.Description)
End Sub

Open in new window

0
Comment
Question by:desiredforsome
3 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40474405
You don't say what the error is, but there is no 'End If' for the 'If records.EOF = False' line.
0
 
LVL 16

Expert Comment

by:HooKooDooKu
ID: 40474712
I can't say too much because I don't know what MSG.GetToAddr(i) is returning.

But what I can say is that I see at least one logic flaw...
Your logic appears to be trying to take an ARRAY object and do a for loop on each 'itm' inside the array.  But in the sql loop, you're effectively using the whole array.

In other words... even though the following isn't valid VB syntax, the logic you have written is similar to this...

Dim txt as String
Dim ch as Charactor

txt = "ABCDEFG"
For each ch in txt
    sql = "Select * from Table where Left(Field1,1) = txt

But what you really want is ...
    sql = "Select * from Table where Left(Field1,1) = ch
0
 
LVL 15

Accepted Solution

by:
ChloesDad earned 500 total points
ID: 40474718
you have the following structure, which is not allowed

For Each itm in Array
  IF ........
Next
End IF

The next and the end if are the wrong way around, but there arwe other lines of code inbetween, so its difficult to establish what should be where, but here is my best guess

    Sub Main()
        Dim database As New ADODB.Connection
        Dim records As New ADODB.Recordset
        Dim sql As String
        Dim phones As String
        Dim numto As Long
        numto = 1 MSG.NumTo
        Dim recip As Object

        On Error GoTo erl

        database.ConnectionString = "Provider=sqloledb; Data Source=192.168.2.121\EMMSDE;Initial Catalog=outlookreport; User Id=jsmith; Password=s5993153492;"
        database.Open()

        If (numto > 0) Then

            For i = 0 To numto - 1
                recip = Array(MSG.GetToAddr(i))

                For Each itm In Array(MSG.GetToAddr(i))

                    sql = "SELECT * FROM OUTLOOKREPORT.DBO.MFUSERS where username='" & recip & "'"
                    records.Open(sql, database, adOpenForwardOnly, adLockReadOnly)

                    If records.EOF = False Then

                        MSG.SaveEml("I:\Mail\" & MSG_UID & Format(Date,"yyyymmdd") & Format(Time,"hhmmss") & ".eml")
                        Email2DBAccept = False
                    End If
                Next itm
                'This is where we conver to eml

            Next i

            'End Code on it
            Call AddToLog("Message Posted to Online Portal")
        Else
            Email2DBAccept = True
        End If
        records.Close()
        database.Close()

        Exit Sub




        'select the record we want


erl:
        Call AddToLog("Script Errror: " & Err.Description)
    End Sub

Open in new window


A few other things, there is an exit sub immediately before a next, therefore you would never iterate through the loop more than once. In my opinion Exit Sub should only be used at6 the bottom of a sub routine when there is error handling code below it. Needing to use it elsewhere is generally a sign of bad subroutine design. Alsdo notice how the code is indented when there is a new block, (IF, FOR, WHILE etc) this makes reading much easier, and misplaced loop control much more apparent as the For and Next will not be in line with each other.
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question