Solved

VB foreach query for string array

Posted on 2014-12-01
3
281 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now