Solved

VB foreach query for string array

Posted on 2014-12-01
3
279 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

744 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

17 Experts available now in Live!

Get 1:1 Help Now