Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

VB foreach query for string array

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
desiredforsome
Asked:
desiredforsome
1 Solution
 
GrahamSkanRetiredCommented:
You don't say what the error is, but there is no 'End If' for the 'If records.EOF = False' line.
0
 
HooKooDooKuCommented:
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
 
ChloesDadCommented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now