Solved

VB foreach query for string array

Posted on 2014-12-01
3
295 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

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…
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…
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…

736 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